Setting Recordsource when there are two or more forms open

[Bobp3114]

I am using the following code to set the recordsource of a form depending on which form is used to open it.
If CurrentProject.AllForms("frmSalesDashboard").IsLoaded = True Then
DoCmd.OpenForm "frmActivitiesEdit", acDesign
Forms!frmActivitiesEdit.RecordSource = qryActivitiesEdit
DoCmd.Close acForm, "frmActivitiesEdit", acSaveYes
'DoCmd.OpenForm "frmActivitiesEdit", acNormal, acSaveYes
DoCmd.OpenForm "frmActivitiesEdit", , , "ActionID = " & Me.txtActionID
ElseIf CurrentProject.AllForms("frmActivitiesOverview").IsLoaded = True Then
DoCmd.OpenForm "frmActivitiesEdit", acDesign
Forms!frmActivitiesEdit.RecordSource = qryActivitiesEdit
DoCmd.Close acForm, "frmActivitiesEdit", acSaveYes
'DoCmd.OpenForm "frmActivitiesEdit", acNormal, acSaveYes
DoCmd.OpenForm "frmActivitiesEdit", , , "ActionID = " & Me.txtActionID
End If

Form SalesDashBoard is always open so the code always goes to that section of the code not to the overview section when required
Question: How do I get around this????
Thanks in advance
Bob

 

[Gasman]

Use OpenArgs.
That is what it is there for.

Alternatively you could do it from the calling form?
Or change the order?

 

[arnelgp]

just test if frmActivitiesEdit is open:\

If CurrentProject.AllForms("frmActivitiesEdit").IsLoaded = False Then
    DoCmd.OpenForm "frmActivitiesEdit"
End If
Forms!frmActivitiesEdit.RecordSource = "Select * from qryActivitiesEdit Where ActionID = " & Me.txtActionID

 

[Ken Sheridan]

I would recommend using the OpenArgs mechanism as Gasman suggests. The following is code in an unbound form's module which builds the SQL statement for a query and passes it to a form as the OpenArgs argument of the OpenForm method:

    Dim strSQL As String
    Dim strWhere As String
   
    strSQL = "SELECT Employees.*, FirstName & "" "" & Lastname AS FullName, " & _
        "Projects.Project FROM Projects " & _
        "RIGHT JOIN (Employees LEFT JOIN ProjectEmployees " & _
        "ON Employees.EmployeeID=ProjectEmployees.EmployeeID) ON " & _
        "Projects.ProjectID=ProjectEmployees.ProjectID WHERE True"
   
    If Not IsNull(Me.cboDepartment) Then
        strWhere = strWhere & " And Department = """ & Me.cboDepartment & """"
    End If
   
    If Not IsNull(Me.cboCity) Then
        strWhere = strWhere & " And City = """ & Me.cboCity & """"
    End If
       
    If Not IsNull(Me.txtDateFrom) Then
        strWhere = strWhere & " And DateAppointed >= #" & _
            Format(Me.txtDateFrom, "yyyy-mm-dd") & "#"
    End If
 
    If Not IsNull(Me.txDateTo) Then
        strWhere = strWhere & " And DateAppointed < #" & _
            Format(Me.txDateTo, "yyyy-mm-dd") & "#+1"
    End If
   
    strSQL = strSQL & strWhere
   
    DoCmd.OpenForm "frmEmployees", OpenArgs:=strSQL

The following code is placed in the Open event procedure of the frmEmployees form:

    If Not IsNull(Me.OpenArgs) Then
        Me.RecordSource = Me.OpenArgs
    End If

 

[The_Doc_Man]

I'm siding with the others who suggest using the OpenArgs argument to pass in proper identifying information from the opener. When you have multiple forms open, "focus" (and therefore the identity of the currently "Current" form) might be a bit fluid. But from that opening form, your code might include something so simple as (borrowing from Ken's post)

DoCmd.OpenForm "frmEmployees", OpenArgs := Me.Name

And then when frmEmployees opens you can test directly to find the name of the opening form. Easy-peasey.

 

[Gasman]

What do people think about

Opening the form hidden
Setting whatever for that form
Revealing said form

All called from calling form?

 

[Ken Sheridan]

I'm siding with the others who suggest using the OpenArgs argument to pass in proper identifying information from the opener. When you have multiple forms open, "focus" (and therefore the identity of the currently "Current" form) might be a bit fluid. But from that opening form, your code might include something so simple as (borrowing from Ken's post)

DoCmd.OpenForm "frmEmployees", OpenArgs := Me.Name

And then when frmEmployees opens you can test directly to find the name of the opening form. Easy-peasey.

That's a good approach, especially if the second form's recordset is restricted by parameters referencing the calling form. Provided that the referenced controls in each of the possible calling forms have the same names, the second form's RecordSource can easily be built in its Open event procedure by concatenating the value of the OpenArgs property into the string expression.

I also like Gasman's idea of hiding the form while its RecordSource is being set up. Another possible approach might be to turn screen repainting off and then on again with the Echo method of the Application object.

 

[tvanstiphout]

Re OpenArgs, I highly recommend borrowing modStrings from the Northwind 2 Developer template, and using it to parse the several OpenArgs arguments that are passed as name/value pairs. I talk about it in this YouTube video, at 35:34.

 

[DaveMD]

@tvanstiphout The StringFormat and StringFormatSQL functions in your presentation look very useful for building dynamic strings. I do the same with using a Dictionary with key/value pairs for passing OpenArgs, but those additional helpers look great and I will plan to incorporate those. It got me thinking if there would be a way to implement f-strings like in Python where you can embed variable names within the literal string for dynamic resolution.

 

[tvanstiphout]

using a Dictionary with key/value pairs for passing OpenArgs

That is indeed what function modStrings.StringToDictionary is for.

 

[tvanstiphout]

f-strings like in Python where you can embed variable names within the literal string for dynamic resolution.

Too bad, so sad, but we do not have access to local variables from VBA (a comparatively VERY simple language). That's why we have to pass them into our function.
Python:
name = "Alice"
age = 30
print(f"My name is {name} and I am {age} years old.")

VBA:
Debug.Print StringFormat("My name is {0} and I am {1} years old.", name, age)

 

[RonPaii]

You can add much more power to OpenArgs by passing the address of a object as a string then converting that address to an object reference in the form or report. Using a user defined class and an object instance you can pass just about anything.

I use this in my sample database https://www.access-programmers.co.uk/forums/threads/another-input-box-replacement.334414/

 

[Ken Sheridan]

While we are on the subject I might as well throw in my six pennyworth. The following Module was developed years ago by Stuart McCall and myself when we were both sysops in the old CompuServe Access forum. Stuart built the original module to pass a comma delimited argument list. Later, in response to a request by a forum member, I extended it to allow named arguments to be passed:

'basArgs
'This module allows lists of values to be passed to a form or report by means of the
'OpenArgs mechanism.  Values can be passed individually as named arguments:

    'Dim args As String
    
    ' <add some named arguments>
    'AddArg args, "First", "Apples"
    'AddArg args, "Second", "Pears"
    'AddArg args, "Third", "Bananas"

'Or as an arguments list:

    'AddArgList args, "Oranges", "Peaches", "Grapefruit"

'The list is then passed to the form with:

    'DoCmd.OpenForm "frmMyForm", OpenArgs:=args

'In the form's or report's module the values can be extracted like so:

    'Dim args As String, i As Integer
    
    'args = Me.OpenArgs

    ' <get some named named arguments>
    'Debug.Print Arg(args, "First")
    'Debug.Print Arg(args, "Second")
    'Debug.Print Arg(args, "Third")
    ' <get some arguments by ordinal position>
    'Debug.Print Arg(args, 4)
    'Debug.Print Arg(args, 5)
    'Debug.Print Arg(args, 6)
    ' <list all arguments>
    'For i = 1 To ArgCount(args)
        'Debug.Print Argname(args, i), Arg(args, i)
    'Next i
    ' get count of arguments
    'Debug.Print ArgCount(args)

Private Const OFFSET As Long = 127
Private Const ASSIGNOP As String = "=="

Function Arg(buffer, idx) As Variant

    If IsNumeric(idx) Then
        i& = InStr(1, buffer, Chr(idx + OFFSET - 1))
        token$ = Chr(idx + OFFSET)
        i& = InStr(i&, buffer, ASSIGNOP) + 2
    Else
        i& = InStr(1, buffer, idx) + Len(idx) + 2
        token$ = Chr(Asc(Mid(buffer, InStr(1, buffer, idx) - 1, 1)) + 1)
    End If
    Arg = Mid(buffer, i&, InStr(i&, buffer, token$) - i&)
        
End Function

Function Argname(buffer, idx) As String

    i& = InStr(1, buffer, Chr(idx + OFFSET - 1))
    token$ = Chr(idx + OFFSET)
    Argname = Mid(buffer, i& + 1, InStr(i&, buffer, ASSIGNOP) - (i& + 1))
        
End Function

Function ArgCount(buffer) As Long

    ArgCount = Asc(Right(Chr(OFFSET) & buffer, 1)) - OFFSET
    
End Function

Sub AddArg(buffer, Argname, argval)

    If Len(buffer & "") = 0 Then buffer = Chr(OFFSET)
    If IsNumeric(Argname) Then Argname = ArgCount(buffer) + 1
    buffer = buffer & Argname & ASSIGNOP & argval & Chr(Asc(Right(buffer, 1)) + 1)
    
End Sub

Sub AddArgList(buffer, ParamArray Tokens())

    For i& = 0 To UBound(Tokens)
        AddArg buffer, i& + 1, Tokens(i&)
    Next
    
End Sub

 

[MarkK]

Borrowing from MVVM, I increasingly like to pass a "ViewModel" class to the form after it opens so the form has everything it needs as live, strongly-typed objects. So rather than re-hydrate objects, or resolve references from strings, I'll run a Property Set ViewModel(vm As IViewModel), and then inside the setter method, configure the form based on the ViewModel's payload.

If there's a delete button on a form, for instance, the Form handles the delete confirmation MsgBox(), but calls VIewModel.Delete() to do the actual work. This simplifies all the code because the form is strictly UI focussed, and the ViewModel's business logic never gets buried in it.

 

[Gasman]

Borrowing from MVVM, I increasingly like to pass a "ViewModel" class to the form after it opens so the form has everything it needs as live, strongly-typed objects. So rather than re-hydrate objects, or resolve references from strings, I'll run a Property Set ViewModel(vm As IViewModel), and then inside the setter method, configure the form based on the ViewModel's payload.

If there's a delete button on a form, for instance, the Form handles the delete confirmation MsgBox(), but calls VIewModel.Delete() to do the actual work. This simplifies all the code because the form is strictly UI focussed, and the ViewModel's business logic never gets buried in it.

@MarkK
An example would be nice for dummies like me?