Solved Open main and subreport to date on form (1 Viewer)

foshizzle

Registered User.
Local time
Today, 05:27
Joined
Nov 27, 2013
Messages
277
I've been using Allen Browne's Method 2: Form for entering the dates on my reports. This works fine. I have a new report with multiple subreports. Is it somehow possible to link them all to the same date entered on the one (single) form?

This is the form code to accept the date and open the main report, based on the described method above.
My subreports are as follows; each have the required textboxes in the report header p/ Allen's instructions:
rptSum1MeterReadings
rptSum2AirlineTransactions
rptSum3TruckVolume
rptSum4TankVolume


Code:
 ' Set focus on another control allowing the textboxes to update
 Me.txtStartDate.SetFocus
 Me.txtEndDate.SetFocus

'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
    
    'DO set the values in the next 3 lines.
    strReport = "rptSum"      'Put your report name in these quotes.
    strDateField = "[TransactionDate]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If
    
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    
    ' Close form builder
     DoCmd.Close acForm, "frmReportBuildSum", acSaveNo
    
    ' Open report
    DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:27
Joined
Oct 29, 2018
Messages
21,357
Are you saying your subreports are not linked to your main report? If so, does it mean you have an unbound main report?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:27
Joined
May 7, 2009
Messages
19,169
make sure that you don't close the Form where you are referring the date from.
on each report/subreport, reference to form and it's date textbox/label:

on unbound textbox of report/subreport, use this controlSource:

=[Forms]!theFormName!theDateTextboxName
 

foshizzle

Registered User.
Local time
Today, 05:27
Joined
Nov 27, 2013
Messages
277
Are you saying your subreports are not linked to your main report? If so, does it mean you have an unbound main report?
Correct, the main report is unbound; as i am referencing the subreports on the main report for additional calculations.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:27
Joined
Oct 29, 2018
Messages
21,357
Correct, the main report is unbound; as i am referencing the subreports on the main report for additional calculations.
So, if you want to filter all subreports using the same criteria, maybe the easiest way is to put an unbound textbox on the main report that reference the form and use it to link all subreports to the main report.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:27
Joined
May 7, 2009
Messages
19,169
where does the date of the reports/subreports come from?
from frmReportBuildSum?
there is a code that is closing it before Opening your Report.

you could just make it Invisible:

Me.Visible = False

and then close it on the Close Event of your Report:

Private Report_Close()
Docmd.Close acForm, "frmReportBuildSum"
End Sub
 

foshizzle

Registered User.
Local time
Today, 05:27
Joined
Nov 27, 2013
Messages
277
*EDIT* attaching DB here

I think I found the problem of why it wasnt updating all subreports: Since I am using Allen Browne's Method 2, I had to add the subreports to the code.

So now, I have:
controls on each subreport with =[Forms]!frmReportBuildSum!txtStartDate and =[Forms]!frmReportBuildSum!txtEndDate.
hidden the frmReportBuildSum using Me.Visible = False
and the updated code below however the problem continues.

Code:
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField1 As String
    Dim strDateField2 As String
    Dim strDateField3 As String
    Dim strDateField4 As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
   
    'DO set the values in the next 3 lines.
    strReport = "rptSum"      'Put your report name in these quotes.
    strDateField1 = "[MeterDate]" 'Put your field name in the square brackets in these quotes.
    strDateField2 = "[TransactionDate]" 'Put your field name in the square brackets in these quotes.
    strDateField3 = "[TruckVolumeDate]" 'Put your field name in the square brackets in these quotes.
    strDateField4 = "[TankVolumeDate]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
   
     'Build the filter string1.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField1 & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField1 & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

         'Build the filter string2.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField2 & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField2 & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

         'Build the filter string3.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField3 & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField3 & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

         'Build the filter string4.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField4 & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField4 & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

   
    'Close the report1 if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If

    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
   
    ' Close form builder
     'DoCmd.Close acForm, "frmReportBuildSum", acSaveNo
     Me.Visible = False
   
    ' Open report
    DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
 

Attachments

  • Database15.accdb
    2.6 MB · Views: 548
Last edited:

foshizzle

Registered User.
Local time
Today, 05:27
Joined
Nov 27, 2013
Messages
277
*EDIT* attaching DB here

I think I found the problem of why it wasnt updating all subreports: Since I am using Allen Browne's Method 2, I had to add the subreports to the code.

So now, I have:
controls on each subreport with =[Forms]!frmReportBuildSum!txtStartDate and =[Forms]!frmReportBuildSum!txtEndDate.
hidden the frmReportBuildSum using Me.Visible = False
and the updated code below however the problem continues.

Code:
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: http://allenbrowne.com/casu-08.html
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strDateField1 As String
    Dim strDateField2 As String
    Dim strDateField3 As String
    Dim strDateField4 As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Do NOT change it to match your local settings.
  
    'DO set the values in the next 3 lines.
    strReport = "rptSum"      'Put your report name in these quotes.
    strDateField1 = "[MeterDate]" 'Put your field name in the square brackets in these quotes.
    strDateField2 = "[TransactionDate]" 'Put your field name in the square brackets in these quotes.
    strDateField3 = "[TruckVolumeDate]" 'Put your field name in the square brackets in these quotes.
    strDateField4 = "[TankVolumeDate]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
  
     'Build the filter string1.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField1 & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField1 & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

         'Build the filter string2.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField2 & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField2 & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

         'Build the filter string3.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField3 & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField3 & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

         'Build the filter string4.
    If IsDate(Me.txtStartDate) Then
        strWhere = "(" & strDateField4 & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
    End If
    If IsDate(Me.txtEndDate) Then
        If strWhere <> vbNullString Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "(" & strDateField4 & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    End If

  
    'Close the report1 if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If

    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
  
    ' Close form builder
     'DoCmd.Close acForm, "frmReportBuildSum", acSaveNo
     Me.Visible = False
  
    ' Open report
    DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
    Exit Sub

Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler

I'm noticing the controls in the subreport for =[Forms]!frmReportBuildSum!txtStartDate and =[Forms]!frmReportBuildSum!txtEndDate are correct but it is not filtering the data in the subreport
 

foshizzle

Registered User.
Local time
Today, 05:27
Joined
Nov 27, 2013
Messages
277
I had to go another route and use each subreport's query for filtering the date. I did however find the information in here useful. Thanks for the tips guys!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:27
Joined
Oct 29, 2018
Messages
21,357
I had to go another route and use each subreport's query for filtering the date. I did however find the information in here useful. Thanks for the tips guys!
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom