Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

News      User Comments     History     Notify Me

Microsoft Access 307
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Custom search button, DoCmd.FindRecord, GoToControl, InputBox, BeforeUpdate, Requery, DB Password, Splitter

Permanent Link
Course Link: Microsoft Access 307
Keywords: access vba findrecord gotocontrol inputbox beforeupdate requery
Post Reply

Does this also work on numeric fields Comment from Kenny Nelson @ 8/1/2017
Does this also work on numeric fields?

Reply from Alex Hedley:

Which option?
Show Just This Thread        Post Reply
dynamic combo in continuous form Comment from Cheryl Hokanson @ 8/11/2016
I just read that the dynamic combo won't work in a continuous form.  I need something that will work in a continuous form in the same way.  Is there anything that will do that, if so could you tell me what it is?
Thanks, Cheryl

Reply from Alex Hedley:

I got around this with the following tip.
Show Just This Thread        Post Reply
Dynamic Combo Boxes Comment from Cheryl H @ 8/6/2016
I think I understand what you mean but wonder:
I copied and pasted the SQL directly from query SQL view.  I assume that it was done correctly.  There are no quote marks at all in that SQL.
My question was,"What in this description is needing corrected or added so that my form will work?"
I think that something like TeamViewer would be helpful.

Reply from Alex Hedley:

You could add an AND to your WHERE clause to also use OperationID if that is needed.
Show Just This Thread        Post Reply
Dynamic Combo Boxes Comment from Cheryl H @ 8/2/2016
The source of the ProductCombo is a query, forgot to put the Q after it.  What do you mean by "acting as a string instead of the actual value?"

Reply from Alex Hedley:

Normally the SQL would be
"SELECT * FROM table WHERE Field=" & Forms!Formname!Field

If it were
"SELECT * FROM table WHERE Field=Forms!Formname!Field"
You would be saying the Field is equal to that string "Forms!Formname!Field" instead of if the Field have a value of 5
"SELECT * FROM table WHERE Field=5"
Show Just This Thread        Post Reply
Dynamic Combo Boxes Comment from Cheryl Hokanson @ 6/13/2016
I have used your format for Dynamic Combo Boxes with the following substitutions:
OrderCombo selects order of choice
ProductCombo diplays only products from that order
OperationCombo fails to limit Operation specific to that product.

Query OrderDrillDownProduct SQL
SELECT OperationT.OperationID, OperationT.OperationAbreviation, OperationT.OperationName, ProductOperationT.ProductID
FROM OperationT INNER JOIN ProductOperationT ON OperationT.OperationID = ProductOperationT.OperationID
WHERE (((ProductOperationT.ProductID)=[Forms]![OrderDrillDownF]![ProductCombo]))
ORDER BY OperationT.OperationAbreviation;

ProdutOperationT does have 2 primary Keys ProductID and OperationID which I used to in another form to limit Operations to a specific Product.

Can you help me get the OperationCombo working?

Reply from Alex Hedley:

Is [Forms]![OrderDrillDownF]![ProductCombo] acting as a string "[Forms]![OrderDrillDownF]![ProductCombo]" instead of the actual value.
How did you set the Source of the combo, is it to a Table/Query?
Show Just This Thread        Post Reply
Cascading Combo Boxes Comment from Tareq Salloum @ 11/7/2014
Hi Richard,
I created the cascade comboboxes and everything works fine. However, if I navigate to the second record or different record, the values in the combo boxes won't appear in the two combo boxes that are based on the query. I the values do get stored in the table but the combo boxes don't display them. How can I fix that?

Reply from Richard Rost:

Tareq, it's impossible for me to tell what the problem is just from your description. Are you working with CONTINUOUS FORMS? If so, I cannot guarantee that the techniques I show in my classes will work. The cascading combo boxes I use only work with SINGLE FORMS. If you want me or my team to look at your database, you can submit it via the TechHelp page.
Show Just This Thread        Post Reply
How to undo database splitting Comment from Tareq Salloum @ 11/4/2014
How to undo database splitting?

Reply from Alex Hedley:

The easiest way is revert to the backup you should have made before splitting it.

If you don't have a back up then just use the Import options to add the Objects back into either your FE/BE, whichever is easiest.
Show Just This Thread        Post Reply
Cascading Combo Boxes Comment from Jyotsana Jaswal @ 4/9/2014

I studied the Access 2003 lesson 307 about dynamic combo and created one for my situation as stated in video. But what's happening is combo Box 1 is saving ID instead of its description in the back end table. I am not understanding the reason for it.


Reply from Richard Rost:

You want to store the ID in the table. The description is just for display purposes.
Show Just This Thread        Post Reply
Comment from Alex Hedley @ 1/21/2013
Dimitrios you might want to run some code in the OnLoad and OnCurrent events to make sure they reflect the current record when you switch between them
Show Just This Thread        Post Reply
Comment from Dimitrios Grevenitis @ 1/19/2013
Hi Richard,
In my let's say Employees form i use 2 Dynamic Combo boxes in order to classify the employees based on their specialty and their rank. Let's say "Technicians" with ranks "Technician 1" , " "Technician 2" ,"Technician 3" and "Managers" with "Manager 1", "Manager 2","Manager 3".
I have made the proper query and the update event in the Combobox that defines the specialty of the employee. The problem is that when i shuffle through the employee's records from the employee form, i only see the the ranks of those who's their specialty happened to be chosen  last in the "SpecialtyCombo". For example: If my last entry was a technician, scrolling the entries of my form i can only  see the entries of  the rank field of  technician employees  and not the  ranks of the manages, although that those ranks have been successfully stored in my Employees table.

Thanks and Congratulations for your great work!
Show Just This Thread        Post Reply
Enter Parameter Value Comment from Jon Wells @ 1/18/2013
Hi Richard,
I'm using 2010 for a basic project tracking DB.  I have created a few combo boxes on the main user form to control other fields.  Example would be if the user selects his/her division the supplier options will change using the query method shown in this lesson (307).

My problem is: My form works fine on its own with all the combo Boxes referencing the query I set up and also running after update code.  When I add the form to a Navigation form and open I get a pop up box saying "Enter Parameter Value" with the following reference "Forms!ProjectsF!AccMgrFilterCombo". Any ideas on how I can correct this?


Reply from Richard Rost:

That prompt usually means you have a form or field name spelled wrong.
Show Just This Thread        Post Reply
Changes to MDE Comment from steve @ 9/23/2012
After pushing .mde to the users, and later making changes to a normal .mdb file, would it be easy to link the .mde table data back to the new .mdb file?

Reply from Richard Rost:

Ideally you would have your TABLES sitting in a split database  MDB back-end file on the server. The front-end MDE file that you're pushing to your users wouldn't contain any tables.
Show Just This Thread        Post Reply
Course Link Microsoft Access 307 Follow up Comment from Stephen @ 6/10/2012
Thanks Richard for your quick response.
I wanted to show the managers name when I selected the location for the combo..
What I selected the location with the dynamic Combo’s the second one showed just the manager but I had to select the name from the list. Then I tried on the requery, I cleared the combo and showed the Dropdown. But I still had to click. I was trying to save a mouse click    

So I ended up doing what I learned from your other lesson and took the value from the second column. Here the code

Private Sub BlackmanLocationCombo_AfterUpdate()
Me.LocationManagerCombo = Me.BlackmanLocationCombo.Column(2)
End Sub

Your courses are fanatic, very educational. You have a great teaching style which makes learning fun and exciting. Thanks and looking forward to you upcoming Access seminars especially the Access VBA series.

Reply from Richard Rost:

Yep. That will work too. I guess I didn't understand what you were trying to do. Sometimes it's better to explain things to me in GENERIC terms, because I don't always understand everyone's terminology for their business. Thanks for the compliments.
Show Just This Thread        Post Reply
Comment from Stephen Davanzo @ 6/10/2012
In the dynamic Combo’s how would you select the first value to populate the combo Box
I was trying this code, which Goes to the second Combo shows the dropdown,\.
How do you get it to put the value of the first item into the combo Box.

Here is what I have

Private Sub BlackmanLocationCombo_AfterUpdate()
DoCmd.GoToControl "LocationManagerCombo"
LocationManagerCombo.Value = ""
End Sub  

Thanks Stephen

Reply from Richard Rost:

To set the value you could just say:

Combo1 = SomeValueID

That would set the value in Combo1 and force it to run its update event, which should requery Combo2 for you.
Show Just This Thread        Post Reply
Comment from jorene arguson @ 12/21/2011
how to incorporate an excel worksheet to each records?
Show Just This Thread        Post Reply
Microsoft Access 307 Comment from eddy geijselaers @ 8/13/2011
Richard, I am struggling with the dynamic combo's in combination with the "on not in list" event. They work just fine with excisting data; entering new ones gives trouble.
If the entered item is not in list it errors on the next combo (I have 5 boxes). Figured out I need a second value to store with the item asked for, the ID from the field linked to.
Tried an rs2 recordset but can't get it working. Yes I have the requested ID in the Combobox of the former item and tried all numbers 0 to 2 to find the correct ID; but I failed.
Is there a sollution?
Show Just This Thread        Post Reply
Microsoft Access 307 Comment from Rick Paul @ 3/15/2011
Hi Rick - I realize this is old now, but in Nathan's 2nd question above, if I understood right he wanted to ensure that no one duplicated an Item within each project.  Also, couldn't he have created a compound index on ProjectID/ItemID that was set to 'No Duplicates'?

Reply from Richard Rost:

Yes, you are absolutely correct. I didn't think of that at the time. I should avoid answering questions if I'm tired - which I do too often. :)
Show Just This Thread        Post Reply
Comment from  Emad @ 11/6/2010
Can you Plz cover ULS? I need it.

Reply from Richard Rost:

Emad, what is ULS? I'm not familiar with that term.
Show Just This Thread        Post Reply
Comment from  Emad @ 11/3/2010
But How Can I use SQL Server at my work How do I install in my computer not allowed at you have another solution?

Reply from Richard Rost:

Setting up SQL Server is beyond the scope of this course. I will be making a separate SQL Server course some time in the future.
Show Just This Thread        Post Reply
Comment from Emad @ 11/3/2010
Index:11 38/ 14 35
How do I synchronize with the work of the original file in case of maintenance and modification with File MDE?

Reply from Richard Rost:

Emad, I'm note sure I understand your question. Do you mean if you make changes to the original MDB file how do you synchronize those design changes with the users' MDE files? You really can't. The only thing you can do is to distribute a new MDE file to them.
Show Just This Thread        Post Reply
Comment from  Emad B. @ 11/3/2010
Can we add recoreds at the subform?or it is only for View?

Reply from Richard Rost:

As long as you've got an update-able recordset, you should be able to edit and add records.
Show Just This Thread        Post Reply
Comment from  Jerry @ 6/9/2010
I want to FindAll records in a search that match a query "pump" and then post the list records found in a report. What is my process?

Reply from Richard Rost:

Just create a query with the criteria you want, and then use that query to feed a report. You could even set up a form to type in the parameter.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 1/5/2010
Tasks are covered in 206 and 207. You can find the complete list of topics on the Access Course Index.
Show Just This Thread        Post Reply
Comment from Cheri Parrag @ 1/5/2010
Richard, in which lesson did you discuss creating that main menu with all of the task buttons? Thanks.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 6/13/2009
Jay, I have not completed it, and I might not even bother. Reason: Microsoft removed user-level security from Access in version 2007. User-level security in Access isn't very secure at all (see my blog post about it). If there are enough people interested, I might cover security in Access 2003, but if you need REAL security, you might want to consider upsizing your backend database to SQL Server.
Show Just This Thread        Post Reply
Comment from  Jay @ 6/13/2009
Hi, you refer to a class which will go into greater detail on database security and workgroups. Have you completed this course yet?
Show Just This Thread        Post Reply
Comment from Richard Rost @ 5/20/2009
Lorraine, I don't quite understand what you're trying to do. Are you saying that the customer record already has the different phone numbers and you want the user to pick a phone number type (business, fax, etc.) from a combo box and have that number displayed? Yes, a DLOOKUP in an AfterUpdate event for the combo box should do that. I'm curious as to why you're setting the database up this way (instead of just showing all of the numbers in a subform).
Show Just This Thread        Post Reply
Nested Combo Boxes for multiple rows/records Comment from Richard Rost @ 5/18/2009
Paula, I'm glad it works for you. Yes, you'll need to manually requery the combo boxes if you add records.
Show Just This Thread        Post Reply
Comment from Lorraine McCord @ 5/14/2009
Hi Richard,

I have taken many of your courses and currently viewing Access 307, but it is not quite answering my question.  I have a form like this:

Phone Numbers------------------------
Business 800-555-1212
Business 2
Business Fax 800-555-1213
Mobile 832-888-1212

Phone type (Business, Business 2…) needs to be glued to the phone number next to it.  PhoneType is a combo box.  I have a subform called frmPhoneNumbers, which has five fields—CompanyID, PhoneNumberID, PhoneType, PhoneNumber, and PhoneExtension.  If I choose a different PhoneType, I want the form to display the correct phone number or a blank if there is no phone number—such as, Business Fax will show a different phone number than the Business phone number or Home phone number.

I tried doing the DLookup, didn’t work and now I am lost.  

I tried to use the combo box feature in Access 2007 forms.  That is the:  Find a record on my form based on the value I selected in my combo box but it is not working.

Has this specific question been covered in any of your classes?

Any ideas on how I should set this up properly?

Show Just This Thread        Post Reply
Nested Combo Boxes for multiple rows/records Comment from Paula @ 5/12/2009
Hello Richard,
The nested combo boxes work GREAT!  I followed the info at  It worked fine.  But then I went back and edited some of the data in the first combo box, and the selections in the remaining combo boxes would not change.  I needed to add the AfterUpdate refresh command. I have 4 nested combo boxes on my form. IT WORKS WONDERFULLY!  MANY THANKS!
Show Just This Thread        Post Reply
Comment from Richard Rost @ 5/7/2009
Ian, the only way the data wouldn't be saved is if your combo boxes aren't BOUND to the underlying table. Check the Control Source properties. Also, you might need to issue a ComboBoxName.Requery in the OnCurrent event of the form in order for them (especially the dependent one) to have the right set of data in them.
Show Just This Thread        Post Reply
Comment from ian dudley @ 5/6/2009
Hi Richard,
Re: Dynamic Combo Boxes
I have a question relating to dynamic combo boxes (just included in my own CRM database using your lesson guide 307).
Although the filtering works correctly and displays in the dependent combo box the data will not save to its table/record  and disappears altogether if I close the form and revisit the same record. Any ideas why the data is not being saved?
Thanks, ian
Show Just This Thread        Post Reply
Nested Combo Boxes for multiple rows/records Comment from Richard Rost @ 4/14/2009
Paula, I think I have a solution for you. I posted it as an update at the bottom of this page:
Show Just This Thread        Post Reply
Nested Combo Boxes for multiple rows/records Comment from Paula @ 4/13/2009
Thanks Richard for trying to help.  I have not yet been successful in getting 3 nested combo boxes to work on a continuous form.  Here's what I've tried:
1) I tried the code you suggested, substituting my form/subform/field names:

If ComboBox A is based on straightforward data with no criteria (ie States table), and ComboBox B (cities) is based on ComboBox A(states), when I enter data in Row 2 it doesn’t seem to know that I’m now in Row 2 on the subform.  I get the same error as before "Enter Parameter Value" box with Forms!frmName!sfrmName.Form!cboField

In the Parameter Box that appears for ComboBox B, if I enter the ID value that is the IDcriteria for the ComboBox A in the underlying query ... such as 1, 2, 3 etc. ... then the proper choices appear in ComboBox B.  This carries through with ComboBox C which is built on ComboBox B.  

2) I also went thru your Access Lesson 310.  In TrackingPayments1, I found a reference to getting values on a main form that are based on a field in a subform.  I've done this before.  However, my current dilemma is different.  My combo boxes are all on the subform, and they reference SQL code or an actual query. The combo boxes or underlying data don't have anything to do with the main form.  

I’d be really grateful if you can figure this out.  Thanks so much for your time!
Show Just This Thread        Post Reply
Nested Combo Boxes for multiple rows/records Comment from Richard Rost @ 4/9/2009
Paula, I understand your problem now. I cover this in Access 310. If you need to refer to a field on a FORM it's:


If you need to refer to a field on a SUBFORM, it's:


It's confusing. I know. If, for example, you need to get the OrderTotal field off of your OrderSubform which is a subform of the OrderF form, you'd say:


Hope this helps. Again, Access 310 explains this in more detail.
Show Just This Thread        Post Reply
Nested Combo Boxes for multiple rows/records Comment from Paula @ 4/9/2009
Sorry, I'll try again. I was unable to paste a small screenshot here as an example, so here's a simple different one.

Say I have a subform to record salespeople's trips to various vendors around the country each month.  Some of the fields are:

Date   cboState cboCity cboVendor  cboMerchandise
03/05/09  NY      NYC   A Taylor Co   dresses
03/10/09  FL     Miami  Turen Inc    jewelry
03/25/09  CA    Fresno  ABC Inc    blouses

The subform visually looks like an Excel spreadsheet with multiple rows/records for each month.  The nested combo boxes work fine for the first row.  Beginning with the second row, the cboCity (whose selections are based on the cboState in ROW 2) gives this error message:  

Can you provide some help here?  
Thank you so much!
Show Just This Thread        Post Reply
Nested Combo Boxes with multiple records Comment from Richard Rost @ 4/8/2009
Paula, I'm sorry but I don't understand your question. Can you please elaborate and give me an example?
Show Just This Thread        Post Reply
Nested Combo Boxes with multiple records Comment from Paula @ 4/8/2009
Richard, I've gone thru Access 307, Lessons 2 & 3 re. nested combo boxes.  Do you have a lesson on how to used 2 or 3 combo boxes in multiple rows . . .

   (cboBox1)      (cboBox2)  (cboBox3)
  BudgetCategory  Descript  ExpenseType


Show Just This Thread        Post Reply
Comment from Richard Rost @ 10/25/2008
Nathan, just use a BeforeUpdate event and DLOOKUP to see if that ItemID exists in the table already. If so, just issue a "1" for the Cancel property and optionally pop a warning message up. I cover BeforeUpdate and cancelling an event in Access 305. DLOOKUP was covered back in Access 302.
Show Just This Thread        Post Reply
Comment from Denise Nichols @ 10/24/2008
I especially like how you show us how to troubleshoot a problem.  That is worth a ton.
Show Just This Thread        Post Reply
Comment from Nathan Wittmann @ 10/20/2008
First of all thanks for all your help so far. I am struggling with some basic things that I used to do in Excel VBA. I want to be able to validate information by searching for a duplicate entry. So to give you an example of what I have setup is: I have a projectdetailID, ProjectID, ItemID. I adding components to a project using ProjectDetailsID. What I don't want is the user to add the same ItemID twice. So I need an error when I have the user attempt to duplicate the same ItemID twice. Example first entry is ProjectDetailsID = 1, PrjoectID = 1, ItemID = 35
second entry is ProjectDetailsID = 1, ProjectID = 1, ItemID = 36
Third entry is ProjectDetailsID = 1, ProjectID = 1, and then user duplicates by selecting ItemID = 35 again. The way I used to do this was search and if the search came back true then I would do an If statement that would error if it was duplicated. I just am lost on how I would do it now. I am using a query so I assume I would need to search qry for that field an if it duplicates it msgbox an error. Any help would be appreciated. I have decided that I need to take a VBA class or something. I was able to get by in Excel VBA, but everything in access has been more advanced them I am used to.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 9/9/2008
Nathan, Access really doesn't have a macro recorder like you're used to with Excel and Word. I show throughout the Access series different ways to change various properties. For example, in Access 202 I showed you how to change the Visible property of a text box. Well, to change the Allow Edits property of a form, all you have to do is say: Me.AllowEdits=FALSE. You can do this at run time in pretty much any event.
Show Just This Thread        Post Reply
Comment from Nathan Wittmann @ 9/8/2008
I am struggling with VBA a little. I can follow examples, but I am unable to do somethings that seem simple to me. In the past with Excel VBA I would use the macro recorder to give me the code that I need to do specific tasks. I am unable to do that here. For an example I am trying to have the Data settings changed on a form with a button click. I want to have the record change from allow edits "no" to allow edit "yes". I have tried to use the object browser with no luck. It doesnt seem logical to me. Anyways I would appreciate an example to change different settings on a form or text box ect. But I would appreciate even more is a method that is as effective as recording a task and then using the code like I used to do in Excel VBA. Thanks Nathan
Show Just This Thread        Post Reply
Comment from Richard Rost @ 6/26/2008
Moheb, you need to limit the results with a Query, or use the CRITERIA in the DoCmd.OpenReport method.
Show Just This Thread        Post Reply
Comment from  Moheb @ 6/18/2008
Want to email record which will be in report form but a single record. I tried it via macro but it sends all the records.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 2+2:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

8/15/2019Last Chance for Access CDs
8/1/2019Customer List Form Template
8/1/2019New Template Access Customer Database
7/30/2019Microsoft Access Developer 13
6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP