CBlog(handziuk)

(I'm pretty sure I didn't come up with this but it's been in some of my stuff for a few years now. If I find where I got it from I'll post that link)
Typically when you open a form in Access

DoCmd.OpenForm "Name of Form"

It will open it then if when you call that code again (while still having the first form open) it will just pop focus back to the form as it was first opened. Th Open and Load events don't fire either so you can't "reinitialize" without closing it first.

To get around this you can create a New Form and pass around the Hwnd of that form. I use this to keep a form for creating dynamic datasheets (i.e. you pass in a recordset or recordsource and it figures out how to display it by setting all the appropriate control sources)

Public multiInstanceDic As Dictionary

'returns the window handle (long)
Public Function OpenNewDynamicDataSheetInstance(queryForRecordSource As String, Optional inputCaption As String) As Long
    
    If multiInstanceDic Is Nothing Then
        Set multiInstanceDic = New Dictionary
    End If
    
    Dim frm As Form
    Set frm = New Form_DynamicDatasheet
    frm.Caption = inputCaption
    frm.SetRecordSource queryForRecordSource
    multiInstanceDic.Add frm.Hwnd, frm   'required to keep form alive after function exits
    frm.SetFocus
    
    OpenNewDynamicDataSheetInstance = frm.Hwnd
End Function

Public Function GetDynamicDataSheetInstance(frmHandle As Long) As Form_DynamicDatasheet
    Set GetDynamicDataSheetInstance = multiInstanceDic(frmHandle)
End Function

Now whenever I need to open this form I can call this:

Dim createdWindowHandle As Long
createdWindowHandle = Windows.OpenNewDynamicDataSheetInstance("ViewForRecordsource", "My Caption Text")

And if I need to access things about that specific object like setting some property uniquely for this instance I can do it like this:

Dim dDataSht As Form_DynamicDatasheet
Set dDataSht = Windows.GetDynamicDataSheetInstance(createdWindowHandle)
dDataSht.SomeProperty = "set to this"

Then I clean up the reference to the form from this statis public dictionary in the Unload event of the form. I have this set up to only work with one type of form object. I you want multiple instances of multiple types of forms there are probably work arounds for that. I'm sure I'll need to do that some day in which case I'll update this.

Private Sub Form_Unload(Cancel As Integer)
    If Not MultiFormInstance.multiInstanceDic Is Nothing Then
        If MultiFormInstance.multiInstanceDic.Exists(Me.Hwnd) Then
            MultiFormInstance.multiInstanceDic.Remove Me.Hwnd
        End If
    End If
End Sub

And in my dashboard close event I can get rid of any stragglers (which there shouldn't be any but...who knows)

    If Not Windows.multiInstanceDic Is Nothing Then
        'kill all the dynamic datasheets
        Dim key As Variant
        For Each key In Windows.multiInstanceDic.Keys
            Set Windows.multiInstanceDic(key) = Nothing
        Next key
    End If

Bradley Handziuk's blog

How to have multiple instances of the same form open at once in Access

Add comment

Related posts

Add comment