choosing multiple parameters for a report

[Donna]

I would like to have a form that has a list box that allows the user to
select several items. Then on that form I would like a command button that
runs a report based on the items selected in the list box. I have a form
named "frmSelectCategory" that has a listbox named "Category". I have a
report named "5162 Labels by Category". I have a query that runs the report
named "qryLabels". I went to the Dev Ashish website that you suggested in a
similar question & below is the code I have entered in the ON CLICK event of
the command button on the "frmSelectCategory". It runs the report but no
matter what I select in the listbox, it displays ALL categories. What am I
doing wrong? Thanks in advance!

 

[Allen Browne]

You have a list box where the user can choose one or more categories, and
have the report print out the labels in those categories?

If that's the idea, see:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

 

[Donna]

Note 4 says to drop the Open Args & pass the description in a public string
variable, & then use the format event of the report header to read the string
and assign the value to an unbound text box. Could you please show me what
the code would look like for that and what I would use as the control source
for the unbound text box? Thanks!

 

[Allen Browne]

1. In a standard module (Modules tab of Database window), in the General
Declarations section (top, with the Option statements:
Dim gstrReportFilterDescrip As String

2. In the code that opens the report, set the string first:
gstrReportFilterDescrip = strDescrip
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere

3. Add a text box to the Report Header section of the report to show this
description. Name it (say) txtFilterDescrip. Leave its Control Source blank.

4. In the Open event of the report:
If Len(gstrReportFilterDescrip) > 0 Then
Me.txtFilterDescrip.ControlSource = "=""" & gstrReportFilterDescrip
& """"
gstrReportFilterDescrip = vbNullString
End If

 

[Joe-Al]

What is the user DOES NOT select a item. How do I pass a NULL to the report
(display all records)

 

[Marshall Barton]

What is the user DOES NOT select a item. How do I pass a NULL to the report
(display all records)

In that case, the code should not include the criteria in
the query/filter so there is no need to "pass" a Null.

 

[Joe-Al]

Sorry, I meant to say passing a WILDCARD "*" not NULL. I would like to option
that if they DO NOT CHOOSE an item from the list, to pass a wildcard (all
records) for the filter criteria. Like the query [variablename] allows "*"
for all records.

 

[Marshall Barton]

Sorry, I meant to say passing a WILDCARD "*" not NULL. I would like to option
that if they DO NOT CHOOSE an item from the list, to pass a wildcard (all
records) for the filter criteria. Like the query [variablename] allows "*"
for all records.

You should not have to do that either. The code that
generates the criteria should be something like:

If Not IsNull(somevalue) Then
strWhere = strWhere & "somefield=" & somevalue
End If

Which will omit the criteria for somefield when somevalue is
Null. If there is no criteria fo a field then the records
will not be filtered by that field.

If your situation is different, then I will beed to
understand what you really are doing. I thought you said
you were using standard filtering codem but if its not
working, then I will have to see your code before I can see
what its doing/
..

 

[06»á¼Æ]

HAPPY

 

[Nick]

Running the code I receive a complier error stating method or Data member not
found. The 1stCatagory is highlighted in Blue and a Yellow highlight is on
the Private Sub cmdPreview_Click(). What is my error? I do not understand the
complier error.

Sorry, I meant to say passing a WILDCARD "*" not NULL. I would like to option
that if they DO NOT CHOOSE an item from the list, to pass a wildcard (all
records) for the filter criteria. Like the query [variablename] allows "*"
for all records.

You should not have to do that either. The code that
generates the criteria should be something like:

If Not IsNull(somevalue) Then
strWhere = strWhere & "somefield=" & somevalue
End If

Which will omit the criteria for somefield when somevalue is
Null. If there is no criteria fo a field then the records
will not be filtered by that field.

If your situation is different, then I will beed to
understand what you really are doing. I thought you said
you were using standard filtering codem but if its not
working, then I will have to see your code before I can see
what its doing/
..