Solved Help with recordset

[bwc]

The Debug.Print shows the results I want to see. Lots of True and False

Public Function chkQry()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM reportQ")
        While Not rs.EOF
            Debug.Print rs!Clinician = " & openRptCbo & " And rs!dateEntered = TempVars("enterDate").Value And rs!isPaid = True
            rs.MoveNext
        Wend
    rs.Close
    Set rs = Nothing
End Function

How do I send it to a OnClick event to filter a report? The report is bound to the query in the recordset

Private Sub indPaidRptBtn_Click()
    DoCmd.OpenReport "pbR", acViewReport, , chkQry = True
End Sub

Rather then the getting a filtered report, I get a blank report

 

[arnelgp]

the function chkQry() doesn't do/return anything.
try this:

Private Sub indPaidRptBtn_Click()
    DoCmd.OpenReport "pbR", acViewReport, , chkQry = True
DoCmd.OpenReport ReportName:="pbR", View:=acViewReport, _
     WhereCondition:="Clinician = " & openRptCbo & " And " & _
      "dateEntered = #" & Format$(TempVars("enterDate").Value, "mm/dd/yyyy") & "# And isPaid = True"
End Sub

 

[bwc]

the function chkQry() doesn't do/return anything.
try this:

Private Sub indPaidRptBtn_Click()
    DoCmd.OpenReport "pbR", acViewReport, , chkQry = True
DoCmd.OpenReport ReportName:="pbR", View:=acViewReport, _
     WhereCondition:="Clinician = " & openRptCbo & " And " & _
      "dateEntered = #" & Format$(TempVars("enterDate").Value, "mm/dd/yyyy") & "# And isPaid = True"
End Sub

thank you for your reply. I do have something similar to that. I want to put it in a function because I find myself duplicating code...

Private Sub indPaidRptBtn_Click()
    DoCmd.OpenReport "pbR", acViewReport, , _
        "Clinician=""" & openRptCbo & """ and [isPaid]= True and [dateEntered]=#" & TempVars("enterDate").Value & "#"
End Sub

Private Sub indUnPaidRptBtn_Click()
    DoCmd.OpenReport "pbR", acViewReport, , _
        "Clinician=""" & openRptCbo & """ and [isPaid]= False and [dateEntered]=#" & TempVars("enterDate").Value & "#"
End Sub

 

[theDBguy]

You could try something like:

Private Sub indPaidRptBtn_Click()
OpenMyReport True
End Sub

Private Sub indUnPaidRptBtn_Click()
OpenMyReport False
End Sub

Private Sub OpenMyReport(Paid As Boolean)
DoCmd.OpenReport "pbR", acViewReport, , "Clinician=""" & openRptCbo & """ and [isPaid]=" & Paid & " and [dateEntered]=#" & TempVars("enterDate").Value & "#"
End Sub

(untested)
Sent from phone...

 

[bwc]

You could try something like:

Private Sub indPaidRptBtn_Click()
OpenMyReport True
End Sub

Private Sub indUnPaidRptBtn_Click()
OpenMyReport False
End Sub

Private Sub OpenMyReport(Paid As Boolean)
DoCmd.OpenReport "pbR", acViewReport, , "Clinician=""" & openRptCbo & """ and [isPaid]=" & Paid & " and [dateEntered]=#" & TempVars("enterDate").Value & "#"
End Sub

(untested)
Sent from phone...

Thank you. That worked

 

[theDBguy]

Thank you. That worked

You're welcome. Good luck with your project.

 

[Pat Hartman]

Use a variable name for the report. I use a combo or listbox rather than individual buttons so the report name is always part of the rowSource for the list so there is no hardcoding involved. Always use tables to control stuff like this whenever you can. I also use a variable for the target so there are several case statements. But it allows a coherent interface that supports multiple reports with potentially different arguments and options to print, export to .pdf, preview and even in some cases, export to Excel.

Here is an example that is probably more complicated than you need but it also shows the details of how you can run multiple reports using generic code.

Manage Reports form

This sample just can't be imported and used, it requires customization. There is a table behind the scenes which documents the reports and also identifies each report's arguments and whether they are required or optional. I've used this concept in at least a dozen applications and given that...

www.access-programmers.co.uk