Using a variable as reference to a form

[Zyborg]

I would like to call a public procedure to do some modifications in multiple forms.
The name of the forms are the same, but followed by a number. The name of the form and the number I'd like to put in a variable for the procedure to work with. I just don't know how to make the variable so VBA would accept it?

If the variable could work, then I would be able to use the same procedure to do mods in form_Test_0, form_Test_1, form_Test_2 etc...

Public Sub paintForm(Optional FormIdent = 0)

Dim varForm

'form_Test_0 is the name of the form listed in VBAs Microsoft Access Class objects

varForm = "form_Test_" & FormIdent 'Will NOT be approved

varForm = "form_Test_0" 'Works!

With varForm
.Controls("something").Property = Value
.Controls("somethingelse").Property = Value
End With


End Sub

 

[Andrzejek]

Why not just pass a UserForm as a parameter to your Sub?

Public Sub paintForm(frmForm As MSForms.UserForm, Optional FormIdent = 0)
''' Do what you want with frmForm here


End Sub

And to get to your paintForm Sub from any form, simply do:

Call paintForm(Me)

 

[BFroeschlKC]

Just checking some basic stuff here.

For this code to work:

With varForm
    .Controls("something").Property = Value
    .Controls("somethingelse").Property = Value
End With

Form "form_Test_0" would need to be open. I don't see where you open the form.

Here's some code that may help.

Public Sub openform(sFormName As String)
    Dim oForm As Form
    Dim bFormOpen As Boolean
    
    'Let's see if it's open. "Forms" is a list of open forms
    If Application.Forms.Count > 0 Then
        'We have some forms open, let's see if sFormName is already open
        For Each oForm In Forms
            If oForm.Name = sFormName Then
                'already open
                bFormOpen = True
                Exit For
            End If
        Next
    Else
        'there are no forms open
    End If
    
    If Not bFormOpen Then
        'The form may not exist, so let's handle that
        On Error Resume Next
            DoCmd.openform FormName:=sFormName
            If Err.Number <> 0 Then
                MsgBox "Error #" & Err.Number & ": " & Err.Description
            Else
                Set oForm = Forms(sFormName)
                bFormOpen = True
            End If
        On Error GoTo 0
    End If
    
    If bFormOpen Then paintForm aForm:=oForm
    
    Set oForm = Nothing
    
End Sub


Public Sub paintForm(ByVal aForm As Form)
    Dim aValue As String
    
    aValue = "whatever " & aForm.Name
    
    With aForm
        .Controls("lblSomeText").Caption = aValue
    End With
    
End Sub

 

[Zyborg]

Not quite...

What currently is accepted is (requardless if the form is open or not):

With form_Test_0
----coding----

What I want it to accept is:

varFormNo = 0
varForm = "form_Test_" & varform
With varForm
----coding----

But if I do anything like .something.property = value, I get an 'object required'. I want it to somehow accept the variable...

 

[Andrzejek]

Did you try my suggestion?

Your approach of:
With frmForm
.Controls("something").Property = Value
.Controls("somethingelse").Property = Value
End With
should work just fine....

 

[Zyborg]

Thank you, Andy

I was so focused on creating a variable that I didn't see the obvious: just use the full name!

With frmForm as Form it worked for me using Form_Test_0 or whatever in the call.

And thats why external inputs are priceless when you hit a wall....