Pass Parameters from a Date Range Form to a Report

The report and its source query can access the textbox controls on the open form

Dale, aikimark thank you.
Dale, I think you are closer to what I am after.  I do like your #3 option.  Its an approach I have been wanting to implement for quite some time. Putting the pieces together would be an issue for me.  I think #1 is something I can handle a little better and will probably then need multiple forms i/o of one form like in your #3 option.  

I want to ask a question about #3 option (using the form for multiple reports)
There are 2 buttons:  one for Cancel and one for Continue
"Continue" seems to be Open Report to me.   Is that true?

I think the code below sets the Cancel argument of the Form object to True and exits the subroutine (which closes the report).

Private Sub Report_Open(Cancel As Integer)

      If CurrentProject.AllForms("frmDateRange").IsLoaded = False Then
        DoCmd.OpenForm "frmDateRange"
        MsgBox "Enter the appropriate fields, then click the report button"
        Cancel = True
    End If
   
End Sub

Gustav, didnt refresh before submitting.... let me look over.

Gustav, that can be applied to multiple reports meaning that i have one frmDateRange and use the reports OnOpen event to check if the frmDateRange is open?  I believe, if true, then it would be similar to Dale's #3.   Not sure if I am following.  Grateful for your response and correct me if I am mistaken.

Yes, if you have the form open, you can have a button to call the two functions and open any selected report as long as you adjust the criteria for the query you use as source for the report.

Also, you can have on form where you set the dates, and another where you open the report(s).

The thing I like is, that you isolate the names of your form and controls from the SQL of the queries for the reports.

/gustav

Ok I think I an following but have a followup on this :
"Also, you can have on form where you set the dates, and another where you open the report(s)."
==>i would need a button for each report, correct?

That's up to you.
If you have many reports, you could list them in a list- or combox, select one, and click the button.

/gustav

Ok.  I will try to follow that.  I am going to be away from the computer for awhile though and might not get back until tomorrow.  

Any other comments are welcome.

pdvsa,

In my option #3, both buttons change the Tag property of the form, then simply hide the form.

Me.visible = false

Because you use the acDialog window mode to open the form from the Report_Open event, the code in the open event pauses until the form is closed or hidden.  I generally just hide it so that I can retrieve the value of the tag property from within the Report_Open event, something like:

    Docmd.openform "MyReportParameters",,,, acdialog   'check the # of commas
    if forms("myReportParameters").Tag = "Cancel" then
        Cancel = True
        Exit Sub
    Else
        Cancel = False   'not really needed because it is the default
        'some code here to define a filter for the report
    End if
    docmd.close acform, "MyReportParameters"

Select allOpen in new window

Gustav: would I place the OpenReport command like below?  

Private Sub Command17_Click()

    Call ReportDateFrom(Nz(Me!txtdatefrom.Value, Date))
    Call ReportDateTo(Nz(Me!txtDateTo.Value, Date))
    DoCmd.OpenReport "rptDraws_All", acViewReport, , , acDialog
    '  need to adjust query criteria to: Where [YourReportDateField] Between ReportDateFrom() And ReportDateTo()
End Sub

Select allOpen in new window

Does this look correct:
I have placed both functions in my Module "Global" functions part of db.
ReportDateTo:

Public Function ReportDateTo(Optional ByVal Value As Date) As Date
'Gustav (cactusdata):  9/13/16
    
    Static LastDate As Date

    If Value = #12:00:00 AM# Then
        ' Read date.
        If LastDate = #12:00:00 AM# Then
            LastDate = Date
        End If
    Else
        ' Store date.
        LastDate = Value
    End If

    ReportDateTo = Value

End Function

Select allOpen in new window

Nearly. I made a mistake: The last line in both functions should read:

    ReportDateTo = LastDate

/gustav

Thank you Dale and Gustav.  I am a bit more familiar with Gustav's solution and I have successfully implemented it.   Grateful to both.

You are welcome!

/gustav

I am going to ask a follow up on this though.   I need functionality to open the report and show all records if the txtDateFrom is Null.

You could adjust the call:

    Call ReportDateFrom(Nz(Me!txtDateFrom.Value, #1/1/100#))

/gustav

Nice.  Worked perfectly.  I was about to press the send button on the follow up question.  Thank you sir... have a good day!

Thanks!

/gustav

Gustav, sorry to pester you about this.

It seems that if the date ranges are NULL on the date range form, the report doesnt include all records (only some).  
In the below, the first line was modified based on your previous suggestion above, to show ALL records if the txtDateFrom was NULL.  
I thought that maybe I needed the issue was that I needed to modify the line below with #1/1/100# as well (the txtDateTo line) but that returned no records.  
Maybe you know what exactly the issue is.  
when you have a sec... appreciate as always.
Just wondering if you see something obvious to the issue and I am not wishing much more than 1 minute of your time because I can live without this fix.  

button on the frmDateRange:

Private Sub cmdFuturePast_Click()

    Call ReportDateFrom(Nz(Me!txtdatefrom.Value, #1/1/100#)) 
    Call ReportDateTo(Nz(Me!txtDateTo.Value, Date))
    DoCmd.OpenReport "rptFuture_Past", acViewReport, , , acDialog

End Sub

Select allOpen in new window

Also, possibly you see some issue in the report control source:

SELECT tblRepayment.ValueDate, tblDraws_Details1.ID, tblDraws_Details1.BankID, tblDraws_Details1.ID_facility, tblRepayment.PaymentMadeYN, tblRepayment.Amount
FROM tblDraws_Details1 INNER JOIN (tblRepayment LEFT JOIN tblCurrencyExchange ON tblRepayment.Currency = tblCurrencyExchange.CurrencyID) ON tblDraws_Details1.ID = tblRepayment.DrawIDrpmt
WHERE (((tblRepayment.ValueDate) Between ReportDateFrom() And ReportDateTo()))
ORDER BY tblRepayment.ValueDate, tblRepayment.PaymentMadeYN;

Select allOpen in new window

If you have future dates, you could modify as follows:

    Call ReportDateTo(Nz(Me!txtDateTo.Value, #12/31/9999#))

However, if ValueDate can be Null, those records will always be excluded.
Or you could modify to:

    WHERE ((Nz(tblRepayment.ValueDate,Date()) Between ReportDateFrom() And ReportDateTo()))

/gustav

Gustav, that was exactly it.  The future dates were being excluded.  I used the first option.  Thank you once again...very grateful.

Great. Step by step ...

/gustav