[AccessD] Open Multiple Instances of a Form and passing a parameter

>
> jwcolby:
> Ick.
>

Ick indeed. Using the Caption or a Tag to pass parameters is ugly. Here is
sample code with correct parameter passing.

Enhancements on Allen's approach:
* One module per form. Every method in the module is prefixed with the form
name. This keeps managing this kind of code simple
* You send the parameters when you call the 'nameNew' routine
* The form gets one chance to read the parameters using the 'nameOpenArgs'
function
* Since multiple instances of a form all share the same query, save the
form with a blank RecordSource, and set it with SQL when the form opens

To create a new instance of form 'Blah', call 'BlahNew' with your OpenArgs
parameter:

--------------------------------------------------------------------------------
Option Compare Database
Option Explicit

' Module MBlah

Private mrBlahs As New Collection
Private mvBlahParameter As Variant

Public Sub BlahDestroy(lHwnd As String)
    On Error Resume Next
    mrBlahs.Remove CStr(lHwnd)
End Sub

Public Sub BlahNew(vBlahParameter As Variant)
    Dim rForm As Access.Form
    mvBlahParameter = vBlahParameter
    Set rForm = New Form_frmBlah
    rForm.Visible = True
    mrBlahs.Add rForm, CStr(rForm.hwnd)
    mvBlahParameter = Empty
End Sub

Public Function BlahOpenArgs() As Variant()
    BlahOpenArgs = Array()
    If Not IsEmpty(mvBlahParameter) Then
        BlahOpenArgs = Array(mvBlahParameter)
    End If
End Function

Public Function BlahSql(vBlahParameter As Variant)
    BlahSql = "SELECT [Blah] FROM [Blah] WHERE ([Blah].[Blah] = '" &
vBlahParameter & "');"
End Function
--------------------------------------------------------------------------------

And here is the Form code. Note that the Recordsource is left blank for
multi-instance forms, and is filled in using code:

--------------------------------------------------------------------------------
Option Compare Database
Option Explicit

' Form class Form_frmBlah

Private mvMyParameter As Variant

Private Sub cmdClose_Click()
    BlahDestroy Me.hwnd
End Sub

Private Sub Form_Close()
    BlahDestroy Me.hwnd
End Sub

Private Sub Form_Open(Cancel As Integer)
    Dim vArgs() As Variant
    vArgs = BlahOpenArgs()
    mvMyParameter = vArgs(0)
End Sub

Private Sub Form_Load()
    Me.RecordSource = BlahSql(mvMyParameter)
End Sub
--------------------------------------------------------------------------------