If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
filtering works for subreports but not main report
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 |
#2
|
|||
|
|||
filtering works for subreports but not main report
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/O...p#Hookom,Duane which could be used in the record source of the subreports. -- Duane Hookom Microsoft Access MVP "jkatj" wrote: 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 |
#3
|
|||
|
|||
filtering works for subreports but not main report
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" wrote: 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/O...p#Hookom,Duane which could be used in the record source of the subreports. -- Duane Hookom Microsoft Access MVP "jkatj" wrote: 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 |
#4
|
|||
|
|||
filtering works for subreports but not main report
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" wrote: 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" wrote: 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/O...p#Hookom,Duane which could be used in the record source of the subreports. -- Duane Hookom Microsoft Access MVP "jkatj" wrote: 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 |
#5
|
|||
|
|||
filtering works for subreports but not main report
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" wrote: 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" wrote: 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" wrote: 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/O...p#Hookom,Duane which could be used in the record source of the subreports. -- Duane Hookom Microsoft Access MVP "jkatj" wrote: 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 |
#6
|
|||
|
|||
filtering works for subreports but not main report
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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/O...p#Hookom,Duane which could be used in the record source of the subreports. -- Duane Hookom Microsoft Access MVP "jkatj" wrote: 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 |
#7
|
|||
|
|||
filtering works for subreports but not main report
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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/O...p#Hookom,Duane which could be used in the record source of the subreports. -- Duane Hookom Microsoft Access MVP "jkatj" wrote: 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 |
#8
|
|||
|
|||
filtering works for subreports but not main report
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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/O...p#Hookom,Duane which could be used in the record source of the subreports. -- Duane Hookom Microsoft Access MVP "jkatj" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|