Combo Box conundrum

[jb1099]

I am using two combo boxes to allow the entry of the location of an inventory item. The first combo box uses a query to populate all the building numbers. The second box will then allow the user to select only the rooms that are available from the first combo box selection. Combo box buildings (first combo box) has a requery event on the After Update event so that when the room combo box drop down is clicked it only populates based on the building selected. Currently the setup works, but when an option is selected from the room list, the room combo box defaults back to the first item in the list no matter what option is selected. Any ideas what might be causing this behavior?

 

[ck1999]

make sure the after update is on the combobox and not the form.

 

[jb1099]

Verified that event is on After update on the building combo box, but when room is selected from the list, still only selects first item in the list.

 

[ck1999]

SELECT prices.RM fROM Prices WHERE (((prices.Name)=combo2.text))

place this in the row source for the Combo1 box

replace prices with the table you are searching in and .name with the column heading of the building
combo2.text replace with your combo box name
and .rm should be name of the column of the rooms
Still use your requery on the combo box for the building
Hope this helps.

Chris

 

[TheAceMan1]

How areya jb1099 . . . . .
[ol][li] Post the [blue]RowSource[/blue] of the [purple]Room Combo.[/blue] If its a query post the SQL.[/li]
[li]post the code in the [blue]AfterUpdate[/blue] event of the [purple]Buildings Combo.[/purple][/li][/ol]

See Ya! . . . . . .

 

[jb1099]

The row source for the room combo box is:
SELECT tblBuildings.pkBuildings, tblRooms.txtRoom FROM tblBuildings INNER JOIN tblRooms ON tblBuildings.pkBuildings = tblRooms.fkBuildingID
WHERE (((tblBuildings.pkBuildings)=[forms]![frmAdd].[numBuilding]))
ORDER BY tblRooms.txtRoom;

the code for the after update event on the buildings combo is:

me.cbobuildings.requery

 

[TheAceMan1]

jb1099 . . . . .
[ol][li][blue]numBuilding[/blue] in your where clause denotes a [purple]field name which is not the same[/purple] as the combo name [blue]cbobuildings[/blue]. If cbobuildings is bound to numBuilding, then NumBuilding doesn't get updated until the record is saved. In any case, [blue]you should be pinging on the cbobuildings[/blue]. So the where clause should be:

[blue]WHERE (tblBuildings.pkBuildings)=forms!frmAdd.[purple][b]cbobuildings[/b][/purple])[/blue]

[/li]
[li]me.cbobuildings.requery by name, requeries the buildings combo not the room!. So change the code to:

[blue]   Me![purple][b]RoomComboboxName[/b][/purple] = Null
   Me![purple][b]RoomComboboxName[/b][/purple].Requery[/blue]

[/li][/ol]

See Ya! . . . . . .