467,101 Members | 1,155 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,101 developers. It's quick & easy.

Need some Help

Ok I got a set of Forms Reports etc it goes like this
Reports Form Passes a Filter to the report for the needed results
The filter it passes along is fine except one problem
I need a way for the subreport to also be filtered by the same data

The main report name is Dayville Review Report and the subreport is Dayville Review subreport

Now all the info from the Form is passed into the vba environment for double checking when I was just using the main report its filters fine it still does for that I just can't get the subreport to filter I know I'm missing something
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  3. 'The Name of the Button on the Form that sends the data
  4. Private Sub Command10_Click()
  6. Dim Filter As String
  8. Filter = ""
  10. 'Add date if only start date is filled in
  11. If IsNull(Me.txtEnd) And Not IsNull(Me.txtStart) Then
  12.     Me.txtEnd = Me.txtStart
  13.     End If
  15. 'check if End Date > Start date
  16. If Me.txtEnd < Me.txtStart Then
  17.     MsgBox "Make sure your End Date is AFTER your Start Date", vbCritical, "Invalid Entry!"
  18.     Exit Sub
  19.     End If
  21. 'Checks if the start date for the report is null or not
  22. If Not IsNull(Me.txtStart) Then
  23.     Filter = "[Date] between #" & Me.txtStart & "# AND #" & Me.txtEnd & "#"
  24.     End If
  26. If Not IsNull(Me.txtBakco) Then
  27.          If Filter <> "" Then
  28.             Filter = Filter & " AND "
  29.         End If
  30.         Filter = Filter & "[Bakco Number] = " & Me.txtBakco
  31.     End If
  34. 'Check what areas the user wants the info for and adds it to the filter
  35. If Me.cboArea <> "All Areas" Then
  36.         If Filter <> "" Then
  37.             Filter = Filter & " AND "
  38.         End If
  39.     Filter = Filter & "[Area] = '" & Me.cboArea & "'"
  40.     End If
  42. 'This is the part where it detect if its the report with the subreport in this case Dayville Review Report is report 0 in the index. the Subreport isn't in the index wasn't sure if that would help.
  43. If Me.cboReport = 0 Then
  45. 'DoCmd.OpenReport "Dayville Review Subreport", acViewPreview, , Filter
  46. DoCmd.OpenReport Me.cboReport.Column(2), acViewPreview, , Filter
  48. Exit Sub
  49. End If
  51. DoCmd.OpenReport Me.cboReport.Column(2), acViewPreview, , Filter
  53. Debug.Print Filter
  54. End Sub
Most of this wasn't written by me I am just trying to work with it.

Also one option I guess would be to somehow change the query the subreport originally pulled its data from in this case that is called Dayville Accuracy Query
The current sql view of that looks like this

Expand|Select|Wrap|Line Numbers
  1. SELECT [Dayville Accuracy].Name, [Dayville Accuracy].Area, 
  2. [Dayville Accuracy].[Bakco Number], [Dayville Accuracy].Date, 
  3. [Dayville Accuracy].[Total Checked], [Dayville Accuracy].[Miss-Pick], 
  4. [Dayville Accuracy].BNR, [Dayville Accuracy].Over,
  5.  [Dayville Accuracy].Damage
The Bakco number field is the link between the two reports.

Thank you for your time
Apr 20 '07 #1
  • viewed: 1338
4 Replies
Expert Mod 8TB
Your subreport needs to be tied to your main report. Look at the master child relationship or see if the subreport has a permanent filter attached.
Apr 21 '07 #2
Ok I'm still fairly new to access how would I apply the kind of filter I would need to it
Apr 21 '07 #3
As far as the parent child relationship goes I meant to say the Bakco Number was setup as such. That is why the report works fine if I limit the 2 queries by hand that the 2 reports draw from
Apr 21 '07 #4
After a lot of searching around and referencing various books I found my solution

I copied the Accuracy query to one called Review Query and make these changes.
Dayville Review Query
Criteria for Bakco Number = [Forms]![ReportForm]![txtBakco]
Criteria for Data= Between [Forms]![ReportForm]![txtStart] And [Forms]![ReportForm]![txtEnd]

Then I changed the source for the subreport to this
Dayville Review subreport
SELECT [Dayville Review Query].Name, [Dayville Review Query].[Bakco Number], [Dayville Review Query].[Total Checked], [Dayville Review Query].[Miss-Pick], [Dayville Review Query].BNR, [Dayville Review Query].Over, [Dayville Review Query].Damage FROM [Dayville Review Query];

Now when I enter the data into the form it correctly fitlers the right information
Apr 22 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by mike | last post: by
5 posts views Thread by John Flynn | last post: by
reply views Thread by xunling | last post: by
7 posts views Thread by Timothy Shih | last post: by
8 posts views Thread by skumar434@gmail.com | last post: by
reply views Thread by U S Contractors Offering Service A Non-profit | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.