Reports requery

[opiv6ix]

Hi all,
I'm working on a database for a company where there are two styles of paychecks based on jobs done during pay periods. Basically, the user opens a form, and picks the paydate, then the user opens either of the paycheck reports (both based on the same query). The underlying query has the restriction WHERE [PayDate] = Forms!PayDate_Selection![cbo_PayDate] (rough idea). Anyway, my problem is this: I can't figure out how to refresh these paycheck reports when the combo box selection is changed. I thought it would be simple like, as an OnChange, like Queries!PayChecks.Requery or for the individual reports, like Reports!DriversPay.Requery. Either way, the debugger tells me that I spelled the report/query wrong, or it doesn't exist. Both are untrue. Is there a simple way to do this?

Thanks,
6ix

 

[Wiz47]

You can requery the control on the AfterUpdate Event ...

Me.MyControlName.requery

 

[opiv6ix]

I appreciate that, it's solid advice, but I don't need to refresh anything on the form. The form is one combo box - that's all, and it doesn't need refreshed. I have a query that uses the record chosen by this unbound combo box to place a restriction on the records that are returned. When the combo box is changed (AfterUpdate), I need the query/report to requery/refresh.

 

[Wiz47]

I appreciate that, it's solid advice, but I don't need to refresh anything on the form. The form is one combo box - that's all, and it doesn't need refreshed. I have a query that uses the record chosen by this unbound combo box to place a restriction on the records that are returned. When the combo box is changed (AfterUpdate), I need the query/report to requery/refresh.

Private Sub Report_Open(Cancel As Integer)
    On Error Resume Next
    Set frm = Forms!YourFormName
    If Err.Number = 0 Then
        With frm
            If !YourComboBoxName = "YourSelectionName1" Then
                Me.RecordSource = "Query1"
            ElseIf !YourComboBoxName = "YourSelectionName2" Then
                Me.RecordSource = "Query2"
End If
        End With
        Set frm = Nothing
    End If
End Sub

I would write separate querys depending on the choice and call them like this. If you have a record source listed in the report properties - remove it, and put this in the ON OPEN event of the report (Change the fields and names to yours). When the report opens, it will use the proper query.

Just a reminder, these changes are made in the report, not the form.

 

[opiv6ix]

It's not feasible to write separate queries for each selection, as the selections are the dates of every Friday for the year (paydays). Further, if I make the selection, then open the report, it uses the right criteria for the query. The problem is, when I change the criteria, I have to close, then reopen the report to show the information for the new date. I want the report to refresh with the new payday criteria when the change is made to the combo box.

Private Sub Combo_Change()
Reports![PayChecks].Requery ?
End Sub

Problem with this code is that it yields a runtime error '2465' - "Application-defined or object-defined error."

What code would yield the proper result for an OnChange or OnUpdate Event Procedure?

 

[boblarson]

Just a suggestion, as I haven't found a way yet to change a report's recordsource (by requerying) while it is open. This may not be optimal, but about the only way I know of -

Private Sub Combo_Change()
DoCmd.Close acReport, "PayChecks", acSaveNo
Docmd.Open acReport, "PayChecks"
End Sub