locked
.RecordCount method returning incorrect value RRS feed

  • Question



  • I've been working on a script that imports data into a table then creates an array that allows me to truncate the value in one of the fields. It's been running fine as I've been developing but as soon as I got it to stop importing duplicate records, it started counting 10 more records than I have in the table (I have 10 test records I'm working with).  

    I've even deleted all of the records in the table to make sure it's totally empty and added a message box before anything else runs to count the number of records and it's coming out as 10.

    Any thoughts? I can post the code if anyone wants to see it.

    Thanks.

    Debbie
    Wednesday, August 12, 2009 3:43 PM

Answers

  • Hi again,  if TUCounter is set to 10 your loop should not get to 11!

    I did a simple table and macro to check the behaviour of recordcount, and I found that if I deleted records from the recordset the recordcount correctly reported. Are you using the recordset for your deletes or going to the table directly?

    Below is my check code which works on a simple table with an ID column.


    Public Sub rCount()
    Dim rs As Recordset

    Set rs = CurrentDb().OpenRecordset("Table1")
    rs.MoveLast
    Debug.Print "Start RC ", rs.RecordCount
    rs.MoveFirst
    While Not rs.EOF
        If rs!ID > 4 Then rs.Delete
        rs.MoveNext
    Wend
    Debug.Print vbCr; "End RC ", rs.RecordCount
    rs.Close
    Set rs = Nothing
    End Sub

    Regards

    ADG

    • Marked as answer by Tim Li Wednesday, August 19, 2009 2:17 AM
    Thursday, August 13, 2009 7:10 AM

All replies

  • Hi, recordcount is not reliable unless you move to the last record. If you need to know an accurate count do a move to the last record first.

    Regards

    ADG
    Wednesday, August 12, 2009 3:50 PM
  • Forgot to mention that's already included. Still giving me the incorrect value.  Here's the code where I'm replacing the text. The line in bold text is generating a record not found error when TUCounter = 11 when my table starts off with no records. I've also included the following at hte beginning of my script, right after I declare the variables and set the recordset variable to my table:

    USAGErst.MoveLast
    MsgBox USAGErst.RecordCount

    This message box gives me a count of 10.

    ~~~~~~~~~~~~~

    USAGErst.MoveLast

    TUCounter = USAGErst.RecordCount

    MsgBox TUCounter

        If TUCounter = 0 Then
       
            MsgBox "There is no usage."
            Exit Sub
        Else
        End If

    ' create the array for the records in tblIMP_Usage

    ReDim TUReplace(1 To TUCounter) As Variant

    USAGErst.MoveFirst

        For TUReplaceID = 1 To TUCounter
           
            TUReplace(TUReplaceID) = USAGErst!contract_item ' invalid record count is causing this to fail after first 10 records
            USAGErst.MoveNext
       
        Next TUReplaceID

    USAGErst.MoveFirst

        For Each TUReplaceID In TUReplace
           
            IDtrimmed = Mid(TUReplaceID, 1, IDsize)
           
        MsgBox Len(IDtrimmed) & ", " & IDsize
           
            If Len(IDtrimmed) <> IDsize Then
       
                With USAGErst
                    .Edit
                    !contract_item = IDtrimmed
                    .Update 
                    .MoveNext
                End With
           
            Else
            End If
           
        Next TUReplaceID

    Wednesday, August 12, 2009 3:56 PM
  • Hi again,  if TUCounter is set to 10 your loop should not get to 11!

    I did a simple table and macro to check the behaviour of recordcount, and I found that if I deleted records from the recordset the recordcount correctly reported. Are you using the recordset for your deletes or going to the table directly?

    Below is my check code which works on a simple table with an ID column.


    Public Sub rCount()
    Dim rs As Recordset

    Set rs = CurrentDb().OpenRecordset("Table1")
    rs.MoveLast
    Debug.Print "Start RC ", rs.RecordCount
    rs.MoveFirst
    While Not rs.EOF
        If rs!ID > 4 Then rs.Delete
        rs.MoveNext
    Wend
    Debug.Print vbCr; "End RC ", rs.RecordCount
    rs.Close
    Set rs = Nothing
    End Sub

    Regards

    ADG

    • Marked as answer by Tim Li Wednesday, August 19, 2009 2:17 AM
    Thursday, August 13, 2009 7:10 AM
  • Okay, so some progress...

    Up to this point I was deleting directly in the table... but I've done that at least 15-20 times, so I would expect if I had "phantom" records, my record count should be much higher - shouldn't it? 

    I tried running your script and with one import's worth of records in the table (10), it is giving me a count of 20 before I delete, but still shows 10 after the delete. When I view the actual table after the delete there are no records. When I run the script again, I get a runtime error 3021: no current record. When I run my script to import and truncate, as expected I'm still getting a record count of 20 after my import...

    Any thoughts?

    I don't have a counter field, so I'm checking for null values in my ID field, which is text:

    Public Sub rCount()
    Dim rs As Recordset

    Set rs = CurrentDb().OpenRecordset("tblIMP_Usage")
    rs.MoveLast
    MsgBox "Start RC " & rs.RecordCount
    rs.MoveFirst
    While Not rs.EOF
        If Not IsNull(rs!Invoice_ID) Then rs.Delete
        rs.MoveNext
    Wend
    MsgBox "End RC " & rs.RecordCount
    rs.Close
    Set rs = Nothing
    End Sub

    Wednesday, August 19, 2009 1:55 PM
  • So I got fed up and  tried deleting the table and recreating it and that seemed to work. I'm able to delete records directly in the table and via the script without any issues.

    But I'm still curious as to how I'd end up with the ghost records? Anyone have any thoughts?

    Thanks,

    Debbie
    Wednesday, August 19, 2009 2:52 PM