Passing parameters to query and report

[AnnMV8]

I have a button on a form that passes parameters to a query and opens a report. The code is listed below (someone helped me with this a long time ago and I kept it and reuse it). This works without a problem but what I need to do is also pass [dtmStartDate] and [dtmEndDate] which both get passed to [dtmSessionDate] in the query to the report that is opened.

Since both fields are being passed to the field [dtmSessionDate] in the query I don’t know what I need on my report or in the code to make it show up like the following: “From Period: [dtmStartDate] To Period: [dtmEndDate] on my report.

Can anyone help me? Thanks in advance.

Private Sub cmdApplyFilter_Click()

    Dim strFilter As String
    Dim dtmStartDate As Date
    Dim dtmEndDate As Date
        
        'Location
    If Not IsNull(Me.cboLocation) Then
        strFilter = strFilter & " AND txtCity=""" & Me.cboLocation & """ "
    End If
    
        'Supervisor Name
    If Not IsNull(Me.cboSupervisorName) Then
        strFilter = strFilter & " AND txtSupervisorName=""" & Me.cboSupervisorName & """ "
    End If

        'Begin and End Dates
    If Not (IsNull(Me.dtmStartDate) Or Me.dtmStartDate = "") Then
    strFilter = strFilter & " AND (dtmSessionDate) Between #" & Format(Me.dtmStartDate, "mm/dd/yyyy") & "# AND #" & Format(Me.dtmEndDate, "mm/dd/yyyy") & "#"
    End If
 
        'If the report is closed, open the report
    If SysCmd(acSysCmdGetObjectState, acReport, "rptCallDetailsForAllAgentsBySupervisor") <> acObjStateOpen Then
        DoCmd.OpenReport "rptCallDetailsForAllAgentsBySupervisor", acPreview
    End If

        'if report was open, use filter
    With Reports![rptCallDetailsForAllAgentsBySupervisor]
        .Filter = Mid(strFilter, 6)
        .FilterOn = True
    End With

End Sub

[Seth Schrock]

There is probably a cleaner way to do it, but this is what I would do:

Put a textbox on the report that will hold your From Period: [dtmStartDate] To Period: [dtmEndDate] and name it txtDatePeriod. Now put the following line of code in both the

'If the report is closed, open the report

section and the

'if report was open, use filter

section:

Me.txtDatePeriod = "From Period: " & dtmStartDate _
                & " to Period: " & dtmEndDate

So your last lines of code will look like this:

'If the report is closed, open the report
If SysCmd(acSysCmdGetObjectState, acReport, "rptCallDetailsForAllAgentsBySupervisor") <> acObjStateOpen Then
DoCmd.OpenReport "rptCallDetailsForAllAgentsBySupervisor", acPreview
Me.txtDatePeriod = "From Period: " & dtmStartDate _
                & " to Period: " & dtmEndDate
End If

'if report was open, use filter
With Reports![rptCallDetailsForAllAgentsBySupervisor]
.Filter = Mid(strFilter, 6)
.FilterOn = True
Me.txtDatePeriod = "From Period: " & dtmStartDate _
                & " to Period: " & dtmEndDate
End With

[AnnMV8]

Thank you for helping me. I did what you said but am receiving a compile error on txtDatePeriod. Method or data member not found.

Here is the changed code.

        'If the report is closed, open the report
    If SysCmd(acSysCmdGetObjectState, acReport, "rptCallDetailsForAllAgentsBySupervisor") <> acObjStateOpen Then
        DoCmd.OpenReport "rptCallDetailsForAllAgentsBySupervisor", acPreview
        Me.txtDatePeriod = "From Period: " & dtmStartDate _
                & " to Period: " & dtmEndDate
    End If
    
        'if report was open, use filter
    With Reports![rptCallDetailsForAllAgentsBySupervisor]
        .Filter = Mid(strFilter, 6)
        .FilterOn = True
        Me.txtDatePeriod = "From Period: " & dtmStartDate _
                & " to Period: " & dtmEndDate
    End With

[Rabbit]

Please use code tags when posting code. This is your second warning.

Seth's code is only an example of how to do it. It will only work if you have a control on your form named txtDatePeriod to store it. From the error message, it is clear that you don't have said control.

[Seth Schrock]

(don't forget to use code tags when posting code. It makes it much easier to read.)

Okay. I thought that it would work since the report had already been opened in previous code. What you need to do now is to create a standard module (if using Access 2007 or 2010, go to the create tab and select Module from the far right group. Other versions of access I don't know where to go to create a module). In the Module, type the following code after the Option Compare Database line

Option Explicit
Public strDatePeriod As String

Change the Me.txtDatePerio d in my code to be strDatePeriod so that it looks like this in the form:

strDatePeriod = "From Period: " & dtmStartDate _
& " to Period: " & dtmEndDate

You have now declared a public variable and assigned it the value that you want to be put into the textbox txtDatePeriod. Now we need to assign the value of strDatePeriod to txtDatePeriod. Create an On_Load event for your report. Add the following code to the event:

Me.txtDatePeriod = strDatePeriod

This should work. I don't know if there is a more efficient way to do it, but I just used a similar process to create a title for a report that I'm designing and it worked for me. If an expert would like to suggest a better way, I would love to hear it so that I can improve my database as well.

[AnnMV8]

It still didn't work. I'm not receiving an error but the text box is empty. The text box is an Unbound text box named txtDatePeriod and there isn't a Control Source. Did I do that wrong? Is there a way I can attache my database?

This makes sense I just don't know what I've done wrong.

Sorry about the tags.

[Seth Schrock]

If you've double checked everything and it still isn't working, I don't know what is wrong. You should be able to compress the file and then attach it here and I will look to see what is missing. I just don't know where to tell you to look.

[AnnMV8]

Database

I have attached the database in a zip file. There are many objects but the ones I am using are:

frmCallDetailsF orAllAgentsBySu pervisor - this is where the parameters are entered and passed to the query when the Apply Filter button is clicked. Where I also am adding the code to pass to the report. I choice I use from the two dropdowns are Toledo and Andrea Lipinski. The Start and End dates are 01/01/2012 and 05/01/2012.

rptCallDetailsF orAllAgentsBySu pervisor - this is where I have added the unbound text box called txtDatePeriod. Where I want to pass the period end and start dates.

The module is still called Module1.

Thank you so much for doing this.

[Seth Schrock]

Well, I just deleted the On_Load event and recreated it and it worked. However, I don't think that you want the results to be like this:

From Period: 12:00:00 AM to Period: 12:00:00 AM

I've tried using the Format(Me.dtmSt artDate,"mm/dd/yyyy"), but that isn't working. I'll keep trying.

Update: I'm getting intermittent success on the txtDatePeriod getting populated. I'm working on a solution that will work always.

[Seth Schrock]

Okay. I think that I have found a solution for the txtDatePeriod problem. Instead of putting

Me.txtDatePeriod = strDatePeriod

in the On_Load event of the report, put it in the On_Print event of the Header. I think that should work 100% of the time.

Back to working on the date formatting...

[AnnMV8]

I made the change to the header and it worked great. It was the time instead of the date but I'm so glad it showed something. Thank you!!

[Seth Schrock]

I just fixed the date/time problem. In your original post, delete lines 4 and 5. What is happening is that Access is having trouble deciphering between the variable dtmStartDate and the control name dtmStartDate. Never name variables the same as a field name or a control name and you won't have this happen. It also makes it easier for you to know which you are dealing with when you can look at a line of code and know if what you are looking at is a variable, field name, or control name.

[AnnMV8]

It worked!! Thank you so much for all of your help, you did so much. I didn't realize that was wrong, so thank you for teaching me too.

For others looking at this it was all helpful, not just the one I marked as the best answer.