By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,603 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

Need some Help

P: 4
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
  2.  
  3. 'The Name of the Button on the Form that sends the data
  4. Private Sub Command10_Click()
  5.  
  6. Dim Filter As String
  7.  
  8. Filter = ""
  9.  
  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
  14.  
  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
  20.  
  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
  25.  
  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
  32.  
  33.  
  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
  41.  
  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
  44.  
  45. 'DoCmd.OpenReport "Dayville Review Subreport", acViewPreview, , Filter
  46. DoCmd.OpenReport Me.cboReport.Column(2), acViewPreview, , Filter
  47.  
  48. Exit Sub
  49. End If
  50.  
  51. DoCmd.OpenReport Me.cboReport.Column(2), acViewPreview, , Filter
  52.  
  53. Debug.Print Filter
  54. End Sub
  55.  
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
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

P: 4
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

P: 4
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

P: 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.