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:
Code:
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.