Synchronizing Combo Boxes

[LisaP]

This is probably a very easy question (but hey its late in the week).

I have a table [TblKit] which has two fields, floor and platform. On my form i have two combo boxes (named floor and platform). What i want is for the user to choose a floor and then to list the platforms found on that floor in the second combo box. Does anyone know how to populate a combo based on selection in another combo?

 

[tc3of4]

Hello,

assuming the following:

combo_Floor and combo_Platform

Within the Floor combobox Event tab, select the AfterUpdate event.

You can use this as the code

Private Sub combo_Floor_AfterUpdate()

combo_Platform.RowSourceType = "Table/Query"

combo_Platform.RecordSource =
"SELECT TblKit.platform " & _
"FROM TblKit " & _
"WHERE (TblKit.floor = '" & combo_Floor.Value & "')"

'/** Note: If the floor value is stored as a number and not as text then remove the left and right single quotes. If not, then keep the single quotes for text values.

combo_Platform.Requery

Exit Sub

What this does is that every time you select a value from the Floor combo box, you will have combo_Platform recordsource requery with the new values from the SQL.

Hope this helps. If you need more clarification just ask again or e-mail me.

tc3of4

 

[ntp]

Write an SQL Statement or create a query which will return the fields you want to display in the combo box with the criteria being tested against some value from the first combo box.

This query will be the rowsource for the second combo box. Each time a selection is made in the first combo box, requery the second combo box or reapply the SQL statement if you did not create a permamnant query.

Ex.

Private Sub cboYard_AfterUpdate()
Dim StrSQL As String

StrSQL = "SELECT tblkBayListing.BaySYSID, tblkBayListing.BayNumber " _
& "FROM tblkYardListing INNER JOIN tblkBayListing ON " _
& "tblkYardListing.YardNumber = tblkBayListing.YardNumber " _
& "WHERE (((tblkYardListing.YardNumber)= '" & cboYard.Value & "'));"
cboBay.RowSource = StrSQL
cboBay.Enabled = True
cboBay.Requery
End Sub

This procedure causes the combo box cboBay to display values that are valid for the item selected in the combo control cboYard.

ntp

 

[ntp]

Hadn't realised someone else had answered. Same answer anyway only difference is I had the second combo box disabled until the user enters something into the first combo box. A validation measure.

ntp