MsgBox

[John]

Hi,

On a form I have a command button which invokes a maketable query from
selections on the form.
This works great but I would like to have a messagebox prompt the user for
instructions.
If the user click OK then the make table quesry is run, if the user clicks
cancel I would like the routine to stop!

Could some one please take a look at the code below and tell me why I cannot
make the vbOkCal buttons work.

Private Sub MakeTable_Click()

MsgBox "Select Batch will create a new batch of labels ready to print,
this overwrites all previous batch labels created. Do you want to
continue?", vbOKCancel, "Batch Print Selection"
If vbCancel Then
exit sub
else
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMakeBatch"
End If
End sub

Regards

John

 

[Beetle]

By the time you get to this line;

If vbCancel Then

the message box has already been acted upon, so it's too late to capture
the Cancel selection. Put the message box inside the If..Then statement
like;

Dim strMsg as String
strMsg = "Select Batch will create a new batch of labels ready to print,
this overwrites all previous batch labels created. Do you want to
continue?"

If MsgBox (strMsg, vbOKCancel, "Batch Print Selection") = vbCancel Then
Exit Sub
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMakeBatch"
End If

 

[Dirk Goldgar]

Hi,

On a form I have a command button which invokes a maketable query from
selections on the form.
This works great but I would like to have a messagebox prompt the user for
instructions.
If the user click OK then the make table quesry is run, if the user clicks
cancel I would like the routine to stop!

Could some one please take a look at the code below and tell me why I
cannot make the vbOkCal buttons work.

Private Sub MakeTable_Click()

MsgBox "Select Batch will create a new batch of labels ready to print,
this overwrites all previous batch labels created. Do you want to
continue?", vbOKCancel, "Batch Print Selection"
If vbCancel Then
exit sub
else
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMakeBatch"
End If
End sub

MsgBox is a function, so you need to capture and evaluate its return code to
see which of the possible values it is. Try this:

'----- start of revised code -----
Private Sub MakeTable_Click()

On Error GoTo Err_Handler

If MsgBox( _
"Select Batch will create a new batch of labels " & _
"ready to print, this overwrites all previous batch " & _
"labels created. Do you want to continue?", _
vbOKCancel, _
"Batch Print Selection") _
= vbOK _
Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMakeBatch"
End If

Exit_Point:
DoCmd.SetWarnings True
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End sub
'----- end of revised code -----

Note that I also added error handling and a line to turn warnings back on,
regardless of whether or not an error occurs. The way you had it, you would
be leaving warnings turned off indefinitely.

 

[John]

Thanks Guys,
Worked a treat

Regards

john