combo box based on previous field

[anniez]

I have a form with a field (Dept). I have a combo box with employees where employeeDept = forms.formname.dept.
It works fine - if I put in ACCT for Dept, the combobox only shows employees in ACCT.
If I add another record, change the dept to FIC, the combobox still has ACCT. Same if ACCT was an error and want to change it to another dept. When I requery the form to add another record, the Dept field is blank, but the combobox still has ACCT employees. I've spent way too much time on this one - I can only seem to clear it if I close the form and re-open it. Can you help???

 

[grtammi]

anniez:

In a Form_Current event:

<your combo box name>.Requery

By using the Form_Current event, you can have this event fire every the record changes on the form, therefore making sure that whatever record you move to, your combo box reflects the correct information.

HTH

Greg

If you don't understand, just nod and smile ...

 

[anniez]

This doesn't seem to help.
comboboxA - selects the dept (acct)
comboboxB - selects all employees who are in the comboboxA dept (acct).
Once comboboxB has been opened, it will only look at those employees in acct - even if I change the dept in comboboxA.
If I add a record and select another dept, it still only looks at employees in acct.
When I put the command comboboxB.requery in form_current, it selects ALL employees and not just the ones in comboboxA.
I want comboboxB to match the selection currently in comboA, not just what was selected first. Thanks

 

[JoyInOK]

You might try this:

In the Click event of combo A, add this code
ComboB.RowSource = &quot;SELECT DISTINCT NameOfEmployeeTable.FieldToDisplayInComboB FROM NameofEmplyoeeTable WHERE (((NameOfEmployeeTable.EmployeeDepartment) = '&quot; & Forms!NameOfYourForm!comboA.Value & &quot;'));&quot;
ComboB.Requery