INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How do I exit sub on error 3162?

How do I exit sub on error 3162?

(OP)

I have a sub to run an append query. If the append query has no records, the code breaks.

How do I exit the sub on error?

Thank you!

CODE

Option Compare Database


Private Sub cmdAppendImportContacts_Click()

Msg = " Are you sure you want to append imported data into Contacts table? Click Yes to Append; Click No to Delete imported table; Click Cancel to Cancel operation"

Response = MsgBox(Msg, vbYesNoCancel)

If Response = vbYes Then
'<=== runactivemacro clean()
'https://msdn.microsoft.com/en-us/library/5hsw66as.aspx


DoCmd.RunMacro "mcrAppendImportContacts"
Exit Sub
End If


If Response = vbNo Then

DoCmd.DeleteObject acTable, "ImportContacts_xlsx"
MsgBox " ImportContacts Table has been deleted. To Append, re-import ImportContacts.xlsx "
Exit Sub
End If

If Response = vbCancel Then
MsgBox "Action Cancelled"
Exit Sub
End If
    
End Sub 

RE: How do I exit sub on error 3162?

You should not run the code if you know there are no records. So do a dcount first to see if the amount of records is greater than 0.

RE: How do I exit sub on error 3162?

(OP)
How do I stop it?

RE: How do I exit sub on error 3162?

(OP)
I open a macro and use the code

CODE

If
=DCount("*","qryAppendimportContacts")=0
Cancel Event
End IF 

I get a syntax error.

RE: How do I exit sub on error 3162?

Sorry I only write VBA, I do not waste my time with macros. But in general I would not do a dcount on the actual append query, but on another select query that would return the same records to be appended. In vba I would not cancel anything. I would do an if then. If the dcount is greater than 0 then execute the append.

RE: How do I exit sub on error 3162?

(OP)
Thank you for the advice...

Rccline

RE: How do I exit sub on error 3162?

In the worst case (pun intended smile ) scenario you can always trap the error and take appropriate action:

CODE

Option Compare Database
Option Explicit

Private Sub cmdAppendImportContacts_Click()
Dim Msg As String

On Error GoTo ErrHndlr

Msg = " Are you sure you want to append imported data into Contacts table?"

Select Case MsgBox(Msg, vbYesNoCancel)
    Case vbYes
        '<=== runactivemacro clean()
        'https://msdn.microsoft.com/en-us/library/5hsw66as.aspx
        DoCmd.RunMacro "mcrAppendImportContacts"

    Case vbNo
        DoCmd.DeleteObject acTable, "ImportContacts_xlsx"
        MsgBox " ImportContacts Table has been deleted. To Append, re-import ImportContacts.xlsx "

    Case vbCancel
        MsgBox "Action Cancelled"    
End Select

Exit Sub

ErrHndlr:
If Err.Number = 3162 Then
    MsgBox "No records to Append"
    Exit Sub
End If

End Sub 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: How do I exit sub on error 3162?

(OP)
Great post Andy... thank you.

Robert

RE: How do I exit sub on error 3162?

Quote:

In the worst case (pun intended smile ) scenario you can always trap the error and take appropriate action

As Andy points out this is the worst case and only an example of error trapping. This should not be considered a recommended approach to your issue. Unlike many other languages, VBA only uses simple unstructured error handling. Unless you absolutely have to, you should not use error handling in place of coding structure and flow. In other words, if there is a way ahead of time to avoid a possible error than write code to do that. Do not simply let a an error occur and then plan to trap it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close