the following code does not work: vbYesNoCancel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Louis - 29 May 2008 14:04 GMT

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.

Reply to this Message

Tammy F - 29 May 2008 14:21 GMT

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

Reply to this Message

Louis - 29 May 2008 15:06 GMT

Tammy,

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

> 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.
[quoted text clipped - 26 lines]
>
> End Sub

Reply to this Message

Douglas J. Steele - 29 May 2008 15:08 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>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.
[quoted text clipped - 26 lines]
>
> End Sub

Reply to this Message