Requery multiple instances of form independently in VBA (Access)

Question

I am using Allen Browne's brilliant code (http://allenbrowne.com/ser-35.html) to open several instances of a form on click event. I understand that new instances are read-only and I managed to record the data the user inputs, to keep a log, thanks to this post (Access 2007 / VBA - Multiple Instances of Form, Update controls on specific instance from Module).

I have several combo boxes on each form and would like to update/requery what is shown in one depending on user selection on other (for each form, not among forms). For ex, user choose component A on cmb1, cmb2 only shows possible failures of component A.

{me.Requery} will only and always requery the form in the Forms class (the one opened from the database window/nav pane, if any, not the instances on clnClient collection below). However, I can pass information with "Me" and retrieve values for the logging.

Using a slightly modified OpenAClient version from Allen Browne's with input from MajP (in this great thread https://www.tek-tips.com/viewthread.cfm?qid=1753790)

Public clnClient As New Collection

Public Function OpenAClient(FormName As String, Optional inputCaption As String = "") As Form_f_myForm
    'Purpose:   Open an independent instance of form f_myForm
    On Error GoTo Err_OpenAClient

    Dim frm As Form

    'Open a new instance, show it, and set a caption.
    Set frm = New Form_f_myForm
    frm.Visible = True
    frm.Caption = inputCaption
    frm.Tag = FormName

    'Append it to our collection.
    clnClient.Add Item:=frm, Key:=FormName

    Set OpenAClient = frm
    Set frm = Nothing
    Exit Function

Err_OpenAClient:
    If Err.Number = 457 Then
        MsgBox "A Form with this name already exists."
    Else
        MsgBox Err.Number & " " & Err.Description
    End If
End Function

I have tried

Public ActiveForm As Form_f_myform
Set activeForm = OpenAClient("ExampleForm","Example caption")

or a simple

Set activeForm = clnClient.Item(2) 'just to test the second instance

and then use ActiveForm instead of "Me" and manipulate it that way, but it does not seem to work. Any ideas/help would be appreciated. Or ideas to tackle this in a different way. Thanks!

A bit of background. I am very new to VBA and Access, working on a database (Access Office365) for failure reporting. All failures have been assessed beforehand delivery to the user. The user will identify the specific component that is failing and fill the form accordingly (date, type of failure etc..). They will not change the table containing the pre-assessment. This is all fine and working. The issue is when adding the ability for the user to report multiple failures.

Thank you!! Very helpful indeed, much more clear now with your explanation. — Jorge Martires

kismert kismert · Accepted Answer · 2019-01-11T22:23:46

First, let's clear up some misconceptions:

I understand that new instances are read-only...

They can indeed be read-write. They just can't be attached to the same query. The reason for this is query expressions like Forms!f_myform!cmb1 don't work when there is more than one instance of f_myform open. You must construct custom SQL for each instance's record source to get true independent operation.

{Me.Requery} will only and always requery the form in the Forms class...

When used in a form's code, Me.Requery will indeed requery only that instance of the form. The problem comes when all instances of the form share the same query. The reason and fix are above.

As far as coding strategy, get rid of the global Public ActiveForm As Form_f_myform. That's only a recipe for problems.

The form instance itself carries all you need to get your code to work. Try calling functions with the form reference itself (like you did earlier). For example:

    Public Sub ShowComponentFailures(thisForm As Form_f_myform)
        Dim varCompID As Variant
        Dim strSQL As String
        ' get Component ID from given instance's cmb1
        varCompID = thisForm.cmb1.Value
        ' create SQL to retrieve possible failures for CompID 
        strSQL = GetComponentFailuresSQL(varCompID)
        ' set cmb2's record source to SQL
        thisForm.cmb2.RowSource = strSQL
    End Sub

Requery multiple instances of form independently in VBA (Access)

1 Answers