Now that we've chased, killed, skinned and sold the rabbit on EBAY...
Here are the assorted subs..
[External Module]
Option Compare Database
Option Explicit
'Provide a global array to store the OpenArgs parameter names & values for
easy retrieval
Global aOpenParameters()
Sub openRptLoadListDetail(Optional strView As String, Optional
strWhereStatement As String, Optional strReportTitle As String, Optional
strLoadListInformation As String)
'Encapsulate opening the report so that OpenArgs string is built as needed
and passed in, this way, we don't have to remember what the parameter names
are as we code AND we centralize all code that opens the report.
Dim strOpenArgs As String
If Len(strReportTitle) > 0 Then strOpenArgs = strOpenArgs &
"mReportTitle=" & strReportTitle
If Len(strReportTitle) > 0 And Len(strLoadListInformation) > 0 Then
strOpenArgs = strOpenArgs & ";"
If Len(strLoadListInformation) > 0 Then strOpenArgs = strOpenArgs &
"mLoadListInformation=" & strLoadListInformation
DoCmd.OpenReport "rptLoadList_Detail", strView, , strWhereStatement, ,
strOpenArgs
End Sub
Sub loadParametersArray(strArgs As Variant)
'Parse out the OpenArgs string for a Form/Report
'Break the string using ";" as the delimiter
Dim aOpenArgs
'Break the substrings using "=" as the delimiter
Dim aOpenArgsDetail
Dim i As Integer
Dim intNumberOfParameters As Integer
'Temp variable to work w/OpenArgs, charactersInString was giving a ByVal
error which couldn't be worked around, charactersInString() is a function that
counts the number of occurances of a specific character in a string, of
course I could use UBound to calculate the number of parameters but didn't
think about that at the time, I'll probably switch over to that approach
Dim strArgsWorkVar As String
If IsNull(strArgs) = False Then
'Break apart the string into the individual parameter Name/Value pairs
aOpenArgs = Split(strArgs, ";")
strArgsWorkVar = strArgs
intNumberOfParameters = charactersInString(";", strArgsWorkVar) + 1
If intNumberOfParameters > 0 Then
ReDim aOpenParameters(intNumberOfParameters, 2)
'Break apart the parameter Name/Value pairs and load up the global
'array
For i = 0 To intNumberOfParameters - 1
aOpenArgsDetail = Split(aOpenArgs(i), "=")
aOpenParameters(i, 0) = aOpenArgsDetail(0)
aOpenParameters(i, 1) = aOpenArgsDetail(1)
Next i
End If
End If
End Sub
Function getParameterVariable(strParameter As String) As Variant
'Loop through the global array to look up the value for the named
parameter,
Dim i As Integer
getParameterVariable = Null
Select Case UBound(aOpenParameters, 1)
Case -1
Case Is > 0
While i <= UBound(aOpenParameters, 1) - 1 And
IsNull(getParameterVariable)
If aOpenParameters(i, 0) = strParameter Then
getParameterVariable = (aOpenParameters(i, 1))
i = i + 1
Wend
Case Else
End Select
End Function
In the report...
Option Compare Database
Option Explicit
Dim mReportTitle As String
Dim mLoadListInformation As String
Private Sub Report_Open(Cancel As Integer)
If IsNull(Me.OpenArgs) = False Then
'Load up the array
Call loadParametersArray(Me.OpenArgs)
'Load up the report specific module-level variables, this allows
loadParametersArray and getParameterVariable to be reused for *ANY* form or
report
'Explicity cast the variable since it non-strings will be passed in
as a string
mReportTitle = CStr(getParameterVariable("mReportTitle"))
mLoadListInformation =
CStr(getParameterVariable("mLoadListInformation"))
'Set the report title and subheader information
If mReportTitle <> "" Then Me.lblReportTitle.Caption = mReportTitle
'The Load List Information control's control source is a calculated
value of two values in the recordsource. My primary need for starting all of
this was to be able to run the report using a different WHERE statement that
returns a broader range of values - Example - If you're working with a report
that merely provides the detail of an Order, you might use "OrderId = 5" to
view all the items on order number 5, but if you use "ClientId = 300" you'd
return all of the items ordered by client number 300. You'd certainly want to
state that using "<All Orders>" as opposed to "Order #5".
If mLoadListInformation <> "" Then
Me!exprLoadListInformation.ControlSource = "=" & Chr(34) &
mLoadListInformation & Chr(34)
End If
End Sub
dch3 said:
I asked this question differently in another post and got an answer that
didn't answer the question...
So is it possible to create a function that takes a parameter which
represents a global variable and in the function set the variable? Basically
I want a function whereby I can pass in a variable NAME and have the function
set the variable.
NOTE: YES, YES, YES! I am fully aware that I could explicitly set the
variable (mySystemName = "Hello World"), *BUT* I want a function that is
generic and can be used regardless of the variable name that needs to be set.
The function will be used in conjunction with the .OpenArgs statement of the
.OpenForm and .OpenReport methods of DoCmd.
As in...
call setGlobalVariable("mySystemName", "Hello World") will set the global
variable mySystemName to "Hello World"
Global mySystemName as Variant
Global myNetworkName as Variant
sub setGlobalVariable (strVariableName as String, varValue as Variant)
[statement which recognizes strVariableName as a DIM'd variable] = varValue
end sub
