combo filter not working?

[gwog]

Hi,

I have other Access 97 databases that this works fine in. But I can't figure out why it isn't working here.

I have a form frmBusinessUnitReveiw which has two combo boxes on it. Here is the Row Source for each:

Combo 1 "cboBusinessUnit"
SELECT SOP_MASTER_FILE.[Business Unit] FROM SOP_MASTER_FILE;

Combo 2 "comboCategory"
SELECT distinct SOP_MASTER_FILE.Category FROM SOP_MASTER_FILE WHERE (((SOP_MASTER_FILE.[Business Unit])=[Forms]![frmBusinessUnitReview]![cboBusinessUnit]));

In the after change property of cboBusinessUnit I have the following code:

Private Sub cboBusinessUnit_Change()
DoCmd.Requery (comboCategory)
End Sub

It works fine for the first time you make a selection form cboBusinessUnit. The choices in comboCategory are filtered properly.

But when you change the selection in cboBusinessUnit the choices do not change for comboCategory.

I've tried putting the same requery code in almost all of the properties of the 1st and 2nd combo box. With no success.

Any help as to what I am doing wrong is greatly appreciated!

Thanks
lisa.

 

[PHV]

In the AfterUpdate event procedure of cboBusinessUnit:
comboCategory.RowSource = "SELECT DISTINCT [Category] FROM SOP_MASTER_FILE WHERE [Business Unit]=" & cboBusinessUnit & ";"
If the table column [Business Unit] isn't defined as numeric, then:
comboCategory.RowSource = "SELECT DISTINCT [Category] FROM SOP_MASTER_FILE WHERE [Business Unit]='" & cboBusinessUnit & "';"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[gwog]

PHV,

Thank-you so much! That works perfectly!

Lisa.