Recordset Error

[gazzieh]

If I run the following code:

Set rst = db.OpenRecordset("tblBooking")

Then it returns 7; the correct value

If I alter this slightly:

Set rst = db.OpenRecordset("SELECT * FROM tblBooking")

Then it returns 1; obviously the wrong value.



The reason why I wish to do this is because I actually wish to run the following:

Set rst = db.OpenRecordset("SELECT * FROM tblBooking WHERE dteDate=#" & txtDate.value & "#")

But this returns 1 (clearly since the first section returns an error.

Why?

Oh, BTW, I have set the following:

Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb()

The returning code is:

lblNo.Caption = "There are currently " & rst.RecordCount & " bookings for that date."

[pbaldy]

That's not an error, it's a feature.

http://www.baldyweb.com/RecordCounts.htm

If that's all you want from the recordset, this would be more efficient:

Set rst = db.OpenRecordset("SELECT Count(*) As HowMany FROM tblBooking WHERE dteDate=#" & txtDate.value & "#")

lblNo.Caption = "There are currently " & rst!HowMany & " bookings for that date."

[gazzieh]

Thanks for the response and the solution; both are perfect.

Curiously, I have never come across this before and so never required this knowledge. Then again, I think I have only ever needed to know the recordset equalled at least 1 record.

Anyhow, thanks very much.

[pbaldy]

Happy to help.