if then msgbox

[Jake F]

I'm working on a survey database and when the user gets to the last question
i want to ask if they would like to review their answers or save and submit
the survey. I'd like to do it through a msgbox being that i haven't created
one that is more complicated than simple message display and an okay button.
Using the VBA help for msgbox i found the following code and adapted it to my
procedure. I cannot figure out how to change the text for the buttons and
also it is going to the wrong part in the procedure after update the combo so
I'm not sure how to specify the response is for the msgbox not the combo
response. Thanks. Also I'm fairly sure I don't need that many if/then
statements, but was trying to overdue to fix the If Response error.

Private Sub Combo14_AfterUpdate()
On Error GoTo Err_Combo14_AfterUpdate

Dim Msg, Style, Title, Ctxt, Response, MyString

Msg = "Do you want to review your answers or save and submit?" ' Define
message.
Style = vbYesNo + vbDefaultButton1 ' Define buttons.
Title = "Safety Survey" ' Define title.
Response = MsgBox(Msg, Style, Title)

If Rspns = "Yes" Or Rspns = "No" And QstnNbr < 26 Then
DoCmd.GoToRecord , , acNext
QstnText.SetFocus
End If

If Rspns = "Unanswered" And QstnNbr < 26 Then
MsgBox "All questions must be answered.", vbOKOnly, "Safety Survey"
End If

If Rspns = "Yes" And QstnNbr = 26 Then
DoCmd.OpenForm frmNotes

If QstnNbr = 26 And Rspns = "No" Then
MsgBox Msg, Style, Title, , Ctxt
End If

If Response = vbYes Then ' User chose Yes.
MyString = "Review Answers" ' Perform some action.
DoCmd.GoToRecord , QstnNbr, acFirst
Else ' User chose No.
MyString = "Save and Submit" ' Perform some action.
DoCmd.Save acReport, "Rpt"
DoCmd.Close
Forms!fmnumain.Visible = True
End If

Exit_Combo14_AfterUpdate:
Exit Sub
Err_Combo14_AfterUpdate:
MsgBox Error$
Resume Exit_Combo14_AfterUpdate
End Sub

 

[pietlinden]

I'm working on a survey database and when the user gets to the last question
i want to ask if they would like to review their answers or save and submit
the survey.  I'd like to do it through a msgbox being that i haven't created
one that is more complicated than simple message display and an okay button.  
Using the VBA help for msgbox i found the following code and adapted it to my
procedure.  I cannot figure out how to change the text for the buttons and
also it is going to the wrong part in the procedure after update the combo so
I'm not sure how to specify the response is for the msgbox not the combo
response.  Thanks.  Also I'm fairly sure I don't need that many if/then
statements, but was trying to overdue to fix the If Response error.

Private Sub Combo14_AfterUpdate()
On Error GoTo Err_Combo14_AfterUpdate

Dim Msg, Style, Title, Ctxt, Response, MyString

Msg = "Do you want to review your answers or save and submit?"    'Define
message.
Style = vbYesNo + vbDefaultButton1 ' Define buttons.
Title = "Safety Survey"    ' Define title.
Response = MsgBox(Msg, Style, Title)

   If Rspns = "Yes" Or Rspns = "No" And QstnNbr < 26 Then
   DoCmd.GoToRecord , , acNext
   QstnText.SetFocus
   End If

   If Rspns = "Unanswered" And QstnNbr < 26 Then
     MsgBox "All questions must be answered.", vbOKOnly, "Safety Survey"
   End If

   If Rspns = "Yes" And QstnNbr = 26 Then
     DoCmd.OpenForm frmNotes

 If QstnNbr = 26 And Rspns = "No" Then
     MsgBox Msg, Style, Title, , Ctxt
   End If

If Response = vbYes Then    ' User chose Yes.
     MyString = "Review Answers"    ' Perform some action.
     DoCmd.GoToRecord , QstnNbr, acFirst
   Else    ' User chose No.
     MyString = "Save and Submit"    ' Perform some action.
     DoCmd.Save acReport, "Rpt"
     DoCmd.Close
     Forms!fmnumain.Visible = True
   End If

Exit_Combo14_AfterUpdate:
    Exit Sub
Err_Combo14_AfterUpdate:
    MsgBox Error$
    Resume Exit_Combo14_AfterUpdate
End Sub

Maybe this is an end-around answer... download At Your Survey... and
save yourself a lot of trouble.

Second... the constants available for messageboxes are *not* equal to
text values but *integers*. Test it yourself. Open a code module,
show the debug window, and type any of the following. If you want,
add them...
?vbyes
6
?vbno
7
?vbtrue
-1
?vbfalse
0

?vbyes+vbno
13

So this...

If Rspns = "Yes"

can never be true, no matter what. You are trying to compare a text
value to an integer which will be false every time.

one thing you can do is to change ="Yes" to =vbYes and this will get
you closer to where you want to be.