using recordcount and not getting what is in the table

[sparks]

Is recordcount a little messed or what?

For Each tdf In dbs.TableDefs
If Left(tdf.Name, 3) = "tbl" Then
Debug.Print tdf.Name
Debug.Print tdf.RecordCount

it prints the correct name but the RecordCount is off on 8 out of 30
tables. the only thing is all the correct ones don't have any records
LOL

3 of the 8 show 20 records but the tables contain 21
2 or the 8 show 12 when they have 15
3 or the 8 show 17 when they have 18

what am I doing wrong?

[Salad]

Re: using recordcount and not getting what is in the table

sparks wrote:

Is recordcount a little messed or what?
>
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 3) = "tbl" Then
Debug.Print tdf.Name
Debug.Print tdf.RecordCount
>
it prints the correct name but the RecordCount is off on 8 out of 30
tables. the only thing is all the correct ones don't have any records
LOL
>
3 of the 8 show 20 records but the tables contain 21
2 or the 8 show 12 when they have 15
3 or the 8 show 17 when they have 18
>
what am I doing wrong?
>
>

Try this, see if it works better
Dim tdf As TableDef
Dim dbs As Database
Set dbs = CurrentDb
Dim rst As DAO.Recordset

For Each tdf In dbs.TableDefs
If Left(tdf.Name, 3) = "tbl" Then
Set rst = dbs.OpenRecords et(tdf.name, dbOpenSnapshot)
If rst.RecordCount 0 Then rst.MoveLast
Debug.Print tdf.name; rst.RecordCount
rst.Close
End If
Next
Set rst = Nothing
Set dbs = Nothing

[sparks]

Re: using recordcount and not getting what is in the table

On Thu, 18 Sep 2008 11:35:34 -0700, Salad <oil@vinegar.co mwrote:

Dim tdf As TableDef
Dim dbs As Database
Set dbs = CurrentDb
Dim rst As DAO.Recordset
>
For Each tdf In dbs.TableDefs
> If Left(tdf.Name, 3) = "tbl" Then
Set rst = dbs.OpenRecords et(tdf.name, dbOpenSnapshot)
If rst.RecordCount 0 Then rst.MoveLast
Debug.Print tdf.name; rst.RecordCount
rst.Close
End If
Next
Set rst = Nothing
Set dbs = Nothing

at first I thought it was the the rst.movelast that would make it
work.
I thought that the movelast would make sure that everything was loaded
before it printed the count.

but that did not make a difference.

It was the dbOpenSnapshot that did it.

I had tried dbOpenTable and dbOpenDynaset and both gave the same
results.

thank you very much for your help it was driving me nuts....I am just
glad I looked and didn't take it as it was....

thanks again

[Salad]

Re: using recordcount and not getting what is in the table

sparks wrote:

On Thu, 18 Sep 2008 11:35:34 -0700, Salad <oil@vinegar.co mwrote:
>
>

> Dim tdf As TableDef
> Dim dbs As Database
> Set dbs = CurrentDb
> Dim rst As DAO.Recordset
>>
> For Each tdf In dbs.TableDefs
>> If Left(tdf.Name, 3) = "tbl" Then
> Set rst = dbs.OpenRecords et(tdf.name, dbOpenSnapshot)
> If rst.RecordCount 0 Then rst.MoveLast
> Debug.Print tdf.name; rst.RecordCount
> rst.Close
> End If
> Next
> Set rst = Nothing
> Set dbs = Nothing

>
>
at first I thought it was the the rst.movelast that would make it
work.
I thought that the movelast would make sure that everything was loaded
before it printed the count.
>
but that did not make a difference.
>
It was the dbOpenSnapshot that did it.
>
I had tried dbOpenTable and dbOpenDynaset and both gave the same
results.

How odd!

thank you very much for your help it was driving me nuts....I am just
glad I looked and didn't take it as it was....
>
thanks again
>

Entirely welcome.