the following code does not work: vbYesNoCancel

[Louis]

Private Sub Command24_Click()
On Error GoTo Err_Command24_Click


MsgBox "You are about to make changes to tables. Are you sure you want to
continue?" _
, vbYesNoCancel, "UPDATE TABLES"

Select Case vbYesNoCancel

Case vbNo
Exit Sub
Case vnCancel
Exit Sub
Case vbYes
DoCmd.OpenQuery "q M_SD MEMOS ARCHIVE", acViewNormal
DoCmd.OpenQuery "q M_ALLOCATE INVENTORY S&D"
DoCmd.OpenQuery "q DELETE A_SHIP & DEBIT STAGE 2B", acViewNormal

End Select


Exit Sub

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub
' I want the queries to run if "Yes" is selected. I can get the queries to
run if Yes, No, or Cancel is selected. I just want them to run with Yes and
exit if else.

 

[Tammy F]

I would do this with an if statement - but thats because I never learned
about selectcase statement. I will be interested to read the answer too.
Here's my solution:
Private Sub Command24_Click()
On Error GoTo Err_Command24_Click

Dim msgboxans As String
msgboxans = MsgBox("You are about to make changes to tables. Are you sure
you want to continue?", vbYesNoCancel, "UPDATE TABLES")
If msgboxans = vbNo Then
Exit Sub
ElseIf msgboxans = vbCancel Then
Exit Sub
ElseIf msgboxans Then
DoCmd.OpenQuery "q M_SD MEMOS ARCHIVE", acViewNormal
DoCmd.OpenQuery "q M_ALLOCATE INVENTORY S&D"
DoCmd.OpenQuery "q DELETE A_SHIP & DEBIT STAGE 2B", acViewNormal

End If


Exit Sub

Exit_Command24_Click:
Exit Sub

Err_Command24_Click:
MsgBox Err.Description
Resume Exit_Command24_Click

End Sub

 

[Louis]

Tammy,

Thanks alot. The details are what kill you in this business. It worked
beautifully.
Louis

 

[Douglas J. Steele]

The MsgBox function returns an Integer, not a String, so that should be

Dim msgboxans As Integer

However, it's probably simpler to use

If MsgBox("You are about to make changes to tables. " & _
"Are you sure you want to continue?", vbYesNoCancel, "UPDATE TABLES") =
vbYes Then
DoCmd.OpenQuery "q M_SD MEMOS ARCHIVE", acViewNormal
DoCmd.OpenQuery "q M_ALLOCATE INVENTORY S&D"
DoCmd.OpenQuery "q DELETE A_SHIP & DEBIT STAGE 2B", acViewNormal
Else
Exit Sub
End If

or

Select Case MsgBox("You are about to make changes to tables. " & _
"Are you sure you want to continue?", vbYesNoCancel, "UPDATE TABLES")
Case vbYes
DoCmd.OpenQuery "q M_SD MEMOS ARCHIVE", acViewNormal
DoCmd.OpenQuery "q M_ALLOCATE INVENTORY S&D"
DoCmd.OpenQuery "q DELETE A_SHIP & DEBIT STAGE 2B", acViewNormal
Case Else
Exit Sub
End Select