VBA Code Help Please

[buckwheat]

Can someone help me modify this code slightly please. When users open the database, there is a form with cmd button to trigger a delete query to replace existing data in a table. I want to modify this code so if the user selects "Yes" to delete contents, the a macro "ImportUnmatched" will run after data is deleted, and if the user selects "No" another form called "frmUnmatched" will open after they receive the message box that data was not deleted appears.

Appreciate your help

Private Sub cmdConfirm_Click()
On Error GoTo Err_cmdConfirm_Click
Dim stDocName As String
Dim intAnswer As Integer


stDocName = "qryDeleteTblUnmatched"
DoCmd.OpenQuery stDocName, acNormal, acEdit

intAnswer = MsgBox("You are about to replace your previous analysis. Are you sure you want to this?", vbQuestion + vbYesNo, "Delete Record")
If intAnswer = vbYes Then
RunCommand stDocName = acCmdConfirm
End If
Exit Sub
cmdConfirm_Click_Err:
MsgBox "Analysis was not replaced!"
Exit Sub
Exit_cmdConfirm_Click:
Exit Sub
Err_cmdConfirm_Click:
MsgBox Err.Description
Resume Exit_cmdConfirm_Click




End Sub

[June7]

Something like:

If intAnswer = vbYes Then
RunCommand stDocName = acCmdConfirm
'run macro or VBA code here
Else
MsgBox "Records not deleted."
DoCmd.OpenForm "frmUnmatched"
End If

[buckwheat]

Thanks. Should any lines of my original code be removed?

[June7]

You might change the error handler message. Something like:

MsgBox "Process error. Contact Administrator. : " & Err.Number & " : " & Err.Description

[buckwheat]

I think I need a little more detail on the structure . . . I'm very green with VBA and am sure my structure is not correct. . . here is what I have, I'm getting a Type Mismatch error after the Msg box "you are about to replace" (I've renamed the "cmdConfirm" with "cmdDelete")

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
Dim stDocName As String
Dim intAnswer As Integer
stDocName = "qryDeleteTblUnmatched"
DoCmd.OpenQuery stDocName, acNormal, acEdit
intAnswer = MsgBox("You are about to replace your previous analysis. Are you sure you want to do this?", vbQuestion + vbYesNo, "Delete Record")
If intAnswer = vbYes Then
RunCommand stDocName = acCmdDelete
DoCmd.RunMacro "macImportUnmatched"
DoCmd.OpenForm "frmUnmatched"

Else
MsgBox "Records not Replaced."
DoCmd.OpenForm "frmUnmatched"
End If

Exit_cmdDelete_Click:
Exit Sub
Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click
Exit Sub

End Sub

[buckwheat]

Another thing that is happening is I'm getting the warning that comes with the delete qry and if I select "no," it still takes me to my msg box asking if I want to replace.

[June7]

I am confused, where are you selecting 'no' before the MsgBox?

If you don't want the warning, in the macro, turn off warnings with SetWarnings method, then turn warnings back on after running the query

I don't use macros, only VBA which would be:

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tablename WHERE ...."
DoCmd.SetWarnings True

Or SetWarnings not needed with:

CurrentDb.Execute "DELETE FROM tablename WHERE ..."

[buckwheat]

I'll approach this a different way. . . . again since I don't know VBA, giving partial code doesn't help me solve the problem. Thanks for your time.

[June7]

SetWarnings can be coded within macro.

I don't know what code you have in the macro you are calling but if all it does is run a query, then the VBA code I suggest would be instead of calling the macro.

About the 'type mismatch' error - what line triggers that error? Step debug. Refer to link at bottom of my post for VBA debugging techniques.

[buckwheat]

Sorry, I didn't catch your last question about the type mismatch error. It is occurring if I say "yes" to delete, If I select "no" do not delete, everything works fine.

[June7]

Syntax of the RunCommand line looks wrong. Try

RunCommand acCmdDelete

or

DoCmd.RunCommand acCmdDelete

[buckwheat]

I figured this one out. . . using the updated code below instead of the code in my initial thread, the "type mismatch" is no longer an issue.

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
Dim stDocName As String

stDocName = "qryDeleteTblUnmatched"
DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.RunMacro "macImportUnmatched"
DoCmd.OpenForm "frmUnmatched", acNormal

Exit_cmdDelete_Click:
Exit Sub
Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click
Exit Sub

End Sub