Access VBA Requery ComboBox from other form

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I have an Access form with a combobox that has a button next to it where the user can add
more information to a table that is the data source of that combobox.When the user closes the form
that was opened with the button I want the combobox to automatically be refreshed with the additional data
Do I requery the combobox on the other form? or do I use some way to requery the combobox when the user goes back to it with their mouse?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Actually, if you look at the combobox, there is a property called “Listitem edit form”. If you specify your edit form name here, the user can open that form from the combobox, and the combobox will requery on its own when the edit form closed.
Software & Systems Engineer
Commented:
If the form with the combobox is loaded then you can requery the combobox like this :
Form_NameOfForm.YourCombo.Requery
Else just add the Requery all the last command in the button click that adds the data
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks Anders but there is much more involved. selections happen on the other form that influence the outcome so I either want to requery from that other form or do it when the mouse moves back to the combobox
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ok, just wanted to try the “simple” solution first.

Assuming you open the edit form from the first form:
On the edit form, create a module level variable
Private frm as Form

In the open event of the edit form:
Set frm=screen.activecontrol.Parent

In the close event of the edit form:
Frm.CONTROLNAME.Requery
Set frm=nothing
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thank you both but John had exactly what I needed
Hi,

there are several ways to solve this.

As long as your application does not need other active windows than your data entry form at this point in time it is the easiest way to use the DoCmd.OpenForm method with the WindowMode parameter "acDialog". This makes the data entry form modal and blocks all other windows until the user has finished entering (or not) and closed the data entry form.

The acDialog parameter stops the code in the calling form and so you can enter the Requery-method for your combobox directly after the DoCmd.OpenForm which will then be executed after the data entry form is closed.

It's always better to leave the control about controls to the form where they belong to, not to any external resource.

If you want to keep the rest of the application active you could also try to use an event message. This method has the advantage that you can send out a message "hey, new data was entered for combobox X" without knowing which target used that message. If you have the same combobox on several opened forms at the same time, the message can be received by all of them and each one can decide on their own what to do. If only the data entry form is opened, nothing happened - including no error message which would say "Form ... is not opened" when you try to directly requery the combobox in the data entry form.

Here is an article which I wrote some time ago about how to use own events to create a simple messaging system:
https://www.experts-exchange.com/articles/2620/How-to-synchronize-forms-using-own-events-for-example-split-form-with-any-Access-version.html

Cheers,

Christian
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks for the extra information Christian
Hi,

by the way, be careful to use the way "Form_NameOfForm" to address a form. This is the class name of the form which means, if it is not currently opened this will also automatically open the form. You can also only use this way if the form in question has a code module. If you want to make sure that you address an opened form, use the "Forms" collection instead and check with the "CurrentProjects.AllForms("NameOfTheForm").IsLoaded" way if the form is loaded before you access anything on the form.

Cheers,

Christian
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks for the advice

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial