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 SubAnd 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?