Access 2007-2013
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  
 
NEW Release: Access Advanced Level 2    dismiss
 
 
Courses - Microsoft Access 307
Description: Advanced Access
Running Time: 74 minutes
Pre-Requisites: Access 306 very strongly recommended
Previous Lesson: Access 306
Next Lesson: Access 308
Main Topics: Custom Search Button, Dynamic Combo Boxes, Security, MDE File, Split Database
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 1/20/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 

Access 307 continues our advanced Access database development with VBA programming.

This class focuses on creating a custom search button for your forms - where you can place a command button next to a commonly-searched field (say, LastName) then click on it, type in part of the last name, and Access will search for it. We'll learn all of the VB code behind such a feat.

We'll also look at dynamic combo boxes - where the values in the combo box can change or be changed by other fields. I'll show you an example where one combo box can be used to change the values in another (pick a state in one box and it shows you only cities from that state to pick from in the next one, for example).

In the example below, you pick a sales rep, then the second combo box only shows that sales rep's customers, then once you pick a customer, the third combo box shows only orders for that customer.

 

We'll learn some simple database security - I'll show you how to set up a database password. We'll see how to convert between different Access versions, and how to create a secure MDE file. You can distribue an MDE file, which is a full working copy of your database, but nobody else will be able to access the Design View or VBA Code of any of the objects.

 

I'll also show you the database splitter wizard. You can create a front-end database file that has all of your forms, reports, code, and queries, and a back-end database file that contains the tables. This is the preferred method for sharing a database in an office setting.

 


ACCESS 307

Lesson 1. Custom Search Button
Docmd.FindRecord
Docmd.GoToControl
InputBox
Custom Sub with Parameter

Lesson 2. Dynamic Combo Boxes Part 1
Combo boxes that control (filter) other combo boxes
Form_BeforeUpdate
Cancelling BeforeUpdate
Verifying user data before allowing them to leave a record

Lesson 3. Dynamic Combo Boxes Part 2
ComboBox.Requery
Subform Link Master ID

Lesson 4. Simple Database Security
Set Database password
Open Database Exclusive Access
Unset password
Converting a database between versions
Creating an MDE file

Lesson 5. Splitting a Database
Database Splitter Wizard
Linking Tables
Linked Table Manager

 


 
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 307

Richard on 1/1/2007:  Custom search button, DoCmd.FindRecord, GoToControl, InputBox, BeforeUpdate, Requery, DB Password, Splitter
 Moheb on 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.
Richard Rost on 6/26/2008: Moheb, you need to limit the results with a Query, or use the CRITERIA in the DoCmd.OpenReport method.
Nathan Wittmann on 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
Richard Rost on 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.
Nathan Wittmann on 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.

Denise Nichols on 10/24/2008: I especially like how you show us how to troubleshoot a problem. That is worth a ton.
Richard Rost on 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.
Richard Rost on 10/25/2008: Thank you, Denise.
Paula on 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
1.
2.
3.
4.
5.

Thanks!


Richard Rost on 4/8/2009: Paula, I'm sorry but I don't understand your question. Can you please elaborate and give me an example?
Paula on 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:
Forms!sfrmSalesTrips!cboState

Can you provide some help here?
Thank you so much!

Richard Rost on 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:

Forms!FormName!FieldName

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

Forms!FormName!SubformName.Form!FieldName

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:

Forms!OrderF!OrderSubform.Form!OrderTotal

Hope this helps. Again, Access 310 explains this in more detail.

Paula on 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:
Forms!FormName!SubformName.Form!FieldName

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!

Richard Rost on 4/14/2009: Paula, I think I have a solution for you. I posted it as an update at the bottom of this page: http://599cd.com/tips/access/two-combo-boxes
ian dudley on 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

Richard Rost on 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.
Paula on 5/12/2009: Hello Richard,
The nested combo boxes work GREAT! I followed the info at http://599cd.com/tips/access/two-combo-boxes/ 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!

Lorraine McCord on 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
Home
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?

Thanks!

Richard Rost on 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.
Richard Rost on 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).
 Jay on 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?
Richard Rost on 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.
Cheri Parrag on 1/5/2010: Richard, in which lesson did you discuss creating that main menu with all of the task buttons? Thanks.
Richard Rost on 1/5/2010: Tasks are covered in 206 and 207. You can find the complete list of topics on the Access Course Index.
 Jerry on 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.

 Emad B. on 11/3/2010: Hi,
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.

Emad on 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.

 Emad on 11/3/2010: But How Can I use SQL Server at my work How do I install in my computer not allowed at work..do 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.

 Emad on 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.

Rick Paul on 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. :)

eddy geijselaers on 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?
Thanks

jorene arguson on 12/21/2011: how to incorporate an excel worksheet to each records?
Stephen Davanzo on 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()
LocationManagerCombo.Requery
DoCmd.GoToControl "LocationManagerCombo"
LocationManagerCombo.Value = ""
LocationManagerCombo.Dropdown
End Sub

Thanks Stephen


Reply from Richard Rost:

To set the value you could just say:

Combo1 = SomeValueID
Combo1_AfterUpdate

That would set the value in Combo1 and force it to run its update event, which should requery Combo2 for you.

Stephen on 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)
LocationManagerCombo.Requery
SpokeToListBox.Requery
OrderWriterListBox.Requery
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.

steve on 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.

Jon Wells on 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?

Thanks
Jon


Reply from Richard Rost:

That prompt usually means you have a form or field name spelled wrong.

Dimitrios Grevenitis on 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!

Alex Hedley on 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
Jyotsana Jaswal on 4/9/2014: Hi

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.

JJ

Reply from Richard Rost:

You want to store the ID in the table. The description is just for display purposes.

Tareq Salloum on 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.

Tareq Salloum on 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?
Thanks...

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.

Cheryl Hokanson on 6/13/2016: I have used your format for Dynamic Combo Boxes with the following substitutions:
SalesRep=Order(OrderCombo)
Customer=Product(ProductCombo)
Order=Operation(OperationCombo)
Successfull:
OrderCombo selects order of choice
ProductCombo diplays only products from that order
BUT.......
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?

Cheryl H on 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"

Cheryl H on 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.

Cheryl Hokanson on 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.

 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
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