filtering works for subreports but not main report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I built a calendar based on one of Duane Hookom's calendar samples:
http://www.access.hookom.net/Samples.htm

Now I'm trying to make my calendar dynamic using techniques from Martin
Green's website:
http://fontstuff.com/access/acctut19.htm

My calendar report has 4 subreports on it. The "Multi-Select List Box" form
I created for filtering works on each of the subreports individually, but
when I switch it to the main report, I get a "Enter Parameter Value" box for
"CS Person." I suspect this is because the field "CS Person" doesn't actually
appear on the main report, but I don't know how to work around this.

Any suggestions would be appreciated. The code for the "apply filter" button
on my form is below in case that helps.

Many thanks,
jkatj



Private Sub ApplyButton_Click()
Dim varItem As Variant
Dim strPerson As String
Dim strFilter As String
' Open report
DoCmd.OpenReport "Calendar - Landscape", acViewPreview
' Build criteria string from [CS Person box] listbox
For Each varItem In Me.CS_Person_box.ItemsSelected
strPerson = strPerson & ",'" & Me.CS_Person_box.ItemData(varItem) _
& "'"
Next varItem
If Len(strPerson) = 0 Then
strPerson = "Like '*'"
Else
strPerson = Right(strPerson, Len(strPerson) - 1)
strPerson = "IN('-Office Closed-'," & strPerson & ")"
End If
' Build filter string
strFilter = "[CS Person] " & strPerson
' Apply the filter and switch it on
With Reports![Calendar - Landscape]
.Filter = strFilter
.FilterOn = True
End With
End Sub
 
Duane,

Unfortunately, I'm far too unskilled a user to understand what to do from
your post. I mean I do know how to modify the SQL property of the record
source of the subreports, but I have no idea how to do it dynamically (like
from a form that opens a report). Is it done with the cool listbox function
you linked to? If so, can you give me a nudge on how to start?

Many thanks!

Duane Hookom said:
You won't be able to use that code as you have determined. You can modify the
SQL property of the record source of the subreports. Or, there is a generic
listbox function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
could be used in the record source of the subreports.

--
Duane Hookom
Microsoft Access MVP


jkatj said:
I built a calendar based on one of Duane Hookom's calendar samples:
http://www.access.hookom.net/Samples.htm

Now I'm trying to make my calendar dynamic using techniques from Martin
Green's website:
http://fontstuff.com/access/acctut19.htm

My calendar report has 4 subreports on it. The "Multi-Select List Box" form
I created for filtering works on each of the subreports individually, but
when I switch it to the main report, I get a "Enter Parameter Value" box for
"CS Person." I suspect this is because the field "CS Person" doesn't actually
appear on the main report, but I don't know how to work around this.

Any suggestions would be appreciated. The code for the "apply filter" button
on my form is below in case that helps.

Many thanks,
jkatj



Private Sub ApplyButton_Click()
Dim varItem As Variant
Dim strPerson As String
Dim strFilter As String
' Open report
DoCmd.OpenReport "Calendar - Landscape", acViewPreview
' Build criteria string from [CS Person box] listbox
For Each varItem In Me.CS_Person_box.ItemsSelected
strPerson = strPerson & ",'" & Me.CS_Person_box.ItemData(varItem) _
& "'"
Next varItem
If Len(strPerson) = 0 Then
strPerson = "Like '*'"
Else
strPerson = Right(strPerson, Len(strPerson) - 1)
strPerson = "IN('-Office Closed-'," & strPerson & ")"
End If
' Build filter string
strFilter = "[CS Person] " & strPerson
' Apply the filter and switch it on
With Reports![Calendar - Landscape]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Click to expand...
Click to expand...
 
Using the generic multiselect list box function should allow you to create
your subreport record source without modifying any SQL properties.

To change the SQL property of a saved query, you would need to do this prior
to opening your report with subreports. The DAO code might look something
like:

CurrentDb.QueryDefs("qselYourName").SQL = "SELECT ... FROM .... WHERE ..."

--
Duane Hookom
Microsoft Access MVP


jkatj said:
Duane,

Unfortunately, I'm far too unskilled a user to understand what to do from
your post. I mean I do know how to modify the SQL property of the record
source of the subreports, but I have no idea how to do it dynamically (like
from a form that opens a report). Is it done with the cool listbox function
you linked to? If so, can you give me a nudge on how to start?

Many thanks!

Duane Hookom said:
You won't be able to use that code as you have determined. You can modify the
SQL property of the record source of the subreports. Or, there is a generic
listbox function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
could be used in the record source of the subreports.

--
Duane Hookom
Microsoft Access MVP


jkatj said:
I built a calendar based on one of Duane Hookom's calendar samples:
http://www.access.hookom.net/Samples.htm

Now I'm trying to make my calendar dynamic using techniques from Martin
Green's website:
http://fontstuff.com/access/acctut19.htm

My calendar report has 4 subreports on it. The "Multi-Select List Box" form
I created for filtering works on each of the subreports individually, but
when I switch it to the main report, I get a "Enter Parameter Value" box for
"CS Person." I suspect this is because the field "CS Person" doesn't actually
appear on the main report, but I don't know how to work around this.

Any suggestions would be appreciated. The code for the "apply filter" button
on my form is below in case that helps.

Many thanks,
jkatj



Private Sub ApplyButton_Click()
Dim varItem As Variant
Dim strPerson As String
Dim strFilter As String
' Open report
DoCmd.OpenReport "Calendar - Landscape", acViewPreview
' Build criteria string from [CS Person box] listbox
For Each varItem In Me.CS_Person_box.ItemsSelected
strPerson = strPerson & ",'" & Me.CS_Person_box.ItemData(varItem) _
& "'"
Next varItem
If Len(strPerson) = 0 Then
strPerson = "Like '*'"
Else
strPerson = Right(strPerson, Len(strPerson) - 1)
strPerson = "IN('-Office Closed-'," & strPerson & ")"
End If
' Build filter string
strFilter = "[CS Person] " & strPerson
' Apply the filter and switch it on
With Reports![Calendar - Landscape]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 
I apologize, Duane, but I'm still not understanding at all. Are you
recommending that I modify the SQL properties or not? If so, how does that
work when multiple people are trying to access the calendar? If I'm trying to
open the calendar to show just Chicago things (which modifies the SQL
properties that way) and someone else tries to open the calendar to show New
York things (which modifies the SQL properties a different way), well, I just
can't get my head around it. Different people trying to make changes to the
same query, that can't be good, can it?

I apologize for taking your time on this. I think this one must be way
beyond my capabilities. It seemed like it should be relatively easy when I
started.

Duane Hookom said:
Using the generic multiselect list box function should allow you to create
your subreport record source without modifying any SQL properties.

To change the SQL property of a saved query, you would need to do this prior
to opening your report with subreports. The DAO code might look something
like:

CurrentDb.QueryDefs("qselYourName").SQL = "SELECT ... FROM .... WHERE ..."

--
Duane Hookom
Microsoft Access MVP


jkatj said:
Duane,

Unfortunately, I'm far too unskilled a user to understand what to do from
your post. I mean I do know how to modify the SQL property of the record
source of the subreports, but I have no idea how to do it dynamically (like
from a form that opens a report). Is it done with the cool listbox function
you linked to? If so, can you give me a nudge on how to start?

Many thanks!

Duane Hookom said:
You won't be able to use that code as you have determined. You can modify the
SQL property of the record source of the subreports. Or, there is a generic
listbox function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
could be used in the record source of the subreports.

--
Duane Hookom
Microsoft Access MVP


:

I built a calendar based on one of Duane Hookom's calendar samples:
http://www.access.hookom.net/Samples.htm

Now I'm trying to make my calendar dynamic using techniques from Martin
Green's website:
http://fontstuff.com/access/acctut19.htm

My calendar report has 4 subreports on it. The "Multi-Select List Box" form
I created for filtering works on each of the subreports individually, but
when I switch it to the main report, I get a "Enter Parameter Value" box for
"CS Person." I suspect this is because the field "CS Person" doesn't actually
appear on the main report, but I don't know how to work around this.

Any suggestions would be appreciated. The code for the "apply filter" button
on my form is below in case that helps.

Many thanks,
jkatj



Private Sub ApplyButton_Click()
Dim varItem As Variant
Dim strPerson As String
Dim strFilter As String
' Open report
DoCmd.OpenReport "Calendar - Landscape", acViewPreview
' Build criteria string from [CS Person box] listbox
For Each varItem In Me.CS_Person_box.ItemsSelected
strPerson = strPerson & ",'" & Me.CS_Person_box.ItemData(varItem) _
& "'"
Next varItem
If Len(strPerson) = 0 Then
strPerson = "Like '*'"
Else
strPerson = Right(strPerson, Len(strPerson) - 1)
strPerson = "IN('-Office Closed-'," & strPerson & ")"
End If
' Build filter string
strFilter = "[CS Person] " & strPerson
' Apply the filter and switch it on
With Reports![Calendar - Landscape]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Using the generic list box function requires a one time design change to the
subreport's record source. There would be no "run-time" changes.

Changing the SQL property of a query that is the record source would be
run-time changes executed prior to each time the main report is opened. In a
typical system, each user would have their own copy of the front-end mdb so
changing the SQL of a saved query would only effect the one user.

--
Duane Hookom
Microsoft Access MVP


jkatj said:
I apologize, Duane, but I'm still not understanding at all. Are you
recommending that I modify the SQL properties or not? If so, how does that
work when multiple people are trying to access the calendar? If I'm trying to
open the calendar to show just Chicago things (which modifies the SQL
properties that way) and someone else tries to open the calendar to show New
York things (which modifies the SQL properties a different way), well, I just
can't get my head around it. Different people trying to make changes to the
same query, that can't be good, can it?

I apologize for taking your time on this. I think this one must be way
beyond my capabilities. It seemed like it should be relatively easy when I
started.

Duane Hookom said:
Using the generic multiselect list box function should allow you to create
your subreport record source without modifying any SQL properties.

To change the SQL property of a saved query, you would need to do this prior
to opening your report with subreports. The DAO code might look something
like:

CurrentDb.QueryDefs("qselYourName").SQL = "SELECT ... FROM .... WHERE ..."

--
Duane Hookom
Microsoft Access MVP


jkatj said:
Duane,

Unfortunately, I'm far too unskilled a user to understand what to do from
your post. I mean I do know how to modify the SQL property of the record
source of the subreports, but I have no idea how to do it dynamically (like
from a form that opens a report). Is it done with the cool listbox function
you linked to? If so, can you give me a nudge on how to start?

Many thanks!

:

You won't be able to use that code as you have determined. You can modify the
SQL property of the record source of the subreports. Or, there is a generic
listbox function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
could be used in the record source of the subreports.

--
Duane Hookom
Microsoft Access MVP


:

I built a calendar based on one of Duane Hookom's calendar samples:
http://www.access.hookom.net/Samples.htm

Now I'm trying to make my calendar dynamic using techniques from Martin
Green's website:
http://fontstuff.com/access/acctut19.htm

My calendar report has 4 subreports on it. The "Multi-Select List Box" form
I created for filtering works on each of the subreports individually, but
when I switch it to the main report, I get a "Enter Parameter Value" box for
"CS Person." I suspect this is because the field "CS Person" doesn't actually
appear on the main report, but I don't know how to work around this.

Any suggestions would be appreciated. The code for the "apply filter" button
on my form is below in case that helps.

Many thanks,
jkatj



Private Sub ApplyButton_Click()
Dim varItem As Variant
Dim strPerson As String
Dim strFilter As String
' Open report
DoCmd.OpenReport "Calendar - Landscape", acViewPreview
' Build criteria string from [CS Person box] listbox
For Each varItem In Me.CS_Person_box.ItemsSelected
strPerson = strPerson & ",'" & Me.CS_Person_box.ItemData(varItem) _
& "'"
Next varItem
If Len(strPerson) = 0 Then
strPerson = "Like '*'"
Else
strPerson = Right(strPerson, Len(strPerson) - 1)
strPerson = "IN('-Office Closed-'," & strPerson & ")"
End If
' Build filter string
strFilter = "[CS Person] " & strPerson
' Apply the filter and switch it on
With Reports![Calendar - Landscape]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your time and patience, Duane. I was hoping there was a simple
tweak to make Martin Green's form work for my calendar or another solution as
easy as that, but clearly that's not the case. I think I'm going to have to
go the very unappealing route of making a copy of the calendar report (and
each of its subreports) for each city; I just don't see any other way. I
don't think I have my database set up the "typical" way you describe (we all
access the same one, we don't have multiple copies of it), and nothing you're
saying is making any sense to me. Unfortunately, I'm far too inexperienced
for whatever you're suggesting, I reckon.

Again, I thank you for your time and efforts. You're a great guy to have
around the boards.

Duane Hookom said:
Using the generic list box function requires a one time design change to the
subreport's record source. There would be no "run-time" changes.

Changing the SQL property of a query that is the record source would be
run-time changes executed prior to each time the main report is opened. In a
typical system, each user would have their own copy of the front-end mdb so
changing the SQL of a saved query would only effect the one user.

--
Duane Hookom
Microsoft Access MVP


jkatj said:
I apologize, Duane, but I'm still not understanding at all. Are you
recommending that I modify the SQL properties or not? If so, how does that
work when multiple people are trying to access the calendar? If I'm trying to
open the calendar to show just Chicago things (which modifies the SQL
properties that way) and someone else tries to open the calendar to show New
York things (which modifies the SQL properties a different way), well, I just
can't get my head around it. Different people trying to make changes to the
same query, that can't be good, can it?

I apologize for taking your time on this. I think this one must be way
beyond my capabilities. It seemed like it should be relatively easy when I
started.

Duane Hookom said:
Using the generic multiselect list box function should allow you to create
your subreport record source without modifying any SQL properties.

To change the SQL property of a saved query, you would need to do this prior
to opening your report with subreports. The DAO code might look something
like:

CurrentDb.QueryDefs("qselYourName").SQL = "SELECT ... FROM .... WHERE ..."

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Unfortunately, I'm far too unskilled a user to understand what to do from
your post. I mean I do know how to modify the SQL property of the record
source of the subreports, but I have no idea how to do it dynamically (like
from a form that opens a report). Is it done with the cool listbox function
you linked to? If so, can you give me a nudge on how to start?

Many thanks!

:

You won't be able to use that code as you have determined. You can modify the
SQL property of the record source of the subreports. Or, there is a generic
listbox function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
could be used in the record source of the subreports.

--
Duane Hookom
Microsoft Access MVP


:

I built a calendar based on one of Duane Hookom's calendar samples:
http://www.access.hookom.net/Samples.htm

Now I'm trying to make my calendar dynamic using techniques from Martin
Green's website:
http://fontstuff.com/access/acctut19.htm

My calendar report has 4 subreports on it. The "Multi-Select List Box" form
I created for filtering works on each of the subreports individually, but
when I switch it to the main report, I get a "Enter Parameter Value" box for
"CS Person." I suspect this is because the field "CS Person" doesn't actually
appear on the main report, but I don't know how to work around this.

Any suggestions would be appreciated. The code for the "apply filter" button
on my form is below in case that helps.

Many thanks,
jkatj



Private Sub ApplyButton_Click()
Dim varItem As Variant
Dim strPerson As String
Dim strFilter As String
' Open report
DoCmd.OpenReport "Calendar - Landscape", acViewPreview
' Build criteria string from [CS Person box] listbox
For Each varItem In Me.CS_Person_box.ItemsSelected
strPerson = strPerson & ",'" & Me.CS_Person_box.ItemData(varItem) _
& "'"
Next varItem
If Len(strPerson) = 0 Then
strPerson = "Like '*'"
Else
strPerson = Right(strPerson, Len(strPerson) - 1)
strPerson = "IN('-Office Closed-'," & strPerson & ")"
End If
' Build filter string
strFilter = "[CS Person] " & strPerson
' Apply the filter and switch it on
With Reports![Calendar - Landscape]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 
At least one of Martin Green's solutions uses a list box on a form. The
generic list box function that I pointed you to could apply to his solution.

There are two queries in the sample mdb that use the function. The two
queries are used as the record sources of subforms but could just as easily
be used as the record sources of subreports.

--
Duane Hookom
Microsoft Access MVP


jkatj said:
Thanks for your time and patience, Duane. I was hoping there was a simple
tweak to make Martin Green's form work for my calendar or another solution as
easy as that, but clearly that's not the case. I think I'm going to have to
go the very unappealing route of making a copy of the calendar report (and
each of its subreports) for each city; I just don't see any other way. I
don't think I have my database set up the "typical" way you describe (we all
access the same one, we don't have multiple copies of it), and nothing you're
saying is making any sense to me. Unfortunately, I'm far too inexperienced
for whatever you're suggesting, I reckon.

Again, I thank you for your time and efforts. You're a great guy to have
around the boards.

Duane Hookom said:
Using the generic list box function requires a one time design change to the
subreport's record source. There would be no "run-time" changes.

Changing the SQL property of a query that is the record source would be
run-time changes executed prior to each time the main report is opened. In a
typical system, each user would have their own copy of the front-end mdb so
changing the SQL of a saved query would only effect the one user.

--
Duane Hookom
Microsoft Access MVP


jkatj said:
I apologize, Duane, but I'm still not understanding at all. Are you
recommending that I modify the SQL properties or not? If so, how does that
work when multiple people are trying to access the calendar? If I'm trying to
open the calendar to show just Chicago things (which modifies the SQL
properties that way) and someone else tries to open the calendar to show New
York things (which modifies the SQL properties a different way), well, I just
can't get my head around it. Different people trying to make changes to the
same query, that can't be good, can it?

I apologize for taking your time on this. I think this one must be way
beyond my capabilities. It seemed like it should be relatively easy when I
started.

:

Using the generic multiselect list box function should allow you to create
your subreport record source without modifying any SQL properties.

To change the SQL property of a saved query, you would need to do this prior
to opening your report with subreports. The DAO code might look something
like:

CurrentDb.QueryDefs("qselYourName").SQL = "SELECT ... FROM .... WHERE ..."

--
Duane Hookom
Microsoft Access MVP


:

Duane,

Unfortunately, I'm far too unskilled a user to understand what to do from
your post. I mean I do know how to modify the SQL property of the record
source of the subreports, but I have no idea how to do it dynamically (like
from a form that opens a report). Is it done with the cool listbox function
you linked to? If so, can you give me a nudge on how to start?

Many thanks!

:

You won't be able to use that code as you have determined. You can modify the
SQL property of the record source of the subreports. Or, there is a generic
listbox function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane which
could be used in the record source of the subreports.

--
Duane Hookom
Microsoft Access MVP


:

I built a calendar based on one of Duane Hookom's calendar samples:
http://www.access.hookom.net/Samples.htm

Now I'm trying to make my calendar dynamic using techniques from Martin
Green's website:
http://fontstuff.com/access/acctut19.htm

My calendar report has 4 subreports on it. The "Multi-Select List Box" form
I created for filtering works on each of the subreports individually, but
when I switch it to the main report, I get a "Enter Parameter Value" box for
"CS Person." I suspect this is because the field "CS Person" doesn't actually
appear on the main report, but I don't know how to work around this.

Any suggestions would be appreciated. The code for the "apply filter" button
on my form is below in case that helps.

Many thanks,
jkatj



Private Sub ApplyButton_Click()
Dim varItem As Variant
Dim strPerson As String
Dim strFilter As String
' Open report
DoCmd.OpenReport "Calendar - Landscape", acViewPreview
' Build criteria string from [CS Person box] listbox
For Each varItem In Me.CS_Person_box.ItemsSelected
strPerson = strPerson & ",'" & Me.CS_Person_box.ItemData(varItem) _
& "'"
Next varItem
If Len(strPerson) = 0 Then
strPerson = "Like '*'"
Else
strPerson = Right(strPerson, Len(strPerson) - 1)
strPerson = "IN('-Office Closed-'," & strPerson & ")"
End If
' Build filter string
strFilter = "[CS Person] " & strPerson
' Apply the filter and switch it on
With Reports![Calendar - Landscape]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top