Recordsets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to using Recordsets to select data from tables, but I tried building
the following statements to start out:
PQuery = "SELECT * FROM PHY"
Set PRecord = CurrentDb.OpenRecordset(PQuery)
MsgBox "Records " & PRecord.RecordCount, vbOKOnly, "MESSAGE"

I have 3 records in the PHY table, but the RecordCount always displays as 1.
I have tried adding or deleting records in the table, but it always displays
as 1. Can someone explain what is happening? If it is because of my lack of
experience with recordsets, can I get some suggestions on materials to review?

Thanks.
 
Martin,

You could say this is a bug, in a sense... the thing is, you have to
force Jet to actually try to read the recordset, before you get a
correct record count. Try this:

PQuery = "SELECT * FROM PHY"
Set PRecord = CurrentDb.OpenRecordset(PQuery)
If Not PRecord.EOF Then PRecord.MoveLast
MsgBox "Records " & PRecord.RecordCount, vbOKOnly, "MESSAGE"

If you are going to work with tthe recordset afterwards, you'll probably
need a:
If Not PRecord.BOF Then PRecord.MoveFirst
before you carry on.

HTH,
Nikos
 
From Nikos Yannacopoulos :
Martin,

You could say this is a bug, in a sense... the thing is, you have to force
Jet to actually try to read the recordset, before you get a correct record
count. Try this:

PQuery = "SELECT * FROM PHY"
Set PRecord = CurrentDb.OpenRecordset(PQuery)
If Not PRecord.EOF Then PRecord.MoveLast
MsgBox "Records " & PRecord.RecordCount, vbOKOnly, "MESSAGE"

If you are going to work with tthe recordset afterwards, you'll probably need
a:
If Not PRecord.BOF Then PRecord.MoveFirst
before you carry on.

HTH,
Nikos
Click to expand...

And if you just need the count, you can look at the DCount function.
Or modify your query so it will only select one field (PK), speeding
things up since it doesn't need to get all the other fields.
 
Thank you both for the replies. The If statement with the EOF works fine and
I will check into the DCount function.

Thanks again.
 
Back
Top