Parameters from a form

[Guest]

Is it possible to enter Parameter(or a retrieve specification) from a form in
an ADP select query. I used to do this all the time in Access 97, but i do
not seem to able to do this in an Access ADP. I just use to point to the Open
form, and the field that contains the data, sometimes the field on the form
could be unbound.

Thanks in advance, Im new to ADP projects.

 

[Malcolm Cook]

Sure,

Here is an example of one way. It assume that you have set up the example

Open the form 'Category' and view its properties.
On the data tab, note that the 'record source' is simply "Categories", the name of the table.
Change it to a parameterized select query, for isntance:
SELECT Categories.*, CategoryName AS cn FROM Categories WHERE (CategoryName LIKE ?)
Now, change the forms empty 'Input Parameters' to read:
cn char = [CategoryName LIKE what?]
Note that the parameter name, cn, is the name of the alias in the select query.
Now view the form, and you will be prompted with 'CategoryName LIKE what'.
If instead you make your 'Input Parameters' to read:
cn char = [myform]![myfield]
when you open the form (or refresh it) your parameterized query will get its value from myfield

Note that if your form is a subform, and myfield is on the parent form, then you don't even need to set the input parameters, IF YOU
RENAME MYFIELD to have the same name as the parameter, in this case 'cn'. This is one of the magic tircks that ADPs will do for
you.

Cheers,

 

[Guest]

sorry for not getting back to you sooner, we were experiencing some internet
issues here.
Anyway I was able to get it semi work with your suggestion, but not the way
I was hoping. When I ran it a pop up requesting the parameter to be entered.
What I wanted was to be able to input the parameter on the form and the query
would then pick the parameter from the form. I would also like to be abe to
do the same for update and delete queries.

Thank you

Sure,

Here is an example of one way. It assume that you have set up the example

Open the form 'Category' and view its properties.
On the data tab, note that the 'record source' is simply "Categories", the name of the table.
Change it to a parameterized select query, for isntance:
SELECT Categories.*, CategoryName AS cn FROM Categories WHERE (CategoryName LIKE ?)
Now, change the forms empty 'Input Parameters' to read:
cn char = [CategoryName LIKE what?]
Note that the parameter name, cn, is the name of the alias in the select query.
Now view the form, and you will be prompted with 'CategoryName LIKE what'.
If instead you make your 'Input Parameters' to read:
cn char = [myform]![myfield]
when you open the form (or refresh it) your parameterized query will get its value from myfield

Note that if your form is a subform, and myfield is on the parent form, then you don't even need to set the input parameters, IF YOU
RENAME MYFIELD to have the same name as the parameter, in this case 'cn'. This is one of the magic tircks that ADPs will do for
you.

Cheers,


--
Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA

Is it possible to enter Parameter(or a retrieve specification) from a form in
an ADP select query. I used to do this all the time in Access 97, but i do
not seem to able to do this in an Access ADP. I just use to point to the Open
form, and the field that contains the data, sometimes the field on the form
could be unbound.

Thanks in advance, Im new to ADP projects.

 

[Malcolm Cook]

Bob!

You will get that "pop up requesting the parameter to be entered" if you either
(1) don't set the form's Input Parameter property, or
(2) do set it, but incorrectly formed.

Did you try setting it?

To what?

If you like, send along your _exact_ SQL for the Record Source property and your _exact_ value for the the Input Parameter property
and I'll look at it.

Cheers,

Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA

sorry for not getting back to you sooner, we were experiencing some internet
issues here.
Anyway I was able to get it semi work with your suggestion, but not the way
I was hoping. When I ran it a pop up requesting the parameter to be entered.
What I wanted was to be able to input the parameter on the form and the query
would then pick the parameter from the form. I would also like to be abe to
do the same for update and delete queries.

Thank you

Sure,

Here is an example of one way. It assume that you have set up the example

Open the form 'Category' and view its properties.
On the data tab, note that the 'record source' is simply "Categories", the name of the table.
Change it to a parameterized select query, for isntance:
SELECT Categories.*, CategoryName AS cn FROM Categories WHERE (CategoryName LIKE ?)
Now, change the forms empty 'Input Parameters' to read:
cn char = [CategoryName LIKE what?]
Note that the parameter name, cn, is the name of the alias in the select query.
Now view the form, and you will be prompted with 'CategoryName LIKE what'.
If instead you make your 'Input Parameters' to read:
cn char = [myform]![myfield]
when you open the form (or refresh it) your parameterized query will get its value from myfield

Note that if your form is a subform, and myfield is on the parent form, then you don't even need to set the input parameters, IF
YOU
RENAME MYFIELD to have the same name as the parameter, in this case 'cn'. This is one of the magic tircks that ADPs will do for
you.

Cheers,


--
Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA

Is it possible to enter Parameter(or a retrieve specification) from a form in
an ADP select query. I used to do this all the time in Access 97, but i do
not seem to able to do this in an Access ADP. I just use to point to the Open
form, and the field that contains the data, sometimes the field on the form
could be unbound.

Thanks in advance, Im new to ADP projects.

 

[Guest]

I appreciate all your help. Here is what I have. Since im only testing this
right now, I havent assigned permanent names for the form the form is called
"Form1" the parameter field is called "Test".

the Query in the record source reads like:
SELECT CustomerCode AS Cust, Source, Comments FROM dbo.tbl_HoldOrderHeader
WHERE (CustomerCode LIKE ?)

The Input Parmeters on the form is set up as:
Cust char =[Form]![Form1]![Test]

This works, but it forces a pop up for the parameter

Sorry for the delay in getting back to you. Unfortunately my company does
not believe on finishing one project before going on to the next project, In
fact they believe that we should be able to handle 20 projects at one time
and do it efficiently.

Bob!

You will get that "pop up requesting the parameter to be entered" if you either
(1) don't set the form's Input Parameter property, or
(2) do set it, but incorrectly formed.

Did you try setting it?

To what?

If you like, send along your _exact_ SQL for the Record Source property and your _exact_ value for the the Input Parameter property
and I'll look at it.

Cheers,

Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA

sorry for not getting back to you sooner, we were experiencing some internet
issues here.
Anyway I was able to get it semi work with your suggestion, but not the way
I was hoping. When I ran it a pop up requesting the parameter to be entered.
What I wanted was to be able to input the parameter on the form and the query
would then pick the parameter from the form. I would also like to be abe to
do the same for update and delete queries.

Thank you

Sure,

Here is an example of one way. It assume that you have set up the example

Open the form 'Category' and view its properties.
On the data tab, note that the 'record source' is simply "Categories", the name of the table.
Change it to a parameterized select query, for isntance:
SELECT Categories.*, CategoryName AS cn FROM Categories WHERE (CategoryName LIKE ?)
Now, change the forms empty 'Input Parameters' to read:
cn char = [CategoryName LIKE what?]
Note that the parameter name, cn, is the name of the alias in the select query.
Now view the form, and you will be prompted with 'CategoryName LIKE what'.
If instead you make your 'Input Parameters' to read:
cn char = [myform]![myfield]
when you open the form (or refresh it) your parameterized query will get its value from myfield

Note that if your form is a subform, and myfield is on the parent form, then you don't even need to set the input parameters, IF
YOU
RENAME MYFIELD to have the same name as the parameter, in this case 'cn'. This is one of the magic tircks that ADPs will do for
you.

Cheers,


--
Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA


Is it possible to enter Parameter(or a retrieve specification) from a form in
an ADP select query. I used to do this all the time in Access 97, but i do
not seem to able to do this in an Access ADP. I just use to point to the Open
form, and the field that contains the data, sometimes the field on the form
could be unbound.

Thanks in advance, Im new to ADP projects.

 

[Guest]

I do have one other question in refrence to this matter. Could this also be
done via VBA. (Something Im trying to learn in my spare time)

Bob!

You will get that "pop up requesting the parameter to be entered" if you either
(1) don't set the form's Input Parameter property, or
(2) do set it, but incorrectly formed.

Did you try setting it?

To what?

If you like, send along your _exact_ SQL for the Record Source property and your _exact_ value for the the Input Parameter property
and I'll look at it.

Cheers,

Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA

sorry for not getting back to you sooner, we were experiencing some internet
issues here.
Anyway I was able to get it semi work with your suggestion, but not the way
I was hoping. When I ran it a pop up requesting the parameter to be entered.
What I wanted was to be able to input the parameter on the form and the query
would then pick the parameter from the form. I would also like to be abe to
do the same for update and delete queries.

Thank you

Sure,

Here is an example of one way. It assume that you have set up the example

Open the form 'Category' and view its properties.
On the data tab, note that the 'record source' is simply "Categories", the name of the table.
Change it to a parameterized select query, for isntance:
SELECT Categories.*, CategoryName AS cn FROM Categories WHERE (CategoryName LIKE ?)
Now, change the forms empty 'Input Parameters' to read:
cn char = [CategoryName LIKE what?]
Note that the parameter name, cn, is the name of the alias in the select query.
Now view the form, and you will be prompted with 'CategoryName LIKE what'.
If instead you make your 'Input Parameters' to read:
cn char = [myform]![myfield]
when you open the form (or refresh it) your parameterized query will get its value from myfield

Note that if your form is a subform, and myfield is on the parent form, then you don't even need to set the input parameters, IF
YOU
RENAME MYFIELD to have the same name as the parameter, in this case 'cn'. This is one of the magic tircks that ADPs will do for
you.

Cheers,


--
Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA


Is it possible to enter Parameter(or a retrieve specification) from a form in
an ADP select query. I used to do this all the time in Access 97, but i do
not seem to able to do this in an Access ADP. I just use to point to the Open
form, and the field that contains the data, sometimes the field on the form
could be unbound.

Thanks in advance, Im new to ADP projects.

 

[Malcolm Cook]

Your mistake is you need to use 'forms' not 'form'.

Try setting your Input Parameters instead to: "Cust char =[forms]![Form1]![Test]"

It works for me.

I appreciate all your help. Here is what I have. Since im only testing this
right now, I havent assigned permanent names for the form the form is called
"Form1" the parameter field is called "Test".

the Query in the record source reads like:
SELECT CustomerCode AS Cust, Source, Comments FROM dbo.tbl_HoldOrderHeader
WHERE (CustomerCode LIKE ?)

The Input Parmeters on the form is set up as:
Cust char =[Form]![Form1]![Test]

This works, but it forces a pop up for the parameter

Sorry for the delay in getting back to you. Unfortunately my company does
not believe on finishing one project before going on to the next project, In
fact they believe that we should be able to handle 20 projects at one time
and do it efficiently.

Bob!

You will get that "pop up requesting the parameter to be entered" if you either
(1) don't set the form's Input Parameter property, or
(2) do set it, but incorrectly formed.

Did you try setting it?

To what?

If you like, send along your _exact_ SQL for the Record Source property and your _exact_ value for the the Input Parameter
property
and I'll look at it.

Cheers,

Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA

sorry for not getting back to you sooner, we were experiencing some internet
issues here.
Anyway I was able to get it semi work with your suggestion, but not the way
I was hoping. When I ran it a pop up requesting the parameter to be entered.
What I wanted was to be able to input the parameter on the form and the query
would then pick the parameter from the form. I would also like to be abe to
do the same for update and delete queries.

Thank you

:

Sure,

Here is an example of one way. It assume that you have set up the example

Open the form 'Category' and view its properties.
On the data tab, note that the 'record source' is simply "Categories", the name of the table.
Change it to a parameterized select query, for isntance:
SELECT Categories.*, CategoryName AS cn FROM Categories WHERE (CategoryName LIKE ?)
Now, change the forms empty 'Input Parameters' to read:
cn char = [CategoryName LIKE what?]
Note that the parameter name, cn, is the name of the alias in the select query.
Now view the form, and you will be prompted with 'CategoryName LIKE what'.
If instead you make your 'Input Parameters' to read:
cn char = [myform]![myfield]
when you open the form (or refresh it) your parameterized query will get its value from myfield

Note that if your form is a subform, and myfield is on the parent form, then you don't even need to set the input parameters,
IF
YOU
RENAME MYFIELD to have the same name as the parameter, in this case 'cn'. This is one of the magic tircks that ADPs will do
for
you.

Cheers,


--
Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA


Is it possible to enter Parameter(or a retrieve specification) from a form in
an ADP select query. I used to do this all the time in Access 97, but i do
not seem to able to do this in an Access ADP. I just use to point to the Open
form, and the field that contains the data, sometimes the field on the form
could be unbound.

Thanks in advance, Im new to ADP projects.

 

[AkAlan via AccessMonster.com]

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
need. Post back and I'll copy the code.

Your mistake is you need to use 'forms' not 'form'.

Try setting your Input Parameters instead to: "Cust char =[forms]![Form1]![Test]"

It works for me.

I appreciate all your help. Here is what I have. Since im only testing this
right now, I havent assigned permanent names for the form the form is called

[quoted text clipped - 75 lines]

 

[Guest]

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
need. Post back and I'll copy the code.

Your mistake is you need to use 'forms' not 'form'.

Try setting your Input Parameters instead to: "Cust char =[forms]![Form1]![Test]"

It works for me.

I appreciate all your help. Here is what I have. Since im only testing this
right now, I havent assigned permanent names for the form the form is called

[quoted text clipped - 75 lines]

Thanks in advance, Im new to ADP projects.

 

[Guest]

Thank you, so simple, yet enought to create a massive headache

Your mistake is you need to use 'forms' not 'form'.

Try setting your Input Parameters instead to: "Cust char =[forms]![Form1]![Test]"

It works for me.

I appreciate all your help. Here is what I have. Since im only testing this
right now, I havent assigned permanent names for the form the form is called
"Form1" the parameter field is called "Test".

the Query in the record source reads like:
SELECT CustomerCode AS Cust, Source, Comments FROM dbo.tbl_HoldOrderHeader
WHERE (CustomerCode LIKE ?)

The Input Parmeters on the form is set up as:
Cust char =[Form]![Form1]![Test]

This works, but it forces a pop up for the parameter

Sorry for the delay in getting back to you. Unfortunately my company does
not believe on finishing one project before going on to the next project, In
fact they believe that we should be able to handle 20 projects at one time
and do it efficiently.

Bob!

You will get that "pop up requesting the parameter to be entered" if you either
(1) don't set the form's Input Parameter property, or
(2) do set it, but incorrectly formed.

Did you try setting it?

To what?

If you like, send along your _exact_ SQL for the Record Source property and your _exact_ value for the the Input Parameter
property
and I'll look at it.

Cheers,

Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA


sorry for not getting back to you sooner, we were experiencing some internet
issues here.
Anyway I was able to get it semi work with your suggestion, but not the way
I was hoping. When I ran it a pop up requesting the parameter to be entered.
What I wanted was to be able to input the parameter on the form and the query
would then pick the parameter from the form. I would also like to be abe to
do the same for update and delete queries.

Thank you

:

Sure,

Here is an example of one way. It assume that you have set up the example

Open the form 'Category' and view its properties.
On the data tab, note that the 'record source' is simply "Categories", the name of the table.
Change it to a parameterized select query, for isntance:
SELECT Categories.*, CategoryName AS cn FROM Categories WHERE (CategoryName LIKE ?)
Now, change the forms empty 'Input Parameters' to read:
cn char = [CategoryName LIKE what?]
Note that the parameter name, cn, is the name of the alias in the select query.
Now view the form, and you will be prompted with 'CategoryName LIKE what'.
If instead you make your 'Input Parameters' to read:
cn char = [myform]![myfield]
when you open the form (or refresh it) your parameterized query will get its value from myfield

Note that if your form is a subform, and myfield is on the parent form, then you don't even need to set the input parameters,
IF
YOU
RENAME MYFIELD to have the same name as the parameter, in this case 'cn'. This is one of the magic tircks that ADPs will do
for
you.

Cheers,


--
Malcolm Cook
Stowers Institute for Medical Research - Kansas City, MO USA


Is it possible to enter Parameter(or a retrieve specification) from a form in
an ADP select query. I used to do this all the time in Access 97, but i do
not seem to able to do this in an Access ADP. I just use to point to the Open
form, and the field that contains the data, sometimes the field on the form
could be unbound.

Thanks in advance, Im new to ADP projects.

 

[AkAlan via AccessMonster.com]

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]

 

[Guest]

Thank you for your help I will certainly give this a try. On the first
reading of this I semi understand what is happeing, but as you said it will
probably require reading a few times to fully understand whats going on.

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.