Custom Function: how to add Where Clause

[PeakH]

I have a function that looks up if a query has any records. I use this function in an onClick event so that if there are records it will open a report, and if not it will open a form to add records. This worked great when I had criteria in the query itself, but that created some problems so I got rid of the query criteria and put that criteria in the onClick event. Now the query always has records, so it will always open the report even if the report has no records with the specific criteria.



I think a way for me to fix this is to add a WhereCondition into the function itself. The problem being that I have no idea how to do this.
I tried looking up the VBA code for the OpenForm/Report functions, since they have where conditions, thinking I could copy some code from it, but I have had no luck.

Function fcnRptHasRecs(qQuery As String) As Boolean
'Checks if there are any records
    Dim r As DAO.Recordset


    fcnRptHasRecs = False
    
    Set r = CurrentDb.OpenRecordset(qQuery)
    If Not r.BOF And Not r.EOF Then
        r.MoveLast
        r.MoveFirst
        fcnRptHasRecs = (r.RecordCount > 0)
    End If
    r.Close


    'clean up
    Set r = Nothing
End Function

[pbaldy]

Something like:

Set r = CurrentDb.OpenRecordset("SELECT * FROM " & qQuery & " WHERE YourCriteriaHere")

[PeakH]

This is perfect!

Thank you so much!

[pbaldy]

Happy to help!