Did a query return a record

[Boo]

I want to run a query and do something only if it returns a record.

so far:

If <the query returned a record> Then
Do something, I got this part figured out.
Else
quit doing anything
End If

 

[Rich]

how are you determining if the query has any records, what's the code you're using?

 

[Boo]

Thats the question.
How do I determine if a query has any records.

Change: I'm now looking at a table to see if there are any records.

 

[Surjer]

Depends on how u run the querie. This is a pretty Common Method I have seen used to the death....

Dim DB as Database
Dim rs as recordset
Dim qryText as String


Set DB = CurrentDB

QryText = "Select * from MyTable"
Set RS = DB.OpenRecordset(QryText)

If not rs.recordcount = 0 then
'Do What you want with it
End if

 

[Boo]

Thanks. But
Since it is now a table instead of a query, I am having trouble figuring out how to tell Access it is a table.

Dim DB As Database
Dim rs As Recordset

DoCmd.OpenTable "Current Schedualed Trans"
If Not rs.RecordCount = 0 Then
MsgBox " "

 

[Surjer]

You do not need to use the DoCmd.OpenTable unless you want to see the table. If you want to access the data via code then the recordset is what you want.

Now, A recordset Points to data stored in a table. So to access the data you must use a SQL statement.

Like "Select * from [Current Schedules Trans]"

So your code would look like

'DoCmd.OpenTable "Current Schedualed Trans"
Set RS = Db.OpenRecordset("Select * from [Current Schedules Trans]")
If Not rs.RecordCount = 0 Then

 

[raskew]

Google (sorry) has a number of posts re this topic.
In earlier Access versions, the general guidance was
to use something like:

Public Sub RecSetOpen()

Dim db     As Database
Dim rs     As Recordset
Dim n      As Integer
Dim strSQL As String

   Set db = CurrentDb
   strSQL = "Select * from [Current Schedules Trans]"
   Set rs = db.OpenRecordset(strSQL)
   rs.MoveLast
   rs.MoveFirst
   n = rs.RecordCount
   
   If n > 0 Then
      'do something
   End If
   rs.Close
   db.Close
   Set db = Nothing
End Sub

Starting as I recall with A97, the guidance was that the

rs.MoveLast
rs.MoveFirst

....was not necessary and we could just eliminate these
two lines of code and go directly to n = rs.RecordCount.
Some very creditable contributors make a case that this
guidance may be incorrect and could result in erroneous
record counts. They maintain that if the recordset is slow
in loading, failing to rs.movelast / rs.movefirst could result
in an erroneous count since n will return a snapshot in time
where all records have not yet loaded.

Just food for thought.

Bob