Update a form's data output by changing the query to which it's bound without closing/reopening form

Wadley1957
Wadley1957 used Ask the Experts™
on
Hi. I have a form "frmHST"  that opens from another form. frmHST's source data is a Select Query generated by user field selections on that source form where a little VBA code organizes into a QueryDef, providing the data set for frmHST which it then opens. While still open, I would like the user to have the option of generating a new queryDef from within frmHST and regenerate the form based on these new data. The code for revising the query is easy. How do I cause frmHST to display data based on the revised query. Refresh? Requery? Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Just set the recordsource to the new query.  (me.recordsource = SQLStringVariable)
It will automatically requery the form.
Top Expert 2016
Commented:
try
from the source form module, after revising the Querydef run this code
      Forms!frmHST.requery

how do you open the form frmHST? post the codes.
Top Expert 2016

Commented:
you can do a test if the form frmHST is already loaded

if currentproject.allforms("frmHST").isloaded then
      Forms!frmHST.requery
      else
      docmd.openform "frmHST"
end if
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Thanks for your fast response!
me.recordsource = SQLStringVariable worked! However, the form, frmHST is a subForm (continuous) that displays the filtered records, contained in a main form which has textBoxes where "=Sum()" functions summarize the subform data. How can I set the record source of the subform?

Author

Commented:
Let's say the main form is "frmHST" and the subform is "sFrmHST"
Top Expert 2016

Commented:
Wadley1957,

<the form, frmHST is a subForm (continuous) >

you should have mentioned this in your original post.

Author

Commented:
Yes. I see that I should have.
So, to clarify, frmHST is a form that contains subform sFrmHST. Both have the same source data, queryDef, "qtyTEMP". Initially, frmHST is opened from yet a third form, frmMain. frmMain has a button, cmdLookUp that creates the queryDef using the "LIKE * & myField & *" string to establish the underlying data set for frmHST which it then opens.
My intention is to give the user the opportunity to type another, substitute myField string to try the query again, only to do this directly from frmHST. The info being sought, here, may require the user's intuition and my require several "best guess" attempts before hitting on the correct item.
I think I've got it.

On the "AfterUpdate" event of the field of frmHST into which I enter the criteria for the renewed queryDef, I redefine the queryDef using the string entered (in strSQL), and these 2 code lines seem to get the form and it's subform synchronized with the user's new criteria.

Me.sFrmHST.Form.RecordSource = strSQL
Me.RecordSource = strSQL

Thanks a bunch. I'm splitting the 500 points evenly between both of you. All of your advice is spot on. Thanks!
--Greg

Author

Commented:
Very fast and accurate responses. Thanks!

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