CurrentForm Variable Error

[TheSearcher]

I'm trying to use a global variable to hold the name of a form. It works great and returns the correct form name but I get the error below when referencing it from within the form collection. Does anyone know why?

'On Open event of frm_X
Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm
Globals.glb_CurrentForm = frmCurrentForm.Name 'This returns the correct form name.

'Code in frm_Y
Forms!Globals.glb_CurrentForm!lst_Tasks.Requery

 

[theDBguy]

You have to use a different syntax. Try:

Forms(Globals)...

 

[TheSearcher]

Thanks DBguy. Unfortunately, that didn't work. I tried:

• Forms!(Globals).glb_CurrentForm!lst_Tasks.Requery
• Forms!(Globals.glb_CurrentForm)!lst_Tasks.Requery

and now I get this error:

 

[MarkK]

I'm trying to use a global variable

This is the hazard of using globals. You cannot know for sure that some other code hasn't changed or damaged the value you think resides there. With a global, you can only hope that it still contains the value you previously assigned it. Some other developer or user or process has full access to it. If you design a process that leverages a global, make to always run it with your fingers crossed.

 

[theDBguy]

Thanks DBguy. Unfortunately, that didn't work. I tried:

• Forms!(Globals).glb_CurrentForm!lst_Tasks.Requery
• Forms!(Globals.glb_CurrentForm)!lst_Tasks.Requery

and now I get this error:
View attachment 104444

Hi. It didn't work because you didn't follow my example. I didn't use the bang operator.

 

[TheSearcher]

Globals.glb_CurrentForm = "Forms(" & Globals.glb_CurrentForm & ")"

I removed the bang, included "Forms" in my variable and enclosed it with parentheses. Now I get the error below. Not sure how to qualify lst_Tasks.Requery. I tried the bang and a period in my variable but no luck.

It errors out on this: Forms!Globals.glb_CurrentForm!lst_Tasks.Requery
I'm not surprised because it's being read as Forms(Globals.glb_CurrentForm)lst_Tasks.Requery

 

[theDBguy]

Globals.glb_CurrentForm = "Forms(" & Globals.glb_CurrentForm & ")"

I removed the bang, included "Forms" in my variable and enclosed it with parentheses. Now I get the error below. Not sure how to qualify lst_Tasks.Requery. I tried the bang and a period in my variable but no luck.

It errors out on this: Forms!Globals.glb_CurrentForm!lst_Tasks.Requery
I'm not surprised because it's being read as Forms(Globals.glb_CurrentForm)lst_Tasks.Requery
View attachment 104445

Hmm, I gave you a very simple example, and you seem determined to not follow it. I'm not sure how to help you further. Perhaps if you could post your actual code, we can then tell you exactly how to change it. In essence, this is what I was saying:

Public Globals As String
...
Globals = "frmMain"
...
Forms(Globals).Requery 

or

Forms(Globals).Visible = False

etc.

Hope that makes sense...

 

[MajP]

Here is some of the why

Lets Kill the Me. vs Me! sticky or lets rewrite it

This discussion moved to clean thread https://www.access-programmers.co.uk/forums/threads/bang-vs-dot-me-and-default-properties.327749/

www.access-programmers.co.uk

In short Bang never works with a variable

Assume the form's name is: "SomeLiteralName"
Assume you set a variable to this name.

Dim VariableName as string
VariableName = "SomeLiteralName"

These work

forms!SomeLiteralName   'Use literal name
forms("SomeLiteralName")  ' User literal enclosed in quotes
forms(VariableName)   'Pass a variable for the index

This does not work since you are using a variable with Bang.

Forms!VariableName

So this fails because you cannot use Bang notation with a variable

'Code in frm_Y
Forms!Globals.glb_CurrentForm!lst_Tasks.Requery

Same reason here

Forms!(Globals).glb_CurrentForm!lst_Tasks.Requery
Forms!(Globals.glb_CurrentForm)!lst_Tasks.Requery

 

[cheekybuddha]

It looks like you are fully qualifying your variable name with the name of the module it resides in.

So you must adapt the syntax offered to you by @theDBguy :

Forms(Globals.glb_CurrentForm).lst_Tasks.Requery

 

[TheSearcher]

Thank you all very much for taking time with this. cheekybuddha's example above (via DBguy) definitely should have worked. Unfortunately it didn't along with everything else I've tried. It seems Access cannot recognize a variable used in this way. I will follow Pat's advise and take a completely different approach. Thanks again!

 

[MajP]

It seems Access cannot recognize a variable used in this way.

NO! Of course Access can. Just because you must be doing something incorrectly, does not mean the application can not do it. Referencing a form name with a variable is done all the time.

 

[cheekybuddha]

Is it possible that frmX is a subform at this point?

 

[theDBguy]

Thank you all very much for taking time with this. cheekybuddha's example above (via DBguy) definitely should have worked. Unfortunately it didn't along with everything else I've tried. It seems Access cannot recognize a variable used in this way. I will follow Pat's advise and take a completely different approach. Thanks again!

As I had mentioned earlier, perhaps you could consider posting your entire code, so we can tell you how to fix it.

 

[moke123]

If this is only to requery listboxes try this. I usually only have 2 or 3 forms open at the same time and this insures that all the listboxes are in sync with any changes I make to data.

Public Sub RequeryAllLists()

    Dim ctl As Control
    Dim frm As Variant

    For Each frm In CurrentProject.AllForms
        If frm.IsLoaded Then
            For Each ctl In Forms(frm.Name).Controls
                If ctl.ControlType = acListBox Then
                    ctl.Requery
                End If
            Next
        End If
    Next

End Sub

 

[TheSearcher]

moke123 - My code is not only to requery listboxes but your code is very elegant and helpful. Thanks!