Cascading Combo boxes that won't update

[neha]

This problem has me gripping my hair in frustration.

I have two combo boxes 'cboType' and 'cboDepartment'. cboType contains a list of all types of medical devices and cboDepartment contains a list of all the departments that the devices can be found in.

The cboType combo box has the following code in its Row Source property:
SELECT DISTINCT EquipList.Type FROM EquipList ORDER BY EquipList.Type;

The cboDepartment combo box has the following code in its Row Source property:
SELECT EquipList.Department FROM EquipList WHERE (((EquipList.Type)=[cboType])) ORDER BY EquipList.Department;

Where EquipList is a table with the following fields:
ID (Primary key, Autonumber)
Type
Department

This works fine for me and the combo boxes cascade perfectly. cboDepartment shows only those values based on the choice in cboType.

However, when I change the entry in cboType, cboDepartment does not refresh. It still shows the previous values.

I tried the following things but none of them work:

1. Including "Me.cboDepartment.Requery" in the AfterUpdate property of cboType
1. Including "[Forms]![FormName]!cboDepartment.Requery" in the AfterUpdate property of cboType, just in case Access didn't realise which combo box it is that I want to requery.


3. Including the SQL of the RowSource property of cboDepartment in the AfterUpdate property of cboType.

I did all kinds of other things like include the code "Me.cboDepartment.Requery" in the OnChange property of cboType and even on the OnCurrent property of the form itself, but it doesn't work at all!!!

how do I get these combo boxes to synchronise?

[RuralGuy]

Try changing the RowSource of the cboDepartment to:
SELECT EquipList.Department FROM EquipList WHERE (((EquipList.Type)=" & Me.cboType & ")) ORDER BY EquipList.Department;

[neha]

Dear Rural Guy,

Thank you for your reply. Unfortunately, making the change you mentioned didn't help. In fact, until now, every time I make a change in cboType, I hit the Refresh button on the ribbon under the Home>>Records tab (I'm using Access 2007). This at least refreshes the record and the combo box updates. But making the change you suggested gets rid of this too. How would this change help?

Would it be helpful to mention that this form has several tabs? Does that make a difference?

My knowledge of SQL is pretty rudimentary and I've built most of my database using the GUI. But I'm a fast learner...Please keep the suggestions coming!

[RuralGuy]

In that case put:
SELECT EquipList.Department FROM EquipList WHERE (((EquipList.Type)=FORMS.YourFormName.cboType )) ORDER BY EquipList.Department;
...in the RowSource of the cboDepartment ComboBox (using your actual YourFormName of course)
Be sure there is a Me.cboDepartment.Requery in the AfterUpdate event of the cboType ComboBox.

[neha]

Nope
Still doesn't work!

I've put a "Refresh" command button on my form that refreshes the entire record when you make a change, but this isn't a nice solution at all. I can't seem to figure out what's going wrong!

[neha]

for now I've solved the problem by adding a Macro to the AfterUpdate property that requeries cboDepartment.

However, its still bothering me as to why a simple Me.ContorlName.Requery wouldn't work in my case

[neha]

I've noticed that in my form, I can Requery text boxes but I can't Requery combo boxes unless I use a macro.

[RuralGuy]

Put a "Me.YourComboBoxName.Requery" in the OnEnter event of the ComboBox then.