Row source type refers to different table based on previous value

[tekila]

I've two fields in a form; Test and Location that are both combo boxes. The row source type of Test is a value list; FCT and ICT. The locations for FCT and ICT are stored in separate tables (FCT Locations and ICT Locations). How can I make the row source type of Location refer to the particular table based on the value selected in Test, meaning if I select FCT for Test, the data from FCT Locations table would appear in the combo box of Location?

I know it's possible to achieve this by creating a table to store Test and Location data but due to some other reasons, I want the locations for the two tests to be stored in two tables.

 

[RickSpr]

Create an AfterUpdate event procedure for Test, and use it to set the Row Source property of Location. Code something like this:
Private Sub Test_AfterUpdate()
If IsNull(Test) Then
Location.RowSource = ""
ElseIf Test = "FCT" Then
Location.RowSource = "FCT Locations"
ElseIf Test = "ICT" Then
Location.RowSource = "ICT Locations"
Else
Location.RowSource = ""
End If
End Sub
Rick Sprague

 

[tekila]

I received a compile error: method or data member not found for .RowSource

 

[rajeessh]

Is Location is the name of the combo
Else please name it correctly

 

[tekila]

Oh yah, it should be Locatn instead of location, thanks for the advice.