Create new form instance at the module level

[DeveloperSteve]

I'm trying to create a new instance of an existing form, set it's record source, and display the form for the user. I can create the instance and display it, but as soon as the code stops executing the form instance goes out of scope and disappears. I've read that I need to... declare the variable that represents the new instance of a form class at the module level, which I mostly understand, but how do I actually do this?

I created a module, and a public sub to create the new form instance, then I call the sub from a procedure connected to a form (where the selects options). However, again my new form instance is gone when the code stops.

 

[boblarson]

Just after you create it, you need to add that form to a custom COLLECTION so you can refer to it later and use it.

 

[DeveloperSteve]

Thanks. In my public sub I create a collection and add my new form instance to it.

Public Function Create015Datasheet() As Form
'Create a new form instance
Dim colForms As New Collection
Dim frmNew015Datasheet As Form
Set frmNew015Datasheet = New Form_frmCurrent015Datasheet

colForms.Add frmNew015Datasheet

End Function

How do I refer to the form in my procedure?
Like this?

colForms.frmNew015Datasheet.Recordset = rs

 

[boblarson]

The collection object has to be declared in a standard module in the General Declarations Section:

Public colForms As Collection

And then you can do the

colForms.Add frmNew015Datasheet, keyHere

where keyHere is an identifier so you can use it later. It is a string expression. So you can name it what you want.

And remember unless you are closing the database, when you are completely done with your form, delete it from the collection. But do know that if you delete any of them from the collection then the index will change on those in the collection unless you delete from the end and move backwards.

(if you add the key identifier then you can use)

Function MyProcedure(intForm As Integer)
   Dim frm As Form
 
  Set frm = colForms(0)  ' the (0) to refer to the first one in the collection or you can use the key
 
 Set frm = colForms("MyKeyHere")
  ' then you can use the form using the frm variable.
End Function

 

[DeveloperSteve]

Worked perfectly.