Update form in access when form is not open using VBA

Question

I developed a form in Access that allows users to search for records that match their parameters. The form has about five combo boxes that contain values which a user can search. A query is set up that pulls the values from the combo boxes. An associated form, "F_FilterResults", lists the results of the query as a datasheet that display the results of the search. I created an AfterUpdate event on each combo box on "F_FilterResults" so that after I update the combo box with a search parameter the query and form would refresh itself. Here's some sample code from the AfterUpdate event in the combo boxes on "F_FilterResults".

Private Sub CompanyState_AfterUpdate()
Forms![F_FilterResults].Requery
Forms![F_FilterResults].Refresh
End Sub

This only works however if the "F_FilterResults" form is open. When it is closed I get Run-time error '2450' message that indicates that the "Microsoft Access cannot find the referenced form 'F_Filter.' How can I have this code run when the "F_Filter" form is not open? Note I do not want the form to open right after you select a search parameter from a combo box since the user may want to add more search parameters to the other combo boxes. If I program "F_FilterResults" to open on loading the search form the user may X out it and would not know what the error message means.

What exactly do you want it to do? You can't refresh/requery a closed form (because there would be no point as it gets requeried/refreshed as it's loaded). — Erik A
I never user dynamic parameterized queries. Apply filter criteria to a form when it opens. Review allenbrowne.com/ser-62.html — June7

SunKnight0 SunKnight0 · Accepted Answer · 2017-05-10T19:19:17

You can open a form hidden. That is the "easy" answer. However the need to do this means that the way you go about achieving your result is not the best.

The way I would do it is that instead of updating a form that pulls values off the combo boxes I would generate a string that can be applied as a filter to the form when it is opened.

Update form in access when form is not open using VBA

1 Answers