Link to home
Start Free TrialLog in
Avatar of Fritz Paul
Fritz PaulFlag for South Africa

asked on

How to requery/refresh another form in VBA if you only know its name as a string.

I open a form frmX from different forms, say frmA and frmB.
When I close frmX, I want the applicable form frmA or frmB to be requeried/refreshed.
When I had only one form (frmA) opening frmX, I had a line of code to requery frmA like    
Forms!frmtblordersHistory.Requery

Open in new window


Now that I have more forms opening frmX I have a control on frmX, ctlOpeningForm, that is set to the name of the opening form when opened. Now when I close frmX I want the applicable opening form to be requeried. How do I do that? I tried diffrent things that do not work. One is below.

Private Sub ctlClose_Click()
Dim sFormToRequery As String

    sFormToRequery = Me.ctlOpeningForm   'I get error: Compile error Invalid quelifier.
    DoCmd.close
    sFormToRequery.Requery
'    Forms!frmA.Requery  'Previously worked when I only had frmA as opening form.

End Sub

Open in new window

Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

You need to reference the base form (with base form i mean the form that is calling formX)
Put this in a separate module
Function gIsLoaded(strName As String, _
                   Optional lngtype As AcObjectType = acForm) As Boolean
    gIsLoaded = (SysCmd(acSysCmdGetObjectState, _
                        lngtype, strName) <> 0)

End Function

Open in new window

and go like this on the close event of formX
If gIsLoaded(FormA) Then Form_FormA.Requery

Open in new window

In order for this to work properly you need to each of the forms you want to Requery to have code behind....
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you had a variable higher in scope than a routine, then you could set the variable = companion form that you opened.  Then you could reference the companion form by using that variable.

Module Example:
Option Explicit
Dim frmCompanion As Form

Open in new window

Avatar of Fritz Paul

ASKER

Thanks to all. This did it for me.
Just to add a bit, you can refer to controls like this as well:

Forms(sFormName)(sControlName).Requery

 Access actually converts all your references to this parentheses and quote format internally.

Jim.