Combo based on Combo

[Guest]

I am trying to refer to a value in a column of an existing combo box to set
the criteria in the SQL Statement Query builder for what is displayed in a
second combo box.
I have tried using the criteria statements:
[Forms]![frm_EditBooking]![cbo_FromTown][Column](3) and variations thereof.
All to no avail! Can someone please help? Thanks

 

[Guest]

In the Query (SQL) the column number is not reconized, you need to either
change the column that the combo is bounded to (4 column) or use code to set
the second combo RowSource

Using the AfterUpdate event of the first combo you can write

Me.[Combo2Name].RowSource = "Select Field1,Field2,Field3 From TableName
Where Field3 = " & Me.[cbo_FromTown].Column(3)
Me.[Combo2Name].Requery


If the value of Me.[cbo_FromTown].Column(3) is text then add a single quote

Me.[Combo2Name].RowSource = "Select Field1,Field2,Field3 From TableName
Where Field3 = '" & Me.[cbo_FromTown].Column(3) & "'"
Me.[Combo2Name].Requery

Look at this link for more example:

http://www.databasedev.co.uk/filter_combo_boxes.html

 

[BruceM]

I don't know if one of the variations you tried was:
[Forms]![frm_EditBooking]![cbo_FromTown].Column(3)
but that could help. Also, do you know that the column count in this case
is 0-based, so that (3) is the fourth column?
If still no luck, post the row source SQL for the second combo box.

 

[Guest]

Thankyou for your help. I found another solution which may help others
searching this thread in the future. I used the criteria.
Eval("[Forms]![frm_EditBooking]![cbo_FromTown].Column(3)")

In the Query (SQL) the column number is not reconized, you need to either
change the column that the combo is bounded to (4 column) or use code to set
the second combo RowSource

Using the AfterUpdate event of the first combo you can write

Me.[Combo2Name].RowSource = "Select Field1,Field2,Field3 From TableName
Where Field3 = " & Me.[cbo_FromTown].Column(3)
Me.[Combo2Name].Requery


If the value of Me.[cbo_FromTown].Column(3) is text then add a single quote

Me.[Combo2Name].RowSource = "Select Field1,Field2,Field3 From TableName
Where Field3 = '" & Me.[cbo_FromTown].Column(3) & "'"
Me.[Combo2Name].Requery

Look at this link for more example:

http://www.databasedev.co.uk/filter_combo_boxes.html
--
Good Luck
BS"D

I am trying to refer to a value in a column of an existing combo box to set
the criteria in the SQL Statement Query builder for what is displayed in a
second combo box.
I have tried using the criteria statements:
[Forms]![frm_EditBooking]![cbo_FromTown][Column](3) and variations thereof.
All to no avail! Can someone please help? Thanks