Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Checking Recordset Recordcount (2000)

    I have a couple of tables(PREVISIONI, CONSUNTIVO) in the attached mdb and I'm trying to make sure that the number of records in each table is a multiple of 24.
    I have devised the following code but it still gives me the message "Some records are missing" in spite of the fact that the number of records in each table is indeed a multiple of 24. What's wrong?

    Sub CheckRecordCount()
    Dim cnn1 As ADODB.Connection
    Dim cat1 As New ADOX.Catalog
    Dim rst1 As New ADODB.Recordset
    Dim rst2 As New ADODB.Recordset
    On Error GoTo ErrHandler
    Set cnn1 = CurrentProject.Connection
    Set cat1.ActiveConnection = cnn1
    Set rst1.ActiveConnection = cnn1
    ' Set rst2.ActiveConnection = cnn1

    rst1.Open "Previsioni", , adOpenKeyset, _
    adLockOptimistic, adCmdTable
    rst2.Open "consuntivo", cnn1, adOpenForwardOnly, _
    adLockReadOnly, adCmdTable
    If rst1.RecordCount Mod 24 <> 0 Or rst2.RecordCount Mod 24 <> 0 Then
    MsgBox "Some records are missing"
    End If
    ExitHandler:
    On Error Resume Next
    rst1.Close
    Set rst1 = Nothing
    rst2.Close
    Set rst2 = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking Recordset Recordcount (2000)

    Giorgio,

    In such a case, it is useful to analyse the problem part by part. Your code only issues a blanket statement "Some records are missing", but it doesn't tell you in which recordset it occurs, and what the actual record counts are. To find the cause, you must temporarily let the code return those record counts, for example

    Debug.Print rst1.RecordCount, rst2.recordCount

    The result will be in the immediate window. I got 840 and -1, so the first record count is OK, the second one not. A quick look at the code reveals why:

    rst2.Open "consuntivo", cnn1, adOpenForwardOnly, _
    adLockReadOnly, adCmdTable

    You open rst2 as a forward only recordset. ADODB does not read in the records in advance, so it doesn't know how many records there are. Only after you moving forward record by record until EOF would you know the exact record count. From the online help:
    <hr>The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.<hr>
    If you open rst2 the same way you open rst1, you will get the correct record counts.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Recordset Recordcount (2000)

    Thank you Hans,
    Suppose a record were indeed missing from either table, what's the VBA code to use to determine which of the two tables is missing a record?
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking Recordset Recordcount (2000)

    C'mon Giorgio,

    You can work that out yourself. The answer is obvious.

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Recordset Recordcount (2000)

    Sorry <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    I've solved it this way:
    If rst1.RecordCount Mod 24 <> 0 Then
    MsgBox "Some records are missing in PREVISIONI"
    ElseIf rst2.RecordCount Mod 24 <> 0 Then
    MsgBox "Some records are missing in CONSUNTIVO"
    End If

    How can I launch the CheckRecordCount sub as soon as the mdb file opens?
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Checking Recordset Recordcount (2000)

    I wouldn't use ElseIf here, but

    If rst1.RecordCount Mod 24 <> 0 Then
    MsgBox "Some records are missing in PREVISIONI"
    End If
    If rst2.RecordCount Mod 24 <> 0 Then
    MsgBox "Some records are missing in CONSUNTIVO"
    End If

    If records are missing from both tables, your code would only report it for PREVISIONI. The code above will report it for both.

    Your database opens the GRTN form on startup. You can call CheckRecordCount in the On Load event of this form:

    Private Sub Form_Load()
    CheckRecordCount
    End Sub

    This will perform the check each time GRTN is opened. Alternatively, you could change CheckRecordCount to a function, and create a macro named AutoExec that calls this function through the RunCode action. A macro named AutoExec is run automatically when the database opens.
    Attached Images Attached Images
    • File Type: png x.PNG (4.0 KB, 0 views)

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking Recordset Recordcount (2000)

    Thank you so much Hans! <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •