FilterOn property not work for sub-reports??

  • Thread starter Thread starter Jeff Conrad
  • Start date Start date
J

Jeff Conrad

Hi,

Using Access 97 here.

Background:
I have a form that launches four different vendor reports.
Each of these reports have no sub-reports. By using option
boxes on the form the user can select to print all the
vendors or by using a list box they can pick and choose
which ones to print. With help supplied here long ago I
use the following code in each Report's Open event to
filter the vendor list like so:

(Excuse the line wrapping)

If [Forms]![frmVendorReports]![lstVendors].Enabled = False
Then
'Skip over the filter area
Else
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strFilter As String

Set frm = Forms!frmVendorReports
Set ctl = frm!lstVendors
For Each varItm In ctl.ItemsSelected
strFilter = strFilter & "VendorID=" &
ctl.ItemData(varItm) & " OR "
Next varItm
strFilter = Left(strFilter, Len(strFilter) - 4)
FilterOn = True
Filter = strFilter
End If

This code works beautifully for these four reports. If the
list box is enabled then the filter string is built based
on what items were selected. Keep in mind this works
PERFECT for these four reports!

Now I created two additional vendor reports. Using Duane
Hookom's samples as a guide I created a report that lists
all the order days and one for all the delivery days. They
each have seven sub-reports (one for each day) like a
weekly calendar. Each report looks GREAT and works just
fine by itself showing all vendors with orders/deliveries.
I want to now launch these reports from the same form and
give the user the ability to select only one or several
vendors to display if they so desire. Still with me?

Problem:
Well I thought all I needed to do was add the same code to
the Open event of the subreport. Wrong. It doesn't work. I
receive the following error:

"Run-Time Error 2101:
The setting you entered isn't valid for this property.
To see the valid settings for this property, search the
Help index for the name of the property."

The debugger stops on:
FilterOn = True

I don't get it! I stepped through the code and the Filter
string is built up perfectly. Looking at Help says nothing
that you can't have a filter on a sub-report. If I move
the code to the main report, it does absolutely nothing.
So why is this code failing on a sub-report?

Any thoughts or suggestions are welcome.
Jeff Conrad
Bend, Oregon
 
Jeff said:
Using Access 97 here.

Background:
I have a form that launches four different vendor reports.
Each of these reports have no sub-reports. By using option
boxes on the form the user can select to print all the
vendors or by using a list box they can pick and choose
which ones to print. With help supplied here long ago I
use the following code in each Report's Open event to
filter the vendor list like so:

(Excuse the line wrapping)

If [Forms]![frmVendorReports]![lstVendors].Enabled = False
Then
'Skip over the filter area
Else
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strFilter As String

Set frm = Forms!frmVendorReports
Set ctl = frm!lstVendors
For Each varItm In ctl.ItemsSelected
strFilter = strFilter & "VendorID=" &
ctl.ItemData(varItm) & " OR "
Next varItm
strFilter = Left(strFilter, Len(strFilter) - 4)
FilterOn = True
Filter = strFilter
End If

This code works beautifully for these four reports. If the
list box is enabled then the filter string is built based
on what items were selected. Keep in mind this works
PERFECT for these four reports!

Now I created two additional vendor reports. Using Duane
Hookom's samples as a guide I created a report that lists
all the order days and one for all the delivery days. They
each have seven sub-reports (one for each day) like a
weekly calendar. Each report looks GREAT and works just
fine by itself showing all vendors with orders/deliveries.
I want to now launch these reports from the same form and
give the user the ability to select only one or several
vendors to display if they so desire. Still with me?

Problem:
Well I thought all I needed to do was add the same code to
the Open event of the subreport. Wrong. It doesn't work. I
receive the following error:

"Run-Time Error 2101:
The setting you entered isn't valid for this property.
To see the valid settings for this property, search the
Help index for the name of the property."

The debugger stops on:
FilterOn = True

I don't get it! I stepped through the code and the Filter
string is built up perfectly. Looking at Help says nothing
that you can't have a filter on a sub-report. If I move
the code to the main report, it does absolutely nothing.
So why is this code failing on a sub-report?
Click to expand...


Well, I've never seen that one before, but I've had so many
problems with the Filter property that I may not have gotten
far enough to try it.

The way I do this kind of thing is to construct a record
source SQL string and assign that so I can forget about the
Filter issues.

When doing this in subreport's there is a catch, you have to
guard against doing it more than once, id the subreport can
have multiple instances in the main report. Assuming the
subreport's record source is a simple unfiltered SQL
statement, this is fairly easy using code like:

Sub Report_Open(
Static Initialized As Boolean
Dim strFilter As String
Dim varItm As Variant

If Not Initialized Then
With [Forms]![frmVendorReports
If ]![lstVendors].Enabled = False Then
'Skip over the filter area
Else
For Each varItm In !lstVendors.ItemsSelected
strFilter = strFilter & " OR " & "VendorID=" _
& !lstVendors.ItemData(varItm)
Next varItm
strFilter = " WHERE " & Mid(strFilter,5)
Me.RecordSource = Me.RecordSource & strFilter
End If
End With
Initialized = True
End If
 
Hi Marsh!

Thanks so much for your time and help.
Well I tried your suggestion and it didn't work.
I got Error#2467 in the Detail event of the main form:
"The expression you entered refers to an object that is
closed or doesn't exist..."

The problem may be because you said something about the
subreport being used more than once. This same subreport
is repeated 7 times across the report.

I do however have some steps to reproduce the problem
using Duane's sample! This may help you see the problem.

1. Download Duane's sample calendar reports:
http://www.invisibleinc.com/download.cfm?filFilID=6

2. Go to srptCalendar and put this code in the Open event:
If [Forms]![frmMain]![lstDoctors].Enabled = False Then
'Skip over the filter area
Else
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strFilter As String

Set frm = Forms!frmMain
Set ctl = frm!lstDoctors
For Each varItm In ctl.ItemsSelected
strFilter = strFilter & "[Doctor]=" &
ctl.ItemData(varItm) & " Or "
Next varItm
strFilter = Left(strFilter, Len(strFilter) - 4)
FilterOn = True
Filter = strFilter
End If

(I realize there is probably a syntax error in the For
Each line since it is a text field, but don't worry it)

3. On frmMain make room for a list box. Use the wizard to
create a listbox on tblDoctors only using the Doctor
field. Name the listbox lstDoctors and set the MultiSelect
to Simple.

4. Make a command button to open rptCalendar.

5. Open the form, select one doctor, and then open the
report. You should see the same error.

Any ideas on how to get around this?
I'm stumped!

Thanks for your time!
Jeff Conrad
Bend, Oregon
-----Original Message-----
Well, I've never seen that one before, but I've had so
many problems with the Filter property that I may not
have gotten far enough to try it.

The way I do this kind of thing is to construct a record
source SQL string and assign that so I can forget about
the Filter issues.

When doing this in subreport's there is a catch, you have
to guard against doing it more than once, id the
subreport can have multiple instances in the main
report. Assuming the subreport's record source is a
simple unfiltered SQL statement, this is fairly easy
using code like:

Sub Report_Open(
Static Initialized As Boolean
Dim strFilter As String
Dim varItm As Variant

If Not Initialized Then
With [Forms]![frmVendorReports
If ]![lstVendors].Enabled = False Then
'Skip over the filter area
Else
For Each varItm In ! lstVendors.ItemsSelected
strFilter = strFilter & " OR " & "VendorID=" _
& ! lstVendors.ItemData(varItm)
Next varItm
strFilter = " WHERE " & Mid (strFilter,5)
Me.RecordSource = Me.RecordSource & strFilter
End If
End With
Initialized = True
End If
Click to expand...
 
Jeff, it sounds like you put my suggested code in the main
report's Open event. That won't work because the subreports
open after the main report (the opposite of forms).

As for the multiple instances of a subreport, I was
referring to repeated instances of the same subreport
control, such as would occur if the subreport were in a
detail section and the main report had more than one detail
record.

Back to your trials and tribulations with the Filter
property, I really can't help you with this approach. As I
said before, using Filter and FilterOn have caused me no end
of trouble, so, until MS completely rewrites the code for
this feature, I've absolutely sworn off ever trying to make
them work.
--
Marsh
MVP [MS Access]



Jeff said:
Well I tried your suggestion and it didn't work.
I got Error#2467 in the Detail event of the main form:
"The expression you entered refers to an object that is
closed or doesn't exist..."

The problem may be because you said something about the
subreport being used more than once. This same subreport
is repeated 7 times across the report.

I do however have some steps to reproduce the problem
using Duane's sample! This may help you see the problem.

1. Download Duane's sample calendar reports:
http://www.invisibleinc.com/download.cfm?filFilID=6

2. Go to srptCalendar and put this code in the Open event:
If [Forms]![frmMain]![lstDoctors].Enabled = False Then
'Skip over the filter area
Else
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strFilter As String

Set frm = Forms!frmMain
Set ctl = frm!lstDoctors
For Each varItm In ctl.ItemsSelected
strFilter = strFilter & "[Doctor]=" &
ctl.ItemData(varItm) & " Or "
Next varItm
strFilter = Left(strFilter, Len(strFilter) - 4)
FilterOn = True
Filter = strFilter
End If

(I realize there is probably a syntax error in the For
Each line since it is a text field, but don't worry it)

3. On frmMain make room for a list box. Use the wizard to
create a listbox on tblDoctors only using the Doctor
field. Name the listbox lstDoctors and set the MultiSelect
to Simple.

4. Make a command button to open rptCalendar.

5. Open the form, select one doctor, and then open the
report. You should see the same error.

Any ideas on how to get around this?
I'm stumped!

Thanks for your time!
Jeff Conrad
Bend, Oregon
-----Original Message-----
Well, I've never seen that one before, but I've had so
many problems with the Filter property that I may not
have gotten far enough to try it.

The way I do this kind of thing is to construct a record
source SQL string and assign that so I can forget about
the Filter issues.

When doing this in subreport's there is a catch, you have
to guard against doing it more than once, id the
subreport can have multiple instances in the main
report. Assuming the subreport's record source is a
simple unfiltered SQL statement, this is fairly easy
using code like:

Sub Report_Open(
Static Initialized As Boolean
Dim strFilter As String
Dim varItm As Variant

If Not Initialized Then
With [Forms]![frmVendorReports
If ]![lstVendors].Enabled = False Then
'Skip over the filter area
Else
For Each varItm In ! lstVendors.ItemsSelected
strFilter = strFilter & " OR " & "VendorID=" _
& ! lstVendors.ItemData(varItm)
Next varItm
strFilter = " WHERE " & Mid (strFilter,5)
Me.RecordSource = Me.RecordSource & strFilter
End If
End With
Initialized = True
End If
Click to expand...
Click to expand...
 
Hi Marsh,

Comments below.
Jeff, it sounds like you put my suggested code in the main
report's Open event. That won't work because the
subreports open after the main report (the opposite of
forms).
Click to expand...

Nope, I did put your code in the Open event of the
subreport. My error handling code writes the actual
procedure location that triggers the error into a table. I
was quite surprised to see that this different error came
in the main report's Detail_Print event. Weird.
As for the multiple instances of a subreport, I was
referring to repeated instances of the same subreport
control, such as would occur if the subreport were in a
detail section and the main report had more than one
detail record.
Click to expand...

If you take a look at Duane's sample (which is VERY close
to what I have) it has one subreport repeated 7 times
across the length of the report in the Detail section of
the main report. This is what my subreport does as well.
Are you saying this code will not work in this instance
given these conditions?
Back to your trials and tribulations with the Filter
property, I really can't help you with this approach. As
I said before, using Filter and FilterOn have caused me
no end of trouble, so, until MS completely rewrites the
code for this feature, I've absolutely sworn off ever
trying to make them work.
Click to expand...

I don't use it that much as well, just in a few reports
like these other ones. I'm open to other suggestions you
may have. The worst case scenario I see is just leaving
the report as is: showing all the vendors every time the
report is opened. Nothing terrible by any means, just
thought it would be nice to give the user the ability to
choose which vendors to print. I'll have to put my
thinking cap on and see what else I can come up with.

Thanks for your time and help,
Jeff Conrad
Bend, Oregon
 
Comments inline.
--
Marsh
MVP [MS Access]


Jeff said:
Nope, I did put your code in the Open event of the
subreport. My error handling code writes the actual
procedure location that triggers the error into a table. I
was quite surprised to see that this different error came
in the main report's Detail_Print event. Weird.
Click to expand...

That completely lost me. How can the detail section's Print
event have anything to do with setting the record source in
the Open event??? All that tells me is that a lot of stuff
worked all the way through the formatting phase.

I suggest that you try to step through the code to find out
more details about what went wrong. I just can't tell what
might be causing "this different error".

If you take a look at Duane's sample (which is VERY close
to what I have) it has one subreport repeated 7 times
across the length of the report in the Detail section of
the main report. This is what my subreport does as well.
Are you saying this code will not work in this instance
given these conditions?
Click to expand...

Each subreport control should operate independently, so, No,
I don't see a problem with that.

I don't use it that much as well, just in a few reports
like these other ones. I'm open to other suggestions you
may have. The worst case scenario I see is just leaving
the report as is: showing all the vendors every time the
report is opened. Nothing terrible by any means, just
thought it would be nice to give the user the ability to
choose which vendors to print. I'll have to put my
thinking cap on and see what else I can come up with.
Click to expand...

I still think that setting the record source will work.
There are other ways to filter data than using a where
clause, but I only use temporary tables as a last resort.
 
Hi Marsh!

SUCCESS!!!!!!!!!!!!!!!!!!!!!!!!!
YEE-HAA!!!!!!!!!!!!!!!!!!!!!!!!!

I actually surprised myself and figured out a solution!

Here's the cliff notes version:

1. I followed your advice and stepped through the code
carefully. The code progressed just fine through the
subreport. It repeated seven times. At the end just before
exiting, the Me.RecordSource was:
qryrptVendorOrderDays WHERE VendorID=15 And VendorID=17

I thought that seemed strange, but no error came up.
The code then moved on to the Detail_Print area of the
main report. Basically what the code here does is
calculate what the maximum height of the subreport's are
and draws a nice line across. Still no problems.

But, the very last part of my code in the Detail_Print
area is: (line wrapping)

For I = 1 To 7
Me("txtNoOrders" & I).Visible = Me("srpt" &
I).Report.HasData = False
Next

What this does is hide a text box behind each subreport if
the subreport has any data. If not, keep it visible.

Well that's where the code coughed up a hairball with
Error 2467. I'm completely guessing here, but I thought
maybe Access was completely confused about what "exactly"
the record source of the subreports were, so it could not
evaluate whether it had any records or not. What do you
think??? Sort of like evaluating something to Null?? If I
commented out that code the report finally ran, but no
records showed up at all. "Ahh haa" said the idiot.

2. So then I thought the problem must lie in the record
source we are building in code. I probably should have
mentioned this earlier (oops sorry), but the record source
of the subreport is a saved query and not what I would
call a 'simple' one. This is the SQL of the saved query:

SELECT tblVendorOrderDates.VendorID,
tblVendors.VendorName, tblVendorOrderDates.WeekDayID,
tblWeekDays.WeekDayText, tblVendors.[Active?]
FROM tblWeekDays INNER JOIN (tblVendors INNER JOIN
tblVendorOrderDates ON tblVendors.VendorID =
tblVendorOrderDates.VendorID) ON tblWeekDays.WeekDayID =
tblVendorOrderDates.WeekDayID
WHERE (((tblVendors.[Active?])=[Forms]![frmVendorReports]!
[txtActive] Or (tblVendors.[Active?])=[Forms]!
[frmVendorReports]![txtActive2]))
ORDER BY tblVendors.VendorName;

Works perfect by itself.

So then I created a new query like so:
Select FROM qryrptVendorOrderDays WHERE VendorID=15 And
VendorID=17;

This is what Access was interpreting was the record source
of the subreport so I wanted to see if this query would
work by itself.
Access coughed up a huge hairball! I had to stop and clean
off the keyboard after that one! Well this pinpointed the
problem: the record source of the subreport we were
building up in code was invalid.

3. My next step was to make a saved query that WOULD work
to only show Vendors 15 and 17 along with all the other
information. Through trial and error I arrived at:

SELECT tblVendorOrderDates.VendorID,
tblVendors.VendorName, tblVendorOrderDates.WeekDayID,
tblWeekDays.WeekDayText, tblVendors.[Active?]
FROM tblWeekDays INNER JOIN (tblVendors INNER JOIN
tblVendorOrderDates ON tblVendors.VendorID =
tblVendorOrderDates.VendorID) ON tblWeekDays.WeekDayID =
tblVendorOrderDates.WeekDayID
WHERE (((tblVendors.[Active?])=[Forms]![frmVendorReports]!
[txtActive] Or (tblVendors.[Active?])=[Forms]!
[frmVendorReports]![txtActive2]) And
tblVendorOrderDates.VendorID=15 OR
tblVendorOrderDates.VendorID=17)
ORDER BY tblVendors.VendorName;

4. So now that I was armed with the correct SQL syntax I
figured I 'should' be able to build up the correct syntax
of the record source in code. It took LOTS of trial and
error and Debug.Print lines, but I FINALLY got it just
right. Using your code with a few changes I arrived at
this: (major line wrapping sorry)

Static Initialized As Boolean
Dim strFilter As String
Dim strBegFilter As String
Dim strEndFilter As String
Dim varItm As Variant

strBegFilter = "SELECT tblVendorOrderDates.VendorID,
tblVendors.VendorName, tblVendorOrderDates.WeekDayID,
tblWeekDays.WeekDayText, tblVendors.[Active?] FROM
tblWeekDays INNER JOIN (tblVendors INNER JOIN
tblVendorOrderDates ON tblVendors.VendorID =
tblVendorOrderDates.VendorID) ON tblWeekDays.WeekDayID =
tblVendorOrderDates.WeekDayID WHERE (((tblVendors.
[Active?]) = [Forms]![frmVendorReports]![txtActive] Or
(tblVendors.[Active?]) = [Forms]![frmVendorReports]!
[txtActive2])"

strEndFilter = " ORDER BY tblVendors.VendorName;"

If Not Initialized Then
With [Forms]![frmVendorReports]
If ![lstVendors].Enabled = False Then
'Skip over the filter area
Else
For Each varItm In !lstVendors.ItemsSelected
strFilter = strFilter & " OR "
& "tblVendorOrderDates.VendorID=" _
& !lstVendors.ItemData(varItm)
Next varItm
strFilter = " And " & Mid(strFilter, 5) & ")"
strFilter = strBegFilter & strFilter & strEndFilter
Me.RecordSource = strFilter
End If
End With
Initialized = True
End If

No errors, and the report only showed the two vendors!
Yes!!!
If the list box is disabled, the code just skips the whole
thing and leaves the record source intact as the saved
query.

So now only using one saved query, one main report, and
one subreport I can do ALL of the following from the form:
1. Show all Active vendor order days
2. Show all Inactive vendor order days
3. Show ALL vendor order days
4. Show only selected active vendor order days
5. Show only selected inactive vendor order days
6. Show selected vendor order days from a list of all
vendors

SWEET!!!!! Excuse me while I do a little victory dance!
This is exactly what I wanted! Now I just need to
duplicate this for the delivery days report.

Thank you SO much for the help Marsh!
The code helped immensely!

Jeff Conrad
Bend, Oregon
-----Original Message-----
Comments inline.
--
Marsh
MVP [MS Access]


Jeff said:
Nope, I did put your code in the Open event of the
subreport. My error handling code writes the actual
procedure location that triggers the error into a table. I
was quite surprised to see that this different error came
in the main report's Detail_Print event. Weird.
Click to expand...

That completely lost me. How can the detail section's Print
event have anything to do with setting the record source in
the Open event??? All that tells me is that a lot of stuff
worked all the way through the formatting phase.

I suggest that you try to step through the code to find out
more details about what went wrong. I just can't tell what
might be causing "this different error".

If you take a look at Duane's sample (which is VERY close
to what I have) it has one subreport repeated 7 times
across the length of the report in the Detail section of
the main report. This is what my subreport does as well.
Are you saying this code will not work in this instance
given these conditions?
Click to expand...

Each subreport control should operate independently, so, No,
I don't see a problem with that.

I don't use it that much as well, just in a few reports
like these other ones. I'm open to other suggestions you
may have. The worst case scenario I see is just leaving
the report as is: showing all the vendors every time the
report is opened. Nothing terrible by any means, just
thought it would be nice to give the user the ability to
choose which vendors to print. I'll have to put my
thinking cap on and see what else I can come up with.
Click to expand...

I still think that setting the record source will work.
There are other ways to filter data than using a where
clause, but I only use temporary tables as a last resort.

.
Click to expand...
 
It's been quite a journey, Jeff, but well worth the effort.

That sounds like quite a versatile report and your users
should love it (for about a week, then they'll expect even
greater things from you ;-)).

BTW, nice debugging/deductive reasoning you used to work
through all the various issues.
--
Marsh
MVP [MS Access]


Jeff said:
Hi Marsh!

SUCCESS!!!!!!!!!!!!!!!!!!!!!!!!!
YEE-HAA!!!!!!!!!!!!!!!!!!!!!!!!!

I actually surprised myself and figured out a solution!

Here's the cliff notes version:

1. I followed your advice and stepped through the code
carefully. The code progressed just fine through the
subreport. It repeated seven times. At the end just before
exiting, the Me.RecordSource was:
qryrptVendorOrderDays WHERE VendorID=15 And VendorID=17

I thought that seemed strange, but no error came up.
The code then moved on to the Detail_Print area of the
main report. Basically what the code here does is
calculate what the maximum height of the subreport's are
and draws a nice line across. Still no problems.

But, the very last part of my code in the Detail_Print
area is: (line wrapping)

For I = 1 To 7
Me("txtNoOrders" & I).Visible = Me("srpt" &
I).Report.HasData = False
Next

What this does is hide a text box behind each subreport if
the subreport has any data. If not, keep it visible.

Well that's where the code coughed up a hairball with
Error 2467. I'm completely guessing here, but I thought
maybe Access was completely confused about what "exactly"
the record source of the subreports were, so it could not
evaluate whether it had any records or not. What do you
think??? Sort of like evaluating something to Null?? If I
commented out that code the report finally ran, but no
records showed up at all. "Ahh haa" said the idiot.

2. So then I thought the problem must lie in the record
source we are building in code. I probably should have
mentioned this earlier (oops sorry), but the record source
of the subreport is a saved query and not what I would
call a 'simple' one. This is the SQL of the saved query:

SELECT tblVendorOrderDates.VendorID,
tblVendors.VendorName, tblVendorOrderDates.WeekDayID,
tblWeekDays.WeekDayText, tblVendors.[Active?]
FROM tblWeekDays INNER JOIN (tblVendors INNER JOIN
tblVendorOrderDates ON tblVendors.VendorID =
tblVendorOrderDates.VendorID) ON tblWeekDays.WeekDayID =
tblVendorOrderDates.WeekDayID
WHERE (((tblVendors.[Active?])=[Forms]![frmVendorReports]!
[txtActive] Or (tblVendors.[Active?])=[Forms]!
[frmVendorReports]![txtActive2]))
ORDER BY tblVendors.VendorName;

Works perfect by itself.

So then I created a new query like so:
Select FROM qryrptVendorOrderDays WHERE VendorID=15 And
VendorID=17;

This is what Access was interpreting was the record source
of the subreport so I wanted to see if this query would
work by itself.
Access coughed up a huge hairball! I had to stop and clean
off the keyboard after that one! Well this pinpointed the
problem: the record source of the subreport we were
building up in code was invalid.

3. My next step was to make a saved query that WOULD work
to only show Vendors 15 and 17 along with all the other
information. Through trial and error I arrived at:

SELECT tblVendorOrderDates.VendorID,
tblVendors.VendorName, tblVendorOrderDates.WeekDayID,
tblWeekDays.WeekDayText, tblVendors.[Active?]
FROM tblWeekDays INNER JOIN (tblVendors INNER JOIN
tblVendorOrderDates ON tblVendors.VendorID =
tblVendorOrderDates.VendorID) ON tblWeekDays.WeekDayID =
tblVendorOrderDates.WeekDayID
WHERE (((tblVendors.[Active?])=[Forms]![frmVendorReports]!
[txtActive] Or (tblVendors.[Active?])=[Forms]!
[frmVendorReports]![txtActive2]) And
tblVendorOrderDates.VendorID=15 OR
tblVendorOrderDates.VendorID=17)
ORDER BY tblVendors.VendorName;

4. So now that I was armed with the correct SQL syntax I
figured I 'should' be able to build up the correct syntax
of the record source in code. It took LOTS of trial and
error and Debug.Print lines, but I FINALLY got it just
right. Using your code with a few changes I arrived at
this: (major line wrapping sorry)

Static Initialized As Boolean
Dim strFilter As String
Dim strBegFilter As String
Dim strEndFilter As String
Dim varItm As Variant

strBegFilter = "SELECT tblVendorOrderDates.VendorID,
tblVendors.VendorName, tblVendorOrderDates.WeekDayID,
tblWeekDays.WeekDayText, tblVendors.[Active?] FROM
tblWeekDays INNER JOIN (tblVendors INNER JOIN
tblVendorOrderDates ON tblVendors.VendorID =
tblVendorOrderDates.VendorID) ON tblWeekDays.WeekDayID =
tblVendorOrderDates.WeekDayID WHERE (((tblVendors.
[Active?]) = [Forms]![frmVendorReports]![txtActive] Or
(tblVendors.[Active?]) = [Forms]![frmVendorReports]!
[txtActive2])"

strEndFilter = " ORDER BY tblVendors.VendorName;"

If Not Initialized Then
With [Forms]![frmVendorReports]
If ![lstVendors].Enabled = False Then
'Skip over the filter area
Else
For Each varItm In !lstVendors.ItemsSelected
strFilter = strFilter & " OR "
& "tblVendorOrderDates.VendorID=" _
& !lstVendors.ItemData(varItm)
Next varItm
strFilter = " And " & Mid(strFilter, 5) & ")"
strFilter = strBegFilter & strFilter & strEndFilter
Me.RecordSource = strFilter
End If
End With
Initialized = True
End If

No errors, and the report only showed the two vendors!
Yes!!!
If the list box is disabled, the code just skips the whole
thing and leaves the record source intact as the saved
query.

So now only using one saved query, one main report, and
one subreport I can do ALL of the following from the form:
1. Show all Active vendor order days
2. Show all Inactive vendor order days
3. Show ALL vendor order days
4. Show only selected active vendor order days
5. Show only selected inactive vendor order days
6. Show selected vendor order days from a list of all
vendors

SWEET!!!!! Excuse me while I do a little victory dance!
This is exactly what I wanted! Now I just need to
duplicate this for the delivery days report.

Thank you SO much for the help Marsh!
The code helped immensely!

Jeff Conrad
Bend, Oregon
-----Original Message-----
Comments inline.
--
Marsh
MVP [MS Access]


Jeff said:
Jeff, it sounds like you put my suggested code in the main
report's Open event. That won't work because the
subreports open after the main report (the opposite of
forms).

Nope, I did put your code in the Open event of the
subreport. My error handling code writes the actual
procedure location that triggers the error into a table. I
was quite surprised to see that this different error came
in the main report's Detail_Print event. Weird.
Click to expand...

That completely lost me. How can the detail section's Print
event have anything to do with setting the record source in
the Open event??? All that tells me is that a lot of stuff
worked all the way through the formatting phase.

I suggest that you try to step through the code to find out
more details about what went wrong. I just can't tell what
might be causing "this different error".

As for the multiple instances of a subreport, I was
referring to repeated instances of the same subreport
control, such as would occur if the subreport were in a
detail section and the main report had more than one
detail record.

If you take a look at Duane's sample (which is VERY close
to what I have) it has one subreport repeated 7 times
across the length of the report in the Detail section of
the main report. This is what my subreport does as well.
Are you saying this code will not work in this instance
given these conditions?
Click to expand...

Each subreport control should operate independently, so, No,
I don't see a problem with that.

Back to your trials and tribulations with the Filter
property, I really can't help you with this approach. As
I said before, using Filter and FilterOn have caused me
no end of trouble, so, until MS completely rewrites the
code for this feature, I've absolutely sworn off ever
trying to make them work.

I don't use it that much as well, just in a few reports
like these other ones. I'm open to other suggestions you
may have. The worst case scenario I see is just leaving
the report as is: showing all the vendors every time the
report is opened. Nothing terrible by any means, just
thought it would be nice to give the user the ability to
choose which vendors to print. I'll have to put my
thinking cap on and see what else I can come up with.
Click to expand...

I still think that setting the record source will work.
There are other ways to filter data than using a where
clause, but I only use temporary tables as a last resort.

.
Click to expand...
Click to expand...
 
Back
Top