Cascading Combos & DLookUp

[Rustin788]

Hey,

I'm run into a few issues when creating a form for our locations to input data.

• The first issue I am having is finding a way for a text box using a DLookUp to record that information in the data table. I have it set so that the supervisor can add in the Employee ID and then the employees Name shows up in the Employee field after it looks into the Employee List and matched the ID with the Name. I'm trying to get that name in the data table.
• The second issue I'm having is getting Cascading Combo boxes to work. I can get them to populate the first time, but if the initial combo box is changed, the second one does not up data. In After Update, I have tried to Requery, and setting the RowSource = to the Row Source. The data is a very simple table that has the Location (4 total) and a piece of Equipment at that location.

Thanks for any help, I think I've done just about every tutorial I could find for the Cascading Combo boxes and they never want to requery for me.

[ranman256]

the 2nd combo should look at the 1st combo as criteria
combo2 query: select city from table where [state]=forms!fMyForm!cboState

then on the form when user picks a state from combo1, you must refresh combo2 (the cities):

sub cboState_AfterUpdate()
cboCity.requery
end sub

dlookup format is:
=dlookup("[field2return]","query/table", sWhereClause)

the sWhereClause can be:
"[City]='" & cboCity & "'"

[Rustin788]

Hey, Thanks for the quick response. Here are some screen shots of my combo boxes and the Code. It still works fine on the initial dropdown but it doesn't change if it is switched.



Here is what I have for my DLookUp. It grabs the information fine, I'm still not how to get that information to save with the rest of the form in the table.

=DLookUp("[Employee_Name]","[EmployeeListTbl]","[Employee_Code]= '" & [Forms]![Copy of Audit Entry Form]![cbxEmployeeCode] & "'")

Thanks!

(Note, I'm doing everything on a test form right so ignore some of the naming schemes, they aren't permanent)

[pbaldy]

Can you attach your test db here?

You would typically not save name in related tables, just the code. What if the name changed in the future? Your old records would have the wrong name.

[ssanfu]

.... The second issue I'm having is getting Cascading Combo boxes to work. I can get them to populate the first time, but if the initial combo box is changed, the second one does not up data. In After Update, I have tried to Requery, and setting the RowSource = to the Row Source. The data is a very simple table that has the Location (4 total) and a piece of Equipment at that location.......

Here is a demo for Cascading Combo boxes (and list boxes). Make a few selections, then go back and change an earlier combo box. Look at the VBA code in the after update events...