Dcount results in error "Overflow"

[skydivetom]

Experts:

I'm running into an "overflow" error when using DCOUNT.

Quick background:
1. I'm executing 5 append queries as part of a routine.
2. Prior to the execution of the 5 append queries, I drop all records from the 5 associated tables.
3. Then, as part of the append, I set **DoCmd.SetWarnings False** in order to prevent the display of both a) # of records dropped and b) # of records appended for EACH of the 5 tables.

Now, once the last append query has been executed (w/o any indication of # of records dropped and added), I want to throw a customized message box (using DCOUNT) to display the total # of records processed.

For testing purposes, I'm using the code below:

Private Sub cmdDcount_Click()

    Dim RecordCount_MMAC As Integer
    Dim RecordCount_NNSY As Integer
    Dim RecordCount_PHNSY As Integer
    Dim RecordCount_PNSY As Integer
    Dim RecordCount_POAIRS As Integer
    Dim RecordCount_TOTAL As Integer
    
    RecordCount_MMAC = DCount("[AUTO_ID]", "tbl_MMAC")
    RecordCount_NNSY = DCount("[AUTO_ID]", "tbl_NNSY")
    RecordCount_PHNSY = DCount("[AUTO_ID]", "tbl_PHNSY")
    RecordCount_PNSY = DCount("[AUTO_ID]", "tbl_PNSY")
    RecordCount_POAIRS = DCount("[AUTO_ID]", "tbl_POAIRS")
    
    RecordCount_TOTAL = (RecordCount_MMAC + RecordCount_NNSY + RecordCount_PHNSY + RecordCount_PNSY + RecordCount_POAIRS)
    
    MsgBox "# of records: " & RecordCount_TOTAL

End Sub

When putting a breakpoint at **RecordCount_TOTAL = (RecordCount_MMAC...**, and then hover over the 5 record count lines, the correct # of records are displayed in the tooltip.

However, once I remove the breakpoint and then run the function, an "Overflow error" (see attached JPG) is thrown.

My question(s):
1. How can I prevent the over flow error so that the message box: "MsgBox "# of records: " & RecordCount_TOTAL" shows me the sum of the 5 DCOUNTs?
2. Is there a smoother way (e.g., using a ForLoop or DoWhile) vs. hard-coding the tables names? That is, all 5 tables for which I need to get the DCOUNT have the same table prefix = "tbl_"

Thanks,
Tom



P.S. Please see update in post #2.

[skydivetom]

Quick update... changed declaration from "Integer" to "Long"....

Msgbox now outputs the correct values.

Question remains whether or not a ForLoop OR Dowhile can be applied to count total # of records for all tables starting with "tbl_"?

[pbaldy]

Sure, loop the TableDefs collection, checking for the first 4 characters being "tbl_". Accumulate the values in a variable within the loop.

[orange]

Here's a sample with For Loop

' ----------------------------------------------------------------
' Procedure Name: recCountsAllTables
' Purpose: get a count of all records in a database
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 14-Jul-21
' ----------------------------------------------------------------
Sub recCountsAllTables()
    Dim tdf As DAO.TableDef
    Dim TotRecords As Long
    For Each tdf In CurrentDb.TableDefs
        Debug.Print tdf.name & "   " & tdf.RecordCount
        TotRecords = TotRecords + tdf.RecordCount
    Next tdf
    Debug.Print "Total Records in " & CurrentDb.name & "(" & TotRecords & ")"
End Sub

In my sample database
.......
ZmT1 5
ZMT2 4
ztemp_MilestoneFormat 3
ZZZArticles 3
Total Records in C:\Users\Jack\Documents\Database1_o365.accdb(58305 0)


You can modify to select only certain tables as needed.

[Minty]

If your table names aren't going to change, you have already done the hard work.
You could get the tables names in a loop and do a dcount and simply add it up as you went through, but it seems like hard work to me, unless the table names will change frequently.

[skydivetom]

Orange -- your solution is perfect! I tweaked the code to only include records where table name starts with "tbl_".

Cheers,

Private Sub cmdMethod3_Click()

    Dim tdf As DAO.TableDef
    Dim TotRecords As Long
    
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) = "tbl_" Then
            TotRecords = TotRecords + tdf.RecordCount
        End If
    Next tdf

    MsgBox "Total Records: " & TotRecords

End Sub