Filter Combo Box Based on Selection in Different Combo Box
I have a single form that has two combo boxes. One of these is [Current Facility] and one is [Current Room]. I have been trying for many unsuccessful hours to come up with a way to fillter the [Current Room] based on the selection that is made for [Current Facility]. The values for [Current Facility] are determined based on a table Facilities and the values for [Current Room] are based on a table Rooms that link the facility to the room.
Can someone please help me with a solution to this? Right now, the [Current Room] combo box displays all 150 rooms. This is a problem because it could mess up reporting if people select one facility and a room that is not in that facility.
I thought it would be simple to have the [Current Room] combo box be filtered by what the user selects in the [Current Facility] box.
Can someone please help me with a solution to this? Right now, the [Current Room] combo box displays all 150 rooms. This is a problem because it could mess up reporting if people select one facility and a room that is not in that facility.
I thought it would be simple to have the [Current Room] combo box be filtered by what the user selects in the [Current Facility] box.
ASKER
answer this q
what is the field that link the tables Facility and Rooms?
The field is Facility
what is the rowSource of Combo1?
SELECT Facilities.Facility FROM Facilities;
what is the rowSource of combo2?
SELECT Rooms.Room FROM Rooms;
what is the field that link the tables Facility and Rooms?
The field is Facility
what is the rowSource of Combo1?
SELECT Facilities.Facility FROM Facilities;
what is the rowSource of combo2?
SELECT Rooms.Room FROM Rooms;
in the after update event of combo1
private sub combo1_afterupdate()
me.combo2.rowsource="selec
end sub
this is assuming that the facility field is Text Data Type
if it is Number use this
private sub combo1_afterupdate()
me.combo2.rowsource="selec
end sub
private sub combo1_afterupdate()
me.combo2.rowsource="selec
end sub
this is assuming that the facility field is Text Data Type
if it is Number use this
private sub combo1_afterupdate()
me.combo2.rowsource="selec
end sub
ASKER
Ok...so we seem to be on the right track, but still have a slight problem. I have entered in the code as follows, but I get "Syntax error (missing operator) in query expression 'facility=MCN III (Barrier)'. -- which is replaced by any of the facilities that I select, and nothing is available in the dropdown list.
Private Sub Current_Facility_AfterUpdate()
Me.Current_Room.RowSource = "Select room from rooms where facility=" & Me.Current_Facility & ""
End Sub
use this
Private Sub Current_Facility_AfterUpda
Me.Current_Room.RowSource = "Select room from rooms where facility=" & chr(34) & Me.Current_Facility & chr(34)
End Sub
ASKER
That won't work. Now I get the "data type mismatch in criteria expression"
I tried removing the & Chr(34), but got the Syntax error again.
I tried removing the & Chr(34), but got the Syntax error again.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have a look at the attached sample in which the combo box in the subform is filtered based on the selection made from the listbox on form1. Have a look at the where condition of the query that is used as the control source of the combo box.
You can use a similar method for your case
sample1.mdb
You can use a similar method for your case
sample1.mdb
ASKER
sb9,
Tried your solution, with no results. This turns up a blank dropdown list.
capricorn1,
I'm not sure how to upload my database. Can you give instructions? I know I saw some on here at one time, but when I actually need them I cannot find them.
Tried your solution, with no results. This turns up a blank dropdown list.
capricorn1,
I'm not sure how to upload my database. Can you give instructions? I know I saw some on here at one time, but when I actually need them I cannot find them.
if your db is .accdb add an .txt extension i.e.,
if the name is myDB.accdb, create a copy and rename it copymyDB.accdb.txt
check the Attach File below to initiate upload
if the name is myDB.accdb, create a copy and rename it copymyDB.accdb.txt
check the Attach File below to initiate upload
did you open form1,select one of the items in the listbox then click on the dropdown box in the subform
ASKER
Capricorn1,
I figured out how to get it uploaded, thank you. It is attached to this post.
sb9,
There is no subform, so maybe that is why your solution did not work. I did select an item in the listbox, but the dropdown box would be blank.
Copy-of-Transfer-Database.accdb.txt
I figured out how to get it uploaded, thank you. It is attached to this post.
sb9,
There is no subform, so maybe that is why your solution did not work. I did select an item in the listbox, but the dropdown box would be blank.
Copy-of-Transfer-Database.accdb.txt
RLHunter,
upload an accdb not an accde.
upload an accdb not an accde.
ASKER
That is an accdb.
it is NOT an accdb, believe me when i said it is an accde.
nevermind.. see this sample db
Database1.accdb.txt
nevermind.. see this sample db
Database1.accdb.txt
ASKER
None of these have worked. Does anyone have any suggestions on how I can accomplish this? I simply want to filter a combo box based on what was selected in a different combo box. In my database, there are two tables (facility and room) and one form. The combo box for facility pulls information from the facility table. I want the room combo box to pull the rooms from the room table but only show the rooms that are in a certain facility. I have it currently where all of the rooms populate, but I only want the ones that belong in the facility that was selected.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes. This is how you should do it. but, you have to properly identify the rowsource of the second combo by linking the two tables.
answer this q
what is the field that link the tables Facility and Rooms?
what is the rowSource of Combo1?
what is the rowSource of combo2?