How to create a new form instance using the name o

Question

Code to create new form instance of a closed form using form name

I want to replace the long Select Case list with a variable.

Full code of module

---

In Access 2010 I have a VBA function that opens a new instance of a form when given a string containing the form's name. By adding a form variable "frm" to a collection:

mcolFormInstances.Add Item:=frm, Key:=CStr(frm.Hwnd)

The only way I can figure out to open "frm" is with a Select Case statement that I've manually entered.

Select Case strFormName
    Case "frmCustomer"
        Set frm = New Form_frmCustomer
    Case "frmProduct"
        Set frm = New Form_frmProduct        
    ... etc ... !
End Select

I want it to do it automatically, somewhat like this (although this doesn't work):

Set frm = New Eval("Form_" & strFormName)

Or through some code:

For Each obj In CurrentProject.AllForms 'or AllModules, neither work
    If obj.Name = strFormName Then
        Set FormObject = obj.AccessClassObject 'or something
    End If
Next

Set frm = New FormObject

I just want to avoid listing out every single form in my project and having to keep the list updated as new forms are added.

Answer 1

Here's an ugly hack I found:

DoCmd.SelectObject <acObjectType>, <YourObjectsName>, True
DoCmd.RunCommand acCmdNewObjectForm

The RunCommand step doesn't give you programmatic control of the object, you'll have to Dim a Form variable and Set using Forms.Item(). I usually close the form after DoCmd.RunCommand, then DoCmd.Rename with something useful (my users don't like Form1, Form2, etc.).

Hope that helps.

Answer 2

I think you are looking for something like this MS-Access 2010 function. (The GetForm sub is just for testing):

Function SelectForm(ByVal FormName As String, ByRef FormExists As Boolean) As Form
    For Each f In Application.Forms
      If f.Name = FormName Then
        Set SelectForm = f
        FormExists = True
        Exit Function
      End If
    Next
    FormExists = False
End Function

Sub GetForm(ByVal FormName As String)

  Dim f As New Form
  Dim FormExists As Boolean
  Set f = SelectForm(FormName, FormExists)
  If FormExists Then
    MsgBox ("Form Found: " & f.Caption) 
  Else
    MsgBox ("Form '" & FormName & "' not found.")
  End If

End Sub

Answer 3

I've also done some testing of my own and some reading online about this. As near as I can tell, it isn't possible to create a new form object and set it to an instance of an existing form using a string that represents the name of that form without using DoCmd.OpenForm.

In other words, unless someone else can prove me wrong, what you are trying to do cannot be done.