recordcount problem

[icbiny]

the following is adding 70 to the total no matter how many records are in the table!

Private Sub Command0_Click()

Dim MyDB As Database
Dim MyRS As Recordset

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("Indies")
MsgBox (MyRS.RecordCount)
MyRS.Close
MyDB.Close

End Sub

 

[gemma-the-husky]

I would check the query "indies"

I expect you have some sort of cross-product join causing certain values to be included multiple times.

ie if you have table A with values ABC and table B with values 123 - when you join the tables in a query you may find you get 9 rows in the query

A1, A2, A3, B1, B2, B3, C1, C2, C3.

It is for this reason that a database cannot represent a many to many link. You always have to decompose it into 2 1 to many links

So not

Table A many -------- many Table B

but

Table A 1----- many Junction Table many ----- 1 Table B


(note also that just requesting the record count of the recordset may not work correctly. you may need to move to the last record, before the count is accurate.

simply dcount("*","indies") should give you the record count without needing to check a recordset.

 

[JHB]

Else move last first.

MyRS.MoveLast

 

[icbiny]

the dcount thing worked, tyvm

indies is actually a table and the code has been working fine for 10 years. I made some changes to the database recently although I didn't touch that code it stopped working. the old backup version of the database still works fine!

 

[Guus2005]

If speed is an issue, you may want to stick with the recordset alternative:

Private Sub Command0_Click()
        
    Dim MyDB As Database
    Dim MyRS As Recordset
    
    Set MyDB = CurrentDb
    Set MyRS = MyDB.OpenRecordset("Indies")
    MyRS.MoveLast
    MsgBox (MyRS.RecordCount)
    MyRS.Close
    MyDB.Close

End Sub

HTH