Bradley Handziuk's blog

How effectively pass multiple values in an OpenArgs parameter

I always need to write up a little procedure to handle passing in values in the OpenArgs paramater of DoCmd.OpenReport or DoCmd.OpenForm. Well no more! I am liking this style. I's kind of JSONy. Given a string formatted like
<name of="" control="">|<value for="" that="" control="">||<another control="" name="">|<value for="" that="" control="">...</value></another>
it will put the values in the right place.
Function PopulateWithOpenArgs(ctrls As Controls, OArgs As String) As Dictionary
    Dim controlValues As New Dictionary
    Dim controlsValuesArr() As String, i As Integer
    Dim ctrlValPair() As String
    controlsValuesArr = Split(OArgs, "||")
    On Error Resume Next
    For i = LBound(controlsValuesArr) To UBound(controlsValuesArr)
        ctrlValPair = Split(controlsValuesArr(i), "|")
        If UBound(ctrlValPair) >= 1 Then
            controlValues.Add ctrlValPair(0), ctrlValPair(1)
        End If
    Next i
    Dim ctrlName
    For Each ctrlName In controlValues.Keys
        ctrls(ctrlName) = controlValues(ctrlName)
    Next ctrlName
    Set PopulateWithOpenArgs = controlValues
End Function
So, for example, if I have a form, Frm_ReportOpenOptions, which is responsible for gathering some data from a user before a report is generated and on that form I have two textboxestxtSubmitterNameInput and txtDateSubmittedInput, after they enter their values, I can create a pipe and double-pipe delimited string of those value and the destination control names on the report or form.
Dim rptOpenArgs As String
Dim whereClause As String
rptOpenArgs = "txtDateSubmitted|" & txtDateSubmittedInput & "||txtSubmitterName|" & txtSubmitterNameInput
whereClause = set to whatever value...
DoCmd.OpenReport "Rpt_MonthlyPayments", acViewPreview, , whereClause, OpenArgs:=rptOpenArgs
Then in the Load event I just need to call
Private Sub Report_Load()
    PopulateWithOpenArgs Me.Controls, Me.OpenArgs
End Sub
And the controls are automagically populated. This works well for reports that have a field that needs to be set once, like in the header section. Otherwise, more complicated controls in detail sections might need to get set in a Format event. Reports also cannot have their values changed once they are formatted. So, while a form can be opened via DoCmd.OpenForm then have properties set via Form("Name of form").Property a report canot do that.

Got any better ways of passing in an arbitrary number of OpenArgs parameters?

Comments (1) -

  • Emma

    6/5/2014 4:30:53 PM | Reply

    This would be perfect for GitHub. After your statement "Got any better ways of passing in an arbitrary number of OpenArgs parameters?" -- you should include a link to a GitHub repo.