Like I said this involves a few steps and you will probably need to read this
a few times before it makes sense but it works real well and worth the
initial effort down the road. I recommend you print and study it. It's really
not as bad as it seems once you read it a couple of times. I did a lot of
cutting and pasteing but I'm pretty sure this is all and correct, If you run
into any glitches, just repost.
1st, create a function (I will call this fn_INV_Lookups) with the parameters
you need. The one I give in the example uses 7 parameters.
It is important to have default values for all the parameters. Don't worry
about it for now, I will tell you some things about making the parameter
defaults later on.
Next, use this as your recordsource during developement.
SELECT * FROM fn_INV_Lookups(DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT)
Having the word Default in the function allows you to design the report or
form without getting the little green marks in the text fields.
You are going to collect all the parameter values from a command button that
opens the form or report and pass them to an array vaiable, so first lets do
that. We will get to passing the parameter values later.
Create a module (I called mine modGlobals).
In it define a global variable , this is where you will pass the parameter
values to
Public astrParams(10) As String
This is an array type variable that will hold 10 string values numbered 0
through 9.
Then create a procedure to parse these parameters (the ones we will pass from
the Report/Form Open procedure, we will get to that later)
Public Function GetParams(numParams As Integer, astrParams() As String) As
String
' this function pulls a number of strings from astrParams and compiles them
into a Parameter list
' suitable for passing to SQL Server in a sp or function call
Dim strParams As String
Dim i As Integer
strParams = ""
For i = 0 To (numParams - 1)
strParams = strParams & astrParams(i) & ","
Next
' strip off last comma
strParams = Left(strParams, Len(strParams) - 1)
GetParams = strParams
End Function
Ok so now we need to populat the parameter values into the asrtParams()
variable. This will go in the On Click event of the command button you use to
open the form or report. It is rather long since I use 7 parameters but it
will give you some ideas on how this can be used. Notice how we reference
the asrtParams by place holder as we populate it. You need to know the order
of parameters the function is expecting them in . One way to know for sure is
to simply open the function from the query list. You will be prompted to
input the parameters in the order in which you created them and therefore the
order it is expecting you to pass them in.
On Error GoTo Err_btnReport_Click
Dim stDocName As String
Dim strFilter As String
If chkSite Then
astrParams(0) = "'" & cboSite.Value & "'"
Else
astrParams(0) = "'%'"
End If
If chkShop Then
astrParams(1) = "'" & cboShop.Value & "'"
Else
astrParams(1) = "'%'"
End If
If chkCat Then
astrParams(2) = "'" & cboCat.Value & "'"
Else
astrParams(2) = "'%'"
End If
If chkSOS Then
If (cboSOS.Value <> "SP") And (cboSOS.Value <> "BS") Then
astrParams(3) = "'" & cboSOS.Value & "'"
astrParams(4) = "0"
astrParams(5) = "0"
Else
If cboSOS.Value = "SP" Then
astrParams(3) = "'%'"
astrParams(4) = "1"
astrParams(5) = "null"
Else
astrParams(3) = "'%'"
astrParams(4) = "null"
astrParams(5) = "1"
End If
End If
Else
astrParams(3) = "'%'"
astrParams(4) = "null"
astrParams(5) = "null"
End If
If frmReport.Value = 5 Then
astrParams(6) = "'P%'"
Else
astrParams(6) = "'%'"
End If
Select Case frmReport.Value
Case 1
stDocName = "rptINVListing"
Case 2
stDocName = "rptINVReconciliationSheets"
Case 3
stDocName = "rptBinLabels"
Case 4
stDocName = "rptYellowTags"
Case 5
stDocName = "rptBinStatusCards"
End Select
DoCmd.OpenReport stDocName, acPreview
End Sub
Now for the last task. In the form/reprot On Open event we need to create a
way of getting the parameter values from the asrtParams() variable. Here is
haow we do that.
Create a On_Open event procedure:
Private Sub Report_Open(Cancel As Integer)
Dim strFilter As String
strFilter = GetParams(7, astrParams)
Me.RecordSource = "SELECT * FROM fn_INV_Lookups(" & strFilter & ") fn"
End Sub
Ok if you are still with me I will give you a break down of the sequence of
events.
So when the user clicks the report button...
1) The form with the command button(FormA) calling the Form/Report looks at
what options the user has selected on the form and fills the parameter array
appropriately.
2) FormA calls the report (no filter needed)
3) The form/report (in On_Open event) processes the global parameter array
and creates its own parameter list.
4) The form/report changes its record source property to reflect user
criteria.
5) The form/report opens
More thoughts. Once you are finished designing the report , leave the
recordsource empty, as you can see it will have a recordsource once it opens.
I found that it will first grab all the default records then refilter in the
on open event.
As for function defaults, that can be a little tricky. If you are dealing
with a string type, you need to put Like @Param1 in the criteria block, not
=@Param1 like with an integer. Then go to the function properties and put in
'%' which is a wild character for All.
There are other issues with defaults for Bit types and if you get that far
just repost here and I'll help you with that.
Hope this helps.
Yes I would like to see those codes. 1. I do have the need to be able to do
this, and 2. Im trying to learn VBA.
Thank you for all your help
I have a method for opening forms which require multiple criteria. It's a
little involved and requires some vba but well worth doing if you have the
[quoted text clipped - 11 lines]
Thanks in advance, Im new to ADP projects.
