sql and recordcount dont give same results

[Jean-Paul De Winter]

When I run the sql in following code I get 4 records (which is
correct)
When I check the "recordcount" it seems like TB only has 1 record
What am I doing wrong?
Thanks
Dim db As Database
Dim TB As Recordset, TB_UITPINT As Recordset
Dim TB_filt As String
Set db = CurrentDb()
Set TB_uitprint = db.OpenRecordset("UITPRINTRAPPORTEN",
dbOpenTable)
TB_filt = "SELECT Rapport.Naam, Rapport.Periode FROM Rapport WHERE
((Rapport.Naam)='Baeyens Roel') AND ((Rapport.Periode)='Maart -
April'));"
Set TB = db.OpenRecordset(TB_filt)
If TB.RecordCount <> 0 Then
End If
TB.MoveFirst
Do Until TB.EOF
TB_uitprint.AddNew
TB_uitprint!naam = TB!naam
TB_uitprint!Periode = TB!Periode
TB.MoveNext
Loop
TB_uitprint.Update

 

[Nikos Yannacopoulos]

Jean-Paul,

If you check the RecordCount property of a recordset right after you
have opened it, without having moved in it at all, then you will either
get a 0 if no record exists, or a 1 otherwise, regardless of the actual
number of records; not your fault! If you need the exact recordcount, do
a MoveLast first (followed by MoveFirst if required) and then check the
recordcount. I don't suppose this was done intentionally, it looks more
like a bug to me, but that's the way it is, and you better remember it
if you need an accurate recordcount!

HTH,
Nikos

 

[Andreas]

You are using a SQL statement to create the recordset for "TB".
This creates a "Dynaset" type recordset by default.
You can not check the "RecordCount" property until you have done a
"TB.MoveLast". This is one of the "features" of a Dynaset type
recordset. Check the help - I think if you specify "dbOpenSnapshot",
this problem might go away. For small sets of data, this may also be faster.

Regards,
Andreas

 

[Jean-Paul De WInter]

the recordcount isn't THAT important... when I run the while - loop only one
record is added as well so, will this be solved just by adding a MOVELAST
command?
Thanks
JP

 

[Nikos Yannacopoulos]

Gotcha! This has nothing to do with the recordcount; it has to do with
the fact that TB_uitprint.Update is outside the loop, so it actually
only adds the very last record returned by tyhe query. Move the
TB_uitprint.Update before the Loop and try again!

HTH,
Nikos

 

[Andreas]

Since you are not actually using the RecordCount for anything, it really
does not matter.

Below is the revised version of the code.
Somewhat better.
Untested.


Sub ????? ' or Function
On Error Goto ProcedureError
Dim db As DAO.Database
Dim TB_uitprint As DAO.Recordset
Dim TB As DAO.Recordset
Dim TB_filt As String
TB_filt = "SELECT Rapport.Naam, Rapport.Periode FROM Rapport WHERE
((Rapport.Naam)='Baeyens Roel') AND ((Rapport.Periode)='Maart -
April'));"
Set db = CurrentDb()
Set TB_uitprint = db.OpenRecordset("UITPRINTRAPPORTEN",
dbOpenTable)
Set TB = db.OpenRecordset(TB_filt)
If TB.EOF = False Then
TB.MoveFirst
Do Until TB.EOF
TB_uitprint.AddNew
TB_uitprint!naam = TB!naam
TB_uitprint!Periode = TB!Periode
TB_uitprint.Update
TB.MoveNext
Loop
End If
ProcedureExit:
On Error Resume Next
TB.Close
Set TB = Nothing
TB_uitprint.Close
Set TB_uitprint= Nothing
db.Close
Set db = Nothing
Exit Sub ' or Function?
ProcedureError:
Msgbox "Do your own stuff here"
Resume ProcedureExit
End Sub ' or Function

Regards,
Andreas

 

[Andreas]

You are welcome
And make sure you look at the rest of it as well, especially the bit at
the end, where we close and destroy your recordsets!

Regards,
Andreas

 

[Jean-Paul De WInter]

Thank you all sooo much...
JP

Since you are not actually using the RecordCount for anything, it really
does not matter.

Below is the revised version of the code.
Somewhat better.
Untested.


Sub ????? ' or Function
On Error Goto ProcedureError
Dim db As DAO.Database
Dim TB_uitprint As DAO.Recordset
Dim TB As DAO.Recordset
Dim TB_filt As String
TB_filt = "SELECT Rapport.Naam, Rapport.Periode FROM Rapport WHERE
((Rapport.Naam)='Baeyens Roel') AND ((Rapport.Periode)='Maart -
April'));"
Set db = CurrentDb()
Set TB_uitprint = db.OpenRecordset("UITPRINTRAPPORTEN",
dbOpenTable)
Set TB = db.OpenRecordset(TB_filt)
If TB.EOF = False Then
TB.MoveFirst
Do Until TB.EOF
TB_uitprint.AddNew
TB_uitprint!naam = TB!naam
TB_uitprint!Periode = TB!Periode
TB_uitprint.Update
TB.MoveNext
Loop
End If
ProcedureExit:
On Error Resume Next
TB.Close
Set TB = Nothing
TB_uitprint.Close
Set TB_uitprint= Nothing
db.Close
Set db = Nothing
Exit Sub ' or Function?
ProcedureError:
Msgbox "Do your own stuff here"
Resume ProcedureExit
End Sub ' or Function

Regards,
Andreas