Querying a Query (1 Viewer)

[gary.newport]

I have a piece of code that runs a series of update and append queries. However, there are only a few times that these queries need to run and this is based upon another, earlier set of queries (created to inform the user of what is happening).

I would like to create an IF statement that simply looks at the returning record count of the first query and then selects to run the update or append or not.

If Queries.qryIMPORTNewTGs.RecordCount = 0 Then
        MsgBox "Empty"
    Else
        MsgBox Queries.qryIMPORTNewTGs.RecordCount
    End If
    Exit Function

However, I get an Object Required error message and therefore assume I have the structure VERY wrong.

Any help gratefully received (obviously the above code is test code and not the full system).

 

[DCrake]

Easier way

Create a simple function in a standard module

Public Function HasRecords(AnyQuery As String) As Long


Dim Rs As DAO.Recordset
Dim rCnt As Long
      rCnt = 0
Set Rs = CurrentDb.OpenRecordset(AnyQuery)

If Not Rs.EOF And Not RS.BOF Then
   Rs.MoveLast
   rCnt = Rs.RecordCount   
   Rs.Close
End If

HasRecords = rCnt

Set Rs = Nothing

End Function

Then in your form

Dim rCnt As Long

rCnt = HasRecords("qryIMPORTNewTGs")

If rCnt = 0 Then
   MsgBox "No Records found"
Else
   MsgBox rCnt & " record(s) found"
End If

 

[gary.newport]

I do feel we need to discuss the definition of the word 'easier'.

Thank you ever so much. This makes absolute sense and gives me the extra benefit of using the same function later - because I already know I need to.