Populate combo box based on a control in the form

[Bob Bowman]

I would like to filter the data displayed in a combo box by referencing a
control on the form in the WHERE clause of the Row Source query of the combo
box, but I haven't been successful in discovering the correct syntax for
specifying the form control. Any assistance would be greatly appreciated.

Thanks,
Bob Bowman

 

[fredg]

I would like to filter the data displayed in a combo box by referencing a
control on the form in the WHERE clause of the Row Source query of the combo
box, but I haven't been successful in discovering the correct syntax for
specifying the form control. Any assistance would be greatly appreciated.

Thanks,
Bob Bowman

Bob,
A little bit of information would go a long way here. Hard to help
when we don't know the specifics, like field and table names, as well
as the datatype of the criteria field.

In cases like this, it's always best if you post your current code.
Perhaps you just left off a dot or misplaced a quote. At the very
least, it gives us some indication of table and field names.

Leave the Rowsource of the combo box blank.

Assume a Table/Query RowSourceType.
If the criteria field is a Number datatype:

Code the AfterUpdate event of the Text box:

Me![ComboName].Rowsource = "Select MyTable.FieldA, MyTable.FieldB from
MyTable where MyTable.FieldC = " & Me![TextBoxName]

If it is Text datatype:

Me![ComboName].Rowsource = "Select MyTable.FieldA, MyTable.FieldB from
MyTable Where MyTable.FieldC = """ & Me![TextBoxName] & """"

 

[Bob Bowman]

I would like to filter the data displayed in a combo box by referencing a
control on the form in the WHERE clause of the Row Source query of the combo
box, but I haven't been successful in discovering the correct syntax for
specifying the form control. Any assistance would be greatly appreciated.

Thanks,
Bob Bowman

Bob,
A little bit of information would go a long way here. Hard to help
when we don't know the specifics, like field and table names, as well
as the datatype of the criteria field.

In cases like this, it's always best if you post your current code.
Perhaps you just left off a dot or misplaced a quote. At the very
least, it gives us some indication of table and field names.

Leave the Rowsource of the combo box blank.

Assume a Table/Query RowSourceType.
If the criteria field is a Number datatype:

Code the AfterUpdate event of the Text box:

Me![ComboName].Rowsource = "Select MyTable.FieldA, MyTable.FieldB from
MyTable where MyTable.FieldC = " & Me![TextBoxName]

If it is Text datatype:

Me![ComboName].Rowsource = "Select MyTable.FieldA, MyTable.FieldB from
MyTable Where MyTable.FieldC = """ & Me![TextBoxName] & """"

Thanks for the rapid reply Fred. My situation wasn't so complex as to
require such a flexible solution. The control I wanted to filter on is the
key for the record, so it's essentially static. My problem was that I
thought I could use the "Me" shortcut in the QueryBuilder of the combo box
Recordsource. Instead I used the Forms!<form name>.<control name> syntax and
it worked fine. All I had to do was requery the combo box in the On Current
event.

Thanks again,

Bob Bowman