Filter Property VBA

I have a form "PriceListPrint" that has a field named "CategoryID" and command button to open a report "PriceListEnglish". In the form before the open report command there is this line: CategoryID="Rest"
In the report there is a subreport "PriceListBeef" that has 2 fields "CategoryIDRest" and
"CategoryIDRest2" I only want to show one or the other, not both, if I manually go into the subreport "PriceListBeef" in the Filter Property and write in either of the controls it works, When I tried to do it in code on the "On Load" of the subreport:
If Forms![PriceListPrint]![CategoryID]="Rest" then
Me.Filter=CategoryIDRest
Me.FilterOn=True
Else
Me.Filter=CategoryIDRest2
Me.FilterOn=True
End If
It gives this error "You Entered An Expression That Has No Value"
dailymeatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try your code in the OnOpen event of your subreport.

You might also be able to use conditional formatting on the report to show/hide the values, but your report would have to be able to determine which should be shown based on a value in the report itself, not an external object (like your form).
0
Jeffrey CoachmanMIS LiasonCommented:
Not sure what you have there, but the standard syntax for a filter is something like this:
Me.Filter="YourCountry='USA'"
Me.FilterOn=True
Here you have to set the filter on a field, ...to a value.
For example
State=NY
Country=USA
FirstName=Bob
Year=2015
...etc


So I am not quite sure what:
Me.Filter=CategoryIDRest
...is trying to do...

JeffCoachman
0
dailymeatAuthor Commented:
I did have it on the on load event, sorry.

Tried
 If Forms![PriceListPrint]![CategoryID]="Rest" then
Me.Filter="CategoryIDRest='Rest'"
Me.FilterOn=True
Else
Me.Filter="CategoryIDRest2='Rest2'"
Me.FilterOn=True
End If
It gives this error "The setting you set isin't valid for this property"
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
What line does this error on?
Without a sample for context, I cannot be sure what you have behind the scenes...

But the Filter setting need to reference a "Field", ...not really a control, it should be something like I stated above:
Me.Filter="YourCountry='USA'"
Me.FilterOn=True
Here you have to set the filter on a field, ...to a value.
For example
"State='NY'"
"Country="'USA'"
"FirstName='Bob'"
"Year=" & 2015
...etc

The double quotes signifies the filter setting as a "Literal" string, ...so you cannot directly insert a subreport reference.

Perhaps I am missing something,...
Lets see what Scott comes back with...

JeffCoachman
0
dailymeatAuthor Commented:
The line for the error is on:   Me.Filter="CategoryIDRest='Rest'"

The CategoryIDRest and CategoryIDRest2 are fields, the information in CategoryIDRest field is "rest"
and the information in CategoryIDRest2 field is "rest2"
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The CategoryIDRest and CategoryIDRest2 are fields
Are they actually on your report - that is, you dragged them to the report Detail section? Reports are funny that way - unlike Forms, the objects you refer to for things like this must be actually on the report in many cases.

If you HAVE placed them on the report, then the only other suggestion I have is this:

Open the subreport directly in Design view, and set the value above in the Properties sheet. Open that report, and see if the report shows the correct data.

If not, then there's a problem with your report and/or data, and you'd need to fix that.

If so, then we'll keep working on it with you ...
0
dailymeatAuthor Commented:
Are they actually on your report
 Yes they are in the subreport

 you dragged them to the report Detail section?
Yes they are in the Detail section

Open the subreport directly in Design view,
Yes I have done that, and it shows the correct information
0
Jeffrey CoachmanMIS LiasonCommented:
Open the subreport directly in Design view,
To be clear, you not only opened the sub report in design view, ...but you actually set the filter, ...and the correct filtered data was displayed?

I'll let you continue on with Scott to avoid confusion...

Jeff

...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As Jeff said - You actually set the filter, and the correct data was displayed? If so, can you post a screenshot of the subreport in design view, and show the Properties tab where the Filter properties are set?
0
dailymeatAuthor Commented:
Here is the screen shot
Untitled.jpg
0
Jeffrey CoachmanMIS LiasonCommented:
Please post a small sample database that exhibits this issue.
0
dailymeatAuthor Commented:
I am trying to make a small sample of the database that you requested but having trouble, I will get it to you as soon as I can.
Thanks.
0
dailymeatAuthor Commented:
Here is the sample database
db1.mdb
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your database includes a linked table on your Z drive, and of course we don't have that database - so we can't really run it.

That said, the code errors on this line in the Report Open event:

Me.Filter = CategoryIDRest

As mentioned previously, a Filter should be constructed as "FieldName=Value". If we could see the table, and determine the structure, we might be able to help further.
0
dailymeatAuthor Commented:
Sorry! Here is the database again with the table
db1.mdb
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
1. Move your code in the Open event back to the Load event.

2. Change this:
    If Forms![PriceListsPrint]![CategoryID] = "Rest" Then
        Me.Filter = "CategoryIDRest='Rest'"
        Me.FilterOn = True
    Else
        Me.Filter = "CategoryIDRest2='Rest2'"
        Me.FilterOn = True
    End If

3. Make sure you add the "CategoryIDRest2" and "CategoryIDRest" fields to the subreport, as we instructed you to do previously.
0
dailymeatAuthor Commented:
I don't have a load event in the report, my choices are "On Open" or "On Activate".

"On Open" gives me an error "The setting you enter isn't valid for this property".
When I clicked on debug, this line is highlighted in yellow: Me.Filter = "CategoryIDRest='Rest'"

"On Activate" shows the report but with the information for both fields ( "CategoryIDRest2" and "CategoryIDRest").

Tried in the detail section of the report "On Format" and "On Print" gives me an error "The setting you enter isn't valid for this property".
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There has been a Load event for the Report object since Access 95 (at least). REport Load
0
dailymeatAuthor Commented:
What am I doing wrong?
0
dailymeatAuthor Commented:
Here is the file that  I meant to send you in my last comment
Untitled.jpg
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
My bad - the Load Event (along with several others) were added in 2007.

You can try doing this in the OnFormat event of the Page Header, but I don't know if it'll work for what you want.

Otherwise, you're stuck with (a) basing the subreport on a Query and then (b) modifying that query prior to running your report. You can do that from VBA, of course.
0
dailymeatAuthor Commented:
Thank you. It does not work in the OnFormat event.
Not familiar how to modify a query in VBA
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Actually, you could just directly refer to your filter form in the query.

1. Create a Query from the Recordsource in your subreport. To do that, open the Subreport in Design view, and "edit" the recordsource. This will show the recordsource in the query design window. Save that as a query (name it something like "qryProducts"). Access will change the Recordsource property from your SELECT statement to the name of your query.

2. Now open the new query in Design view. In the Criteria rows for CategoryIDRest2 and CategoryIDRest, Refer to the control on your form that should filter those items:

Query referring directly to a Form
Be SURE to structure this the way shown - that is, you should be using 2 criteria rows to show this data, not just one.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dailymeatAuthor Commented:
Thank you. That worked. With my initial question I failed to mention that I have 7 subreports with different products (I did not wanted to make it complicated), it works fine for one, I would hate to have to do 7 queries or 7 command buttons for each product.
Is there a better way?
Thanks.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure if there is a better way, other than to normalize your database.

Right now, you have 3 distinct "Category" fields, and you're storing data such as Time/Dates for different days, multiple prices, etc etc in the Products table. It would seem you'd have a Products table, and then a Category table, and then a "Join" table between those two. Also, you'd have pricing tables (assuming pricing for a Product is different for different customers, of course). I've dealt with a LOT of inventory data, and the single most important thing is getting the data to a properly normalized structure.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.