Combo Box Query Problem

[pooldead]

I am working on a form in my DB that will pull user information based on a user ID. Everything is working, except that I cannot get my combo boxes to populate unless the associated list box item is selected. Let me explain further.



I am currently running this code to pull the user's location information from the table:

Me.lbox_LocOut.RowSource = "SELECT tbl_Users.Location " & _
"FROM tbl_Users " & _
"WHERE ((tbl_Users.[Primary_User_ID])='" & Me.txt_SearchUser & "');"

That works fine. I am running a similar piece of code in a combo box to pull a list of applications to select from that is based on what location is selected. This code is set to run as lbox_LocOut_AfterUpdate().

Me.cbox_AppOut.RowSource = "SELECT tbl_Applications.Application_Name " & _
"FROM tbl_Applications " & _
"WHERE ((tbl_Applications.[Location_Name])='" & Me.lbox_LocOut & "');"

This code does work, but only if I click into the "lbox_LocOut" field once the information has loaded. I would like to have the combo box update automatically without having to click into the listbox.

[orange]

Lots of combobox info and examples here.

[pooldead]

Thanks orange. I'm going through that information now. I did have a thought, but I'm not sure how to make the syntax work. Within the line of code for my combo box: "WHERE ((tbl_Applications.[Location_Name])='" & Me.lbox_LocOut & "');" would it be possible to change the me.lbox_locout to be a specific field from a different table.

So for example, if I changed it to something like "WHERE ((tbl_Applications.[Location_Name])= tbl_users.location);", effectively ignoring the field in my form and going straight to the table source to find the information for the criteria.

[pooldead]

Marking this as solved. For the sake of anyone that may come across this in the future, I wanted to note what I did to make this work:

For other functions in my form to work correctly, I had to change many of my list boxes to text boxes and write a small bit of code to output the same information into the text box. After that, I just updated my WHERE statements to match the location_name to the new text box.

Code:

Text Box populate:
Me.txt_LocOut = DLookup("Location", "tbl_Users", "tbl_Users.[Primary_User_ID] = '" & Me.txt_SearchUser & "'")

Combo Box Update:
Me.cbox_AppOut.RowSource = "SELECT tbl_Applications.Application_Name " & _ "FROM tbl_Applications " & _
"WHERE ((tbl_Applications.[Location_Name])='" & Me.txt_LocOut & "');"

This has the added benefit of allowing me to manually change the data in the txt_LocOut box and automatically updating my combo boxes after tabbing out of the txt field.

[orange]

Good stuff. Glad you resolved it.