How do I requery a combobox every time a new record is added or existing record is updated?

Question

I'm trying to requery a combobox in a Form in Access so that it updates every time A) Any record is updated in the table B) A new record is created.

I understand from this question that I need to use the requery method, but what I don't understand is how to use an event to trigger it.

So far, I've opened my form and changed the "Has Module" property to "Yes". Then I opened the VBA editor, found my form, and double-clicked to open what I think is the module connected to the form. Then below Option Compare Database I typed "Item.ContainerCombo.Requery" (name of the table, name of the combobox, requery method.)

I know this won't do anything because it's not connected to an event, but I don't know which event to use. Can anyone help?

Please be patient as I have no experience using VBA in Microsoft Access.

Why requery from the form containing the combo box? If it is closed when data is modified, opening it will load the data to the combo box. Otherwise, ReQuery the Combo-box from the form editing the data: Forms(FormName).Controls("ContainerCombo").Requery — marlan

Parfait Parfait · Accepted Answer · 2019-05-19T15:45:56

Generally, VBA (not to be conflated with VB, VB.Net, or VBS but the MS Office application code) is often considered a trigger language in that events fire code.

In MS Access, these events occur mostly on forms and reports often due to user interaction. You do not need to jump directly into the VBA Editor to write neeeded code but locate the event (AfterUpdate, BeforeInsert, OnClick, OnCurrent, OnOpen, OnClose) needed in Property Sheet under Event tab and then enable code which will auto write the subroutine shell for you to then add your .Requery line. In fact, simpler lines like .Requery can even be handled with macros (not to be confused with Excel macros). Access methods do not run outside of a subroutine or function such as your attempt.

Specifically, you mention:

so that [combobox] updates every time A) Any record is updated in the table B) A new record is created

This is not quite clear on the timing and location of needed requery call. Therefore, you need to answer:

When does this table B update or insert? By user, by automated code, or by outside event? This identifies the type of trigger event (i.e., AfterUpdate vs OnClick).
Where does this table B update or insert? On the very form you use or a different form? Is the very form bound to table B? This identifies the location of trigger (i.e., Form A's AfterUpdate not Form B's AfterUpdate).
- If trigger is run outside of the form where combobox resides, you need to change the relative reference (myCombboxName.Requery or Me.myCombboxName.Requery) to absolute reference: Forms!myFormName!myCombboxName.Requery.

Once trigger type and location is identified, you can then place your cursor under Property Sheet in the corresponding event of corresponding form and then enable code such as one of the below subroutines:

Private Sub Form_AfterUpdate()
   Forms!myFormName!myCombboxName.Requery
End Sub

Private Sub Form_AfterInsert()
   Forms!myFormName!myCombboxName.Requery
End Sub

Private Sub myButton_OnClick()
   Forms!myFormName!myCombboxName.Requery
End Sub

How do I requery a combobox every time a new record is added or existing record is updated?

1 Answers