Stop a macro from running based on # of rec in a table? 1

[Turb]

All,
I have been trying to make this work for several days now and am at my wits end.
I have an Access 2000 DB that runs minimized.
I have an autoexec macro that controls the sequencing.
The macro calls a couple of batch files (in succession), then runs a series of append queries before finally exiting Access.
The issue I need help with is this: how can I get the macro to stop function when the table in the DB reaches a specific count (number) of records,
popup an "ok only" VBS msgbox and then exit Access when "ok" is clicked?
I have been trying to use the Count function along with an If/Then statement and a MsgBox in an 'On Open' event for a hidden form but cannot seem to get the coding (see below) correct.
I have searched the Access help files, MS's website, and searched Tek-Tips. Please help! TIA

Private Sub Form_Open(Cancel As Integer)

    Dim rst As Recordset, strSQL As String
    strSQL = "SELECT Count(*) AS TOTAL " _
        & "FROM LIMIT_CHECK;"
        
    If TOTAL!TOTALOFLIMIT_CHECK >= 85 Then MsgBox vbOKOnly, "Contact DB Admin Immediately"
       DoCmd.RunCommand acCmdExit
    End If

End Sub

Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[PHV]

You may try something like this:
Private Sub Form_Open(Cancel As Integer)
If DCount("*", "LIMIT_CHECK") >= 85 Then
MsgBox vbOKOnly, "Contact DB Admin Immediately"
Cancel = True
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Turb]

PHV,
Thank you so much for your reply!
As long as the record count in the LIMIT_CHECK table remains below the >= 85 treshold, no problem. But as soon as I change that to say, >= 65 (I have 70 test records in the table), I get a runtime error '13' Type Mismatch.

Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[Turb]

I get the error on the line:

MsgBox vbOKOnly, "Contact DB Admin Immediately"

Thanks again,
Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[PHV]

MsgBox "Contact DB Admin Immediately", vbOKOnly

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Turb]

PHV,
Thank you very much!!
Star for you my friend.

Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/