How to know emplty recordset

[rickyfong]

I have the following VBA code to select certain records from table to recordset, however, sometimes no records existed and movefirst should give error message. Just want to know how to know there got no records in the TRANS2 in case no criteria is matched?? Thanks

Set TRANS2 = db.OpenRecordset("SELECT * FROM TRANSACTION WHERE tcode = 't12345'", dbOpenDynaset)

TRANS2.MoveFirst

 

[namliam]

if Trans2.Recordcount = 0 then msgbox "No records found"

 

[Frothingslosh]

I've always been partial to 'If TRANS2.BOF And TRANS2.EOF Then MsgBox ErrMsg', myself, but I'll admit namliam's method is probably around twice as fast.

 

[Keith Tedbury]

Its normally a good idea to check the record count before you do anything with the record set to avoid errors.

My self I do

Set TRANS2 = db.OpenRecordset("SELECT * FROM TRANSACTION WHERE tcode = 't12345'", dbOpenDynaset)

If TRANS2.RecordCount<> 0 then

TRANS2.MoveFirst

End If