Filter report based on form 4

[davsanto]

I have a report based on a query.

Part of the report consists of products and categories.

I have a form that will allow the user to select a specific product and a specific category. This will produce a report that will show only the product and category selected.

For example

the user selects Product A and Category B

And reviews the report with only these items listed.

The Problem:

Currently I can only filter the report on either the product or the category but not both. So I get

Product A and all the categories or

Category B and all the products.

Unfortuately I am not a VB programmer. I need someway to use the combination of the product and category selections to filter the report.

Thanks in advance for you help.

 

[GKProgrammer]

Hi davsanto,

In your query why don't you set the criteria for the Product and Category field to the value that is selected in the form. Then when the report is opened the query will only display the values that coincide with the selections on the form. This is assuming that the form will be open when the report is opened (this sounds like it is the case).

For example set the category field criteria section to:
YourFormName!CategoryFieldName

The same for the product criteria section:
YourFormName!ProductFieldName

Let me know if this helps. Regards,
gkprogrammer

 

[davsanto]

Thanks GKProgrammer

Wow that is a realy simple and clean way of doing it. It works at least when I run the query. How do I open the report from the form that will display my selected criteria on the report.

Thanks Again!

David

 

[GKProgrammer]

From a command button On_Click event you could enter something like the following:

If Nz(CategoryFieldName, &quot;&quot <> &quot;&quot; And Nz(ProductFieldName, &quot;&quot <> &quot;&quot; Then
DoCmd.OpenReport, &quot;ReportName&quot;, acViewPreview
Else
MsgBox &quot;Let user know to enter a value for Product and Category&quot;
End If Regards,
gkprogrammer

 

[FancyPrairie]

To ensure that the user prints the set of records shown on the form, I generally do the following in the OnOpen event of the report:

1. Set the Recordsource property of the report equal to the Recordsource property of the form (if the form IsLoaded). This is not really necessary unless the user is allowed to change the Recordsource of the form (which, in some instances, may be a valid thing for the user to do (i.e. switching from Live to Test or Live to Archived data)).
2. If the FilterOn property of the form is True, set the Filter Property of the report equal to the Filter Property of the form (be sure to set the FilterOn property of the report to True). Note that you can also use the Filter option of the Docmd.OpenReport method.
3. If the OrderByOn Property of the form is True, then set the Order By property of the report equal to the Order By Property of the report (be sure to set the OrderByOn property of the report True)
4. If you want to print just the current record shown on the form, then set the Where option of the Docmd.OpenReport method.

 

[davsanto]

thanks again gkprogrammer

I get a compile error on the follow line:

&quot;argument not optional&quot;

DoCmd.OpenReport , &quot;Test Report 2&quot;, acViewPreview

thanks

das

 

[GKProgrammer]

oops...

I accidentally added an extra comma, please omit the red comma after DoCmd.OpenReport below:

DoCmd.OpenReport [red],[/red] &quot;Test Report 2&quot;, acViewPreview Regards,
gkprogrammer

 

[davsanto]

FancyPrairie

Thanks FP

You way almost works! The problem here is using the filter on the form. If I filter only one variable it works but when I put in two it does not work.

das

 

[davsanto]

GKProgrammer

Thanks again! Crazy comma - yep it works but I don't think I am passing the selections from the form to the query. In other words when I access the report direcly I am asked for the product and category and receive a report as selected.

When I try from the form the report is blank.

thanks for your patience - my skills in this area is weak.

das

 

[GKProgrammer]

It sounds like you don't have the correct format entered under the Criteria field in your query. Make sure that the format looks as follows:

[Forms]![YourFormName]![ProductFieldName]
[Forms]![YourFormName]![CategoryFieldName]

Also remember the form must be open to able to read the selected values from the form.

Hope this helps Regards,
gkprogrammer

 

[davsanto]

GOT IT! Works great!

YEP had the wrong info in one of the criteria field

and the report had the filter on.

One more item

How do I display the selected product and then all the categories if no categories are select. in other words all categories are displayed by default for the selected record

Thank!

das

 

[jr4939]

I have one form consisting only of a combo box containing a list of countries and a Close
command button. When the Close button is clicked, I want the records for another form
(based on a query)to be filtered for the selected country. Can't figure out how to do this.
TIA for any help!

 

[GKProgrammer]

davsanto,

Sure there is a way to make this work. It does change things but just a little bit. Go back into the criteria section of the query and add the word [red] Like [/red] in front, like this:

Like [Forms]![YourFormName]![ProductFieldName]
Like [Forms]![YourFormName]![CategoryFieldName]

Now I am not sure what kind of control you are using for your selection on the form but you could change the code in the On_Click event for the button to something like this:

If Nz(CategoryFieldName, &quot;&quot = &quot;&quot; Then CategoryFieldName = &quot;*&quot;
If Nz(ProductFieldName, &quot;&quot = &quot;&quot; Then CategoryFieldName = &quot;*&quot;
DoCmd.OpenReport &quot;ReportName&quot;, acViewPreview
If CategoryFieldName = &quot;*&quot; then CategoryFieldName = &quot;&quot;
If ProductFieldName = &quot;*&quot; then ProductFieldName = &quot;&quot;

Hope this makes sense, let me know.

Regards,
gkprogrammer

 

[FancyPrairie]

The way I suggested shouldn't make any difference how many variables you filter on. The report should still print same info as the form. On the OnOpen event of the Report do this:

dim frm as form

if (CurrentProject.AllForms(&quot;frmYours&quot.IsLoaded) then
set frm = forms(&quot;frmYours&quot

me.RecordSource = frm.RecordSource
if (frm.FilterOn) then
me.Filter = frm.Filter
me.FilterOn = true
end if

if (frm.OrderByOn) then
me.OrderBy = frm.OrderBy
me.OrderByOn = true
end if
end if

 

[davsanto]

Thanks Again GKProgrammer!

I understand your method and it works great!

das

 

[davsanto]

GKProgrammer

One more question if you please

If I change the MULTI SELECT to simple for the property on the select category input of the form what do I need to change in the code to allow more than one category to be displayed in the report?

Thanks

das