Refresh/Requery Calling Form

[Jeff PC via AccessMonster.com]

(Access 97) I have an update form that can be called from multiple forms in
my app. I would like to be able to just have the update form refresh/requery
the calling form, whichever form it was. I've searched this forum and tried
using the OpenArgs property in the calling forms to pass the calling form's
name. So now I can generate a MsgBox in my update form with the name of the
calling form. But then Access doesn't seem to have an Exec() equivalent that
will let me run dynamically generated code,such as:

Exec("Forms![" & Me.OpenArgs & "].requery")

There must be some other way that I am completely missing.

Any help would be appreciated.

 

[Shane S via AccessMonster.com]

Hey Jeff,

If I understand your question, here's an example of what I do. Using
OpenArgs from the calling form, I put the following code in the Close Event
of the called form

If Not(IsNull(Me.OpenArgs)) Then
Select Case Me.OpenArgs
Case "CallingFormsName"
Forms![CallingFormsName].Requery
End Select
End If

I use the Select Case to allow for me to put each and every form that could
call this form.

Hope this helps,
Shane

(Access 97) I have an update form that can be called from multiple forms in
my app. I would like to be able to just have the update form refresh/requery
the calling form, whichever form it was. I've searched this forum and tried
using the OpenArgs property in the calling forms to pass the calling form's
name. So now I can generate a MsgBox in my update form with the name of the
calling form. But then Access doesn't seem to have an Exec() equivalent that
will let me run dynamically generated code,such as:

Exec("Forms![" & Me.OpenArgs & "].requery")

There must be some other way that I am completely missing.

Any help would be appreciated.

 

[Allen Browne]

How about:
Forms(Me.OpenArgs).Requery

 

[Dirk Goldgar]

(Access 97) I have an update form that can be called from multiple
forms in my app. I would like to be able to just have the update
form refresh/requery the calling form, whichever form it was. I've
searched this forum and tried using the OpenArgs property in the
calling forms to pass the calling form's name. So now I can generate
a MsgBox in my update form with the name of the calling form. But
then Access doesn't seem to have an Exec() equivalent that will let
me run dynamically generated code,such as:

Exec("Forms![" & Me.OpenArgs & "].requery")

There must be some other way that I am completely missing.

Any help would be appreciated.

You can use a string value or variable as an index into the Forms
collection, so all you need to say (after having verified that OpenArgs
actually contains *something*) is this:

Forms(Me.OpenArgs).Requery

 

[Jeff PC via AccessMonster.com]

Dirk,

This is looking very promising. I think this is the bit I was missing.

BUt what if the form which called my Update form was actually a subform? In
this case the code below doesn't appear to work. Do we need to: check for a
parent and then if there is one, refresh the parent ,but if there isn't a
parent just requery the form (as you suggest)?

Original Code:
If Not IsNull(Me.OpenArgs) Then
Forms(Me.OpenArgs).Requery
End If

Possibly Instead:
If Not IsNull(Me.OpenArgs) Then
if IsNull(Forms(Me.OpenArgs).Parent)
Forms(Me.OpenArgs).Requery
Else
Forms(Me.OpenArgs).Parent.Refresh
End If
End If

Regards
Jeff P-C

 

[Dirk Goldgar]

Dirk,

This is looking very promising. I think this is the bit I was
missing.

BUt what if the form which called my Update form was actually a
subform? In this case the code below doesn't appear to work. Do we
need to: check for a parent and then if there is one, refresh the
parent ,but if there isn't a parent just requery the form (as you
suggest)?

Original Code:
If Not IsNull(Me.OpenArgs) Then
Forms(Me.OpenArgs).Requery
End If

Possibly Instead:
If Not IsNull(Me.OpenArgs) Then
if IsNull(Forms(Me.OpenArgs).Parent)
Forms(Me.OpenArgs).Requery
Else
Forms(Me.OpenArgs).Parent.Refresh
End If
End If

Regards
Jeff P-C

No, that won't work, because the subform is not a member of the Forms
collection. And you could even have a subform which is nested within a
subform, or even more than 2 levels deep.

The possibility that you might need to requery a subform hadn't occurred
to me. Try this approach and see if it works. Requery the form using
the following code:

Dim strFormSpec As String
Dim varDummy As Variant

strFormSpec = Me.OpenArgs & vbNullString
If Len(strFormSpec) > 0 Then
varDummy = Eval("Forms!" & strFormSpec & ".Requery")
End If

Now, if you are calling the Update form from a main form, call it like
this:

DoCmd.OpenForm "UpdateForm", OpenArgs:=Me.Name

If you're calling it from a subform that is one level deep, call it like
this:

DoCmd.OpenForm "UpdateForm", _
OpenArgs:=Me.Parent.Name & "!" & Me.Name

If you're calling it from a subform that is two levels deep, then call
it like this:

DoCmd.OpenForm "UpdateForm", _
OpenArgs:=Me.Parent.Parent.Name & "!" & _
Me.Parent.Name & "!" & Me.Name

And so on. In other words, for a main form, you'd pass a string like
this: "MainFormName". For a first-level subform, you'd pass a string
like this: "MainFormName!SubformName". For a second-level subform,
you'd pass a string like this:
"MainFormName!SubformName!SubSubformName".

I'm not sure that will work, but I think it will.

 

[Jeff PC via AccessMonster.com]

Above text removed for effciiency

Dirk,

Excellent. it looks like Eval does do the job of an Exec(). The trick was
enclosing the string to be evaluated in Chr$(34) (i.e. quotes). This is the
final code I used that did the job:

Dim strFormSpec As String
Dim varDummy As Variant

strFormSpec = Chr$(34) & "Forms![" & Me.OpenArgs & vbNullString & "].
Requery" & Chr$(34)
If Len(strFormSpec) > 0 Then
varDummy = Eval(strFormSpec)
End If

The testing I've done so far shows the OpenForm code you've quoted works for
Forms and Subforms (I haven't tested for sub-sub forms, but induction says...)
.. So thanks a lot. In fact you've opened up a whole new world of dynamic
run-time coding in MS-Access to me.

 

[Dirk Goldgar]

Above text removed for effciiency

Dirk,

Excellent. it looks like Eval does do the job of an Exec(). The
trick was enclosing the string to be evaluated in Chr$(34) (i.e.
quotes). This is the final code I used that did the job:

Dim strFormSpec As String
Dim varDummy As Variant

strFormSpec = Chr$(34) & "Forms![" & Me.OpenArgs & vbNullString &
"]. Requery" & Chr$(34)
If Len(strFormSpec) > 0 Then
varDummy = Eval(strFormSpec)
End If

The testing I've done so far shows the OpenForm code you've quoted
works for Forms and Subforms (I haven't tested for sub-sub forms, but
induction says...) . So thanks a lot. In fact you've opened up a
whole new world of dynamic run-time coding in MS-Access to me.

I'm a bit confused. I don't *think* I needed to put quotes into
strFormSpec. Also, the way you're building strFormSpec in the code
above, it is impossible that it could ever have a length of 0.