Access VBA Requery ComboBox from other form
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?
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?
Anders Ebro (Microsoft MVP)
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.P
In the close event of the edit form:
Frm.CONTROLNAME.Requery
Set frm=nothing
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.P
In the close event of the edit form:
Frm.CONTROLNAME.Requery
Set frm=nothing
ASKER
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
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
ASKER
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(
Cheers,
Christian
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(
Cheers,
Christian
ASKER
Thanks for the advice