Set Variable by Form/Report Name

[md57780]

I am curious if there is a way to set a variable to an object type in Access by the name of the form or report object.

For example, if I have a form named "frmSwitchboard", is there a way to set a variable to this by using the actual form name?

A simplified example...

Private Sub OpenSwitchboard()
     Call OpenMyForm "frmSwitchboard"
End Sub
 
Private Sub OpenMyForm(fName as string)
Dim frm as Form
     Set frm = New Form(fName)
     frm.Visible = True
End Sub

May not be possible, but thought I'd ask.

 

[Rabbie]

This code should work for you

Private Sub OpenSwitchboard()
     Call OpenMyForm "frmSwitchboard"
End Sub
 
Private Sub OpenMyForm(fName as string)
  DoCmd.OpenForm fName
End Sub

 

[md57780]

Thanks for the speedy reply. While the DoCmd method would work, it still does not resolve my initial question.

I want to set a variable as an instance of a form/report by using the form/report name. There are situations where multiple copies of the same form may be required to be open simutaneously, and (to my knowledge) the DoCmd method doesn't allow for this.

Don't know if this will help, but here's a more detailed view of my code. I basically want to pass an ID value and the form / report name, and allow my code to initialize a variable based on the form / report name passed. The line in question has been bolded.

Private Sub OpenMyForm(ID As Long, frmName As String)
On Error GoTo ErrorTrap
Dim frm As Form, frmCaption As String
 
    Select Case frmName
        Case "Account"
            frmCaption = "View Account  [id:" & ID & "]"
        Case "Client"
            frmCaption = "View Client  [id:" & ID & "]"
        Case Else
            frmCaption = ""
    End Select
 
    For Each frm In Forms
        If frm.Caption = frmCaption Then
        Set frm = Nothing
    Next frm
 
    If frm Is Nothing Then
        [B]Set frm = New Form(frmName) ' How?[/B]
        frm.Visible = True
        If Not frmCaption = "" Then frm.Caption = frmCaption
        clnForms.Add Item:=frm, Key:=CStr(frm.hWnd)
        frm.InitForm ID
    Else
        frm.SetFocus
        DoCmd.Restore
    End If
 
ExitSub:
    Set frm = Nothing
    Exit Sub
 
ErrorTrap:
    MsgBox Err.Description, vbCritical, "OpenMyForm"
    Resume ExitSub
 
End Sub

Thanks again!

 

[boblarson]

I think you want to read this by Access MVP Allen Browne.

 

[md57780]

Appreciate the reply. I have read that site, and have based my form management from it. However, it still does not address my initial question...

In the example in the link, a new instance of a form is created by hard-coding the form code name into the procedure. This is what I am currently doing.

I'm curious if there is a way to initialize the variable by passing the name of the form I want to copy instead of hard-coding it?

Go from: Set frm = New Form_frmClient
To this: Set frm = New Form("form_name")

This way, the frm variable is initialized to the correct form at runtime.

My other option would be to declare a second form variable, and use multiple "Set" statements within the "Select" block, and initialize based on the form name passed, but I have other situations where having the runtime solution would be useful.

Thanks again!

 

[ChrisO]

md57780

I have seen this attempted over the years without a suitable method.

The closest I have seen is the Select Case method you mention so if you find the solution you desire please let us know.

Total airware at the moment because I haven’t tried it…yet.

An instance of a Template Form could be opened.
That instance of the Template would have a sub-form control on it.
That sub-form control has a Source Object, a Form.
That Source Object property is a string.
Set the Source Object property of the sub-form control to the name of the Form you pass.

I did say airware so I’ll have to go and have a play with it…don’t hold your breath.

Chris.

 

[ChrisO]

The airware seems to work okay: -

Option Explicit
Option Compare Text


Public clnForms As New Collection


Sub TestIt()

    OpenMyForm 999, "Client"

End Sub


Private Sub OpenMyForm(ID As Long, frmName As String)
    Dim frm        As Access.Form
    Dim strCaption As String
 
    On Error GoTo ErrorTrap
 
    strCaption = "View " & frmName & " [id:" & ID & "]"
            
    For Each frm In Forms
        If frm.Caption = strCaption Then Set frm = Nothing
    Next frm
 
    If frm Is Nothing Then
        Set frm = New Form_frmTemplate
        frm.Visible = True
        
        If Not strCaption = "" Then frm.Caption = strCaption
        clnForms.Add Item:=frm, Key:=CStr(frm.Hwnd)
        
        frm.ctlTemplateSubform.SourceObject = frmName
        frm.ctlTemplateSubform.Form.InitForm ID
    Else
        frm.SetFocus
        DoCmd.Restore
    End If
 
ExitSub:
    Set frm = Nothing
    Exit Sub
 
ErrorTrap:
    MsgBox Err.Description, vbCritical, "OpenMyForm"
    Resume ExitSub
 
End Sub

Small A2003 demo attached.

Chris.

 

[vbaInet]

I like Chris' idea of using a subform's source object.

With regards creating a new instance of a form using the form name, it's not possible unless you use a bunch of CASE statements like you've already figured out or go with Chris' great idea of embedding the form in a subform control.

 

[md57780]

Thanks to all for suggestions! Really appreciate all the feedback.

The subform technique looks promising. I'll continue moving forward with this in mind. If I happen to run across a method for my initial inquiry I'll post back, but for now I'll go with what we have here.

Thanks again!

 

[ChrisO]

Actually, the more I look at it I think it is the only way you will be able to use a variable name.

You will also have to resize the instance of the Template form, and the sub-form control of that instance, based on the size of the instance of the form being loaded into it. Continuous forms may prove a problem but we can probably resize on the number of records required for display e.g. 10 x detail height.

The sizing requirement may also rule out using the same technique for Reports because they can resize at runtime.

I’ll have more of a play with the resizing of the Form later today and post back with the findings. It might also be a worthwhile addition to Allen Browne’s article on his site although I don’t know how active he still is.

Chris.

 

[ChrisO]

Yep, that works nicely…

As expected, but needed to be proved, an instance of a main form sub-form combination also works. In this case the new instance of the Template form has a sub-form with a new instance of the parent form. Similarly, that parent form has a new instance of its sub-form.

So in the demo both new instances of form frmMain/sub-form are the same but operate independently. We can therefore filter/sort each sub-form differently and export that selection independently.

In the attachment (Access 2003), open module mdlGlobal and run Sub TestIt().

Edit
Oh, BTW, I forgot to mention...

If you get an error of "User-defined type not defined" on the following line: -
Set frmThisForm = New Form_frmTemplate
and you can't see Form_frmTemplate in Intellisense then save yourself a couple of non-productive hours Goggling for the reason.

It seems by experiment, in Access 2003 at least, that in order for the form name (in this case frmTemplate) to show up in Intellisense and be compiled it needs its 'Has Module' property set to True.

I don’t like calling things bugs but it means that we can't make a new instance of a 'light weight' form, one that has no class module.
You do not need code in the form's class module you only need the form's 'Has Module' property set to True.

Edit again…
It's easy to find things when you know what to look for: -
Form requires HasModule set to True.
http://support.microsoft.com/kb/210248


Chris.

 

[md57780]

I came across a little personal side project, and thought it was a good time to try and come up with an alternative to my inquiries posted here earlier. Here's what I have working, for those who might be interested.

Instead of passing a form name, I am passing an object variable that is a copy of the form I want to open. Each form would need a public InitForm procedure to handle the ID passed, be it filtering or whatever.

So far, it's worked well without the need to create a series of Select statements or subform controls.

In my calling procedure...

Private Sub cmdButton_Click()
Dim frmTemplate as New Form_MyFormToDuplicate
 
    FormMgr.OpenForm frmTemplate, listAccts.Column(0)
    Set frmTemplate = Nothing 
 
End Sub

In my form manager module...

Public Sub OpenForm(frmTemplate As Form, ID As String)
On Error GoTo ErrorTrap
Dim frm As Form, frmCaption As String
 
    frmCaption = frmTemplate.Caption & " " & ID
 
    For Each frm In Forms
        If frm.Caption = frmCaption Then Exit For
        Set frm = Nothing
    Next frm
 
    If frm Is Nothing Then
        Set frm = frmTemplate
        With frm
            .InitForm ID
            .Visible = True
            .Caption = frmCaption
            clnForms.Add Item:=frm, Key:=CStr(frm.hWnd)
        End With
    Else
        frm.SetFocus
        DoCmd.Restore
    End If
 
ExitSub:
    Set frm = Nothing
    Exit Sub
 
ErrorTrap:
    MsgBox Err.Description, vbCritical, "FormMgr: OpenForm2(" & ID & ")"
    Resume ExitSub
 
End Sub