MsgBox DialogResult

[Toco]

Hello, I have msgbox using VBYesNo as buttonstyle. My goal is is, if the
user select Yes, then .... (do something), or if they select no, Exit Sub

Here is sample code

Dim strSQL As String, rsOne As ADODB.Recordset
strSQL = "Select custID from tbCustomerLetters"
rsOne.Open strSQL, CurrentProject.Connection, adOpenStatic,
adLockReadOnly
With rsOne
'If .RecordCount = 0 Then MsgBox "No customer were processed
in your query", 64 + 0, "No Letters printed"
''Go To EndRoutinue
MsgBox ("Continue?"), vbYesNo, ("There are " & .RecordCount & "
letters to be printed")

Here is where I need to add code to perform action based on user input. I
know you can do this in VB environment with the DialogResult property by
passing the argument, but I cannot figure out the way to perform this in VBA.
Any ideas?

 

[MikeB]

Hello, I have msgbox using VBYesNo as buttonstyle. My goal is is, if the
user select Yes, then .... (do something), or if they select no, Exit Sub

Here is sample code

Dim strSQL As String, rsOne As ADODB.Recordset
strSQL = "Select custID from tbCustomerLetters"
rsOne.Open strSQL, CurrentProject.Connection, adOpenStatic,
adLockReadOnly
With rsOne
'If .RecordCount = 0 Then MsgBox "No customer were processed
in your query", 64 + 0, "No Letters printed"
''Go To EndRoutinue
MsgBox ("Continue?"), vbYesNo, ("There are " & .RecordCount & "
letters to be printed")

Here is where I need to add code to perform action based on user input. I
know you can do this in VB environment with the DialogResult property by
passing the argument, but I cannot figure out the way to perform this in VBA.
Any ideas?

One Way:

Private Sub test()
Dim strSQL As String, rsOne As ADODB.Recordset
strSQL = "Select custID from tbCustomerLetters"
rsOne.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
With rsOne
If .RecordCount = 0 Then
MsgBox "No customer were processed in your query", 64 + 0, "No Letters
printed"
GoTo EndRoutinue
End If
If MsgBox("Continue?", vbYesNo, "There are " & .RecordCount & "letters to be
printed") = vbYes Then
' do something here
Else
' do something different here
End If
End With
Exit Sub
EndRoutinue:
'goto handler
End Sub

Another Way:

Private Sub test2()
Dim strSQL As String, rsOne As ADODB.Recordset
strSQL = "Select custID from tbCustomerLetters"
rsOne.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
With rsOne
If .RecordCount = 0 Then
MsgBox "No customer were processed in your query", 64 + 0, "No Letters
printed"
GoTo EndRoutinue
End If

Select Case MsgBox("Continue?", vbYesNo, "There are " & .RecordCount &
"letters to be printed")
Case vbYes
' do something here
Case vbNo
' do something different here
Case Else
' do something here if its the unexpected
End Select
End With
Exit Sub
EndRoutinue:
'goto handler
End Sub

 

[Graham Mandeno]

Hi Toco

MsgBox is a Function, and so it returns a result, but you have to call it
the right way and do something with the result otherwise it is discarded.
You are calling it like a Sub, so the result gets lost:
MsgBox "Continue?", vbYesNo

To use the result, you can eith assign it to a variable:
lngResponse = MsgBox( "Continue?", vbYesNo )

or you can just check it immediately with an If statement:
If MsgBox( "Continue?", vbYesNo ) = vbYes then
...