1

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.

Yotam Omer
  • 15,310
  • 11
  • 62
  • 65
Ryan Clarke
  • 197
  • 2
  • 11

3 Answers3

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.

HK1
  • 11,941
  • 14
  • 64
  • 99
  • I hate to, but I think I'm going to have to give the "it's impossible" answer the check mark. Nothing else is turning up. – Ryan Clarke Jul 20 '12 at 01:22
1

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
ron tornambe
  • 10,452
  • 7
  • 33
  • 60
  • Thanks so much for the code. I actually have something very similar in my module (entire module is now posted above). However, Application.Forms only contains open forms. I'm trying to create a new instance of a closed form. I tried to clarify this in some edits to my question. – Ryan Clarke Jun 06 '12 at 19:13
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.

Joshua Utter
  • 11
  • 1