Error Message box

[Alex Martinez]

Hi,

I am using Access 2002 and I have a command button that will delete data
from a table which will import an Excel file and append the file into the
main table and a message box (macro) will appear stating that "Upload
Completed" if the user proceed. Below is my coding:

'Delete data in AuditUpload file

CurrentDb.Execute "DELETE * FROM AuditUpload", dbFailOnError

'Upload AuditUpload spreasheet from Excel spreadsheet
On Error GoTo Err_cmdUploadAppend_Click

Dim stDocName As String

stDocName = "Audit Upload"
DoCmd.RunMacro stDocName

'Append AuditUpload to main audit inventory file

Dim stDocName1 As String

stDocName1 = "Append Audit Inventory Query"
DoCmd.OpenQuery stDocName1, acNormal, acEdit

'Notify user upload has completed

Dim stDocName2 As String

stDocName2 = "Upload Completed Message Box"
DoCmd.RunMacro stDocName2

Exit_cmdUploadAppend_Click:
Exit Sub

Err_cmdUploadAppend_Click:
MsgBox Err.Description
Resume Exit_cmdUploadAppend_Click


My question is if a user press the command button and decide to not append
the file where do I place run macro command in the current code? All I want
is to have a message box (macro) to appear saying "Upload/Append not
completed" when the user aborts the append command. If my code needs
cleaning up, please let me know. I am trying to understand message box
errors. Any tips will be appreciated. Thank you.

 

[Allan Murphy]

Alex

Try the following

sub UploadAppend_Click()
On Error GoTo Err_cmdUploadAppend_Click

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Dim stDocName As String

Msg = "Do you want ot upload the file? " ' Define message.
Msg = Msg & vbCrLf & vbCrLf & "Select Yes to Upload."
Msg = Msg & vbCrLf & vbCrLf & "Select NO to Close Form."
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Upload File Check" ' Define title.

Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes

'Delete data in AuditUpload file
CurrentDb.Execute "DELETE * FROM AuditUpload", dbFailOnError

'Upload AuditUpload spreasheet from Excel spreadsheet
stDocName = "Audit Upload"
DoCmd.RunMacro stDocName

'Append AuditUpload to main audit inventory file

DoCmd.OpenQuery "Append Audit Inventory Query", acNormal, acEdit

'Notify user upload has completed
msgbox( "Upload Completed ")
else ' User chose No
msgbox("Upload not performed")
end if


Exit_cmdUploadAppend_Click:
Exit Sub

Err_cmdUploadAppend_Click:
MsgBox Err.Description
Resume Exit_cmdUploadAppend_Click

end sub

 

[Alex Martinez]

Thanks Allan for your help. The code works great accept if the user press
the NO key in the append query message box "You are about to run an append
query that will modify data in your table" or press NO key in the message
box "You are about to append 2000 row(s)" I still will get "Upload
Completed" message box instead I should get the "Upload not performed"
message box in either case. If anybody can help I will appreciated. Thank
you in advance.

Regards