Update Combobox List from another value

[Joakim N]

Hi,

I'm a beginner of Access.

I have a form JobF. When I have selected a value in combobox Unit, I want to update the list in Machine No. I have written a query MachineNo_CriteriaUnitQ that is the RowSource to combobox for Machine No. In this query I have written a criterion:

[Forms]![JobF]![cboUnit]



I'm not sure if above criterion is correct. If it is correct I need something to trigger that the value in combobox Unit has changed and then update the list in combobox Machine No by running the query MachineNo_CriteriaUnitQ. I believe I need to use event AfterUpdate or something but I don't know what to write.

I hope I have made it clear. My database is attached.

[pbaldy]

Sounds like you're trying to do this:

http://www.baldyweb.com/CascadingCombo.htm

[Joakim N]

Thanks,

That's what I'm looking for.

However, the VBA code doesn't start when I chamge value in cboPlanning. I have added a Breakpoint in the code so it seems it doesn't trigger. Do I have to switch on VBA macros somewhere in Access? I have looked in Trust Center and I believe I have turned on macros. I know how it works in Excel. But Access is new to me.

Private Sub cboPlanning_AfterUpdate()
    With Me
        .cboMachineNo.Value = vbNullString
        .cboMachineNo.Requery
    End With
End Sub

[pbaldy]

Yes, either the database must be in a trusted location or you need to explicitly enable code. There would be a button under the ribbon typically.

[Joakim N]

Thanks,

Now the macro is running.

However I don't get my 2nd combobox (cboMachineNo) to update its list when value in the 1st combobox (cboUnit) changes in form JobF. I believe I'm writing something wrong in the criterion "[Forms]![JobF]![cboUnit]" in query MachineNo_CriteriaUnitQ below. I have attached an image of the query beneath. I have also attached a cleaned database for easier trouble shoot only showing this issue.

[pbaldy]

Try changing your query to:

SELECT MachineNoT.ID_MachineNo, MachineNoT.MachineNo
FROM MachineNoT
WHERE (((MachineNoT.ID_Unit)=[Forms]![JobF]![cboUnit]))
ORDER BY MachineNoT.MachineNo;

[Joakim N]

Thanks,

Worked great!

[pbaldy]

Happy to help!