Microsoft Access 307: Custom Search Button, Dynamic Combo Boxes, Security, MDE File, Split Database

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?

Add Your Questions or Comments