Where condition on a Combo Box?

[branston]

Hi,

Is there a way to add a Where condition to a combo box? For example, I want my combo box to show column B from a table, but only where column A is the same as a field on the table.

Thanks

 

[jzwp22]

You can edit the row source property of the combo box. The row source is essentially a query which can accommodate a WHERE clause

 

[branston]

I had a go at that, and I got it to work with a specific number, but I couldn't seem to get it to reference a field on the form...
e.g:
SELECT [TblA].[ColumnB] FROM TblA WHERE "[TblA].[ColumnA] = '" & me.FieldA & "'";

Hopefully I am going wrong somewhere really obvious!!

 

[jzwp22]

The "me." reference is applicable to queries in VBA. For queries used outside of VBA, you have to use the full form reference:

SELECT [TblA].[ColumnB] FROM TblA WHERE [TblA].[ColumnA] = forms!formname!controlname;

 

[branston]

ah, that's nearly got it working - it doesn't seem to update if I change the controlname field though... Will carry on having a play, definitley closer than I was - thank you!

 

[jzwp22]

So it sounds like you are changing a value in a control on a form and you want the combo box (on the same form) to respond to that change, correct?

If so, you need to add a requery statement to the After Update event of the control that you are changing. The requery statement would look like this

me.comboboxname.requery

You'll have to substitute your actual combo box name above.

 

[branston]

brilliant - Thank you!

 

[jzwp22]

You're welcome. Good luck with your project.