How do I exit sub on error 3162? 1

[rccline]

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!

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()
'[URL unfurl="true"]https://msdn.microsoft.com/en-us/library/5hsw66as.aspx[/URL]


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

 

[MajP]

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.

 

[rccline]

How do I stop it?

 

[rccline]

I open a macro and use the code

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

I get a syntax error.

 

[MajP]

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.

 

[rccline]

Thank you for the advice...

Rccline

 

[Andrzejek]

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

Option Compare Database
Option Explicit

Private Sub cmdAppendImportContacts_Click()
Dim Msg As String
[blue]
On Error GoTo ErrHndlr
[/blue]
Msg = " Are you sure you want to append imported data into Contacts table?"

Select Case MsgBox(Msg, vbYesNoCancel)
    Case vbYes[green]
        '<=== runactivemacro clean()
        '[URL unfurl="true"]https://msdn.microsoft.com/en-us/library/5hsw66as.aspx[/URL][/green]
        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
[blue]
ErrHndlr:
If Err.Number = 3162 Then
    MsgBox "No records to Append"
    Exit Sub
End If
[/blue]
End Sub

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[rccline]

Great post Andy... thank you.

Robert

 

[MajP]

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.