MSGBOX options

[PaulO]

Whenever I create a MSGBOX command within a macro the only button option available to the user is "OK". The only way for the user to cancel the Macro is to press "CTRL" and "Break" keys together.

What I want is for both "OK" and "Cancel" options to appear ... is there some way of getting this within MSGBOX, or should I instead be considering creating a pop-up form that enables me to provide multiple optional actions with customized buttons?

 

[KenHigg]

I suggest you make the leap to the vba version of the msgbox function.

 

[PaulO]

I'm all ears, Ken ...

I'm not a VB expert, to say the least. Have you some example code I could use?

 

[KenHigg]

In the vba code editor type in 'msgbox' then put the cursor back in the in the word somewhere and hit F4 and you should get the help screen.

 

[gemma-the-husky]

are you sure you cant do this in your macro

even in code, you have to add the buttons by changing the msgbox statement

you see, the default button is vbokonly

you can add other buttons/change this in various ways

eg
vbokcancel
vbyesno are self explanatory

you can add other vb constants to set the defaultbutton

vbyesno+vbdefaultbutton2

you can add a "critical" or other special symbol

vbyesno+vbdefaultbutton2+vbcritical

 

[PaulO]

OK I've started out with some VB code-building but need some help as this is clearly not correct ...

MSGBOX ("Do you want to delete this Record?",vbOKCancel,"DELETE RECORD",,)

What I'm trying to do is have a command button driving a message box that gives the user the choice of whether to Delete a record (OK) or not (Cancel).

OK will drive an additional RunQuery command. I want Cancel to stop the Macro completely.

I'm just strapped for VB code knowledge I'm afraid!

 

[KenHigg]

Give me a second to whip up a sample db for you too look at...

 

[PaulO]

This is an example wherein I wish to insert an OK/Cancel command before running the Delete_SAGE_Tables macro:-

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click
Dim stDocName As String
stDocName = "Delete_SAGE_Tables"
DoCmd.RunMacro stDocName
Exit_Command29_Click:
Exit Sub
Err_Command29_Click:
MsgBox Err.description
Resume Exit_Command29_Click

End Sub

 

[KenHigg]

See if this helps...

 

[KenHigg]

See if this works:

Private Sub Command29_Click()
   On Error GoTo Err_Command29_Click
   Dim stDocName As String
   stDocName = "Delete_SAGE_Tables"
    
   If MsgBox("Some message?", vbYesNo + vbQuestion, "Some other text...") = vbYes Then
      DoCmd.RunMacro stDocName
   End if
   
Exit_Command29_Click:
   Exit Sub

Err_Command29_Click:
   MsgBox Err.description
   Resume Exit_Command29_Click

End Sub

 

[PaulO]

Your example seems to dump a value into a text box based on which button is clicked ...

In this case I want to run a subsequent RunQuery command after an OK or Yes command ...

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click
This is maybe where I want the OK/Cancel to run? If OK I want to continue on to the next part of the macro if Cancel then abort macro
Dim stDocName As String
stDocName = "Delete_SAGE_Tables"
DoCmd.RunMacro stDocName
Exit_Command29_Click:
Exit Sub
Err_Command29_Click:
MsgBox Err.description
Resume Exit_Command29_Click

End Sub

 

[PaulO]

See if this works:

Private Sub Command29_Click()
   On Error GoTo Err_Command29_Click
   Dim stDocName As String
   stDocName = "Delete_SAGE_Tables"
 
   If MsgBox("Some message?", vbYesNo + vbQuestion, "Some other text...") = vbYes Then
      DoCmd.RunMacro stDocName
   End if
 
Exit_Command29_Click:
   Exit Sub
 
Err_Command29_Click:
   MsgBox Err.description
   Resume Exit_Command29_Click
 
End Sub

Do I need an "Else" command also?

 

[PaulO]

This seems like it might be working ...

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click
Dim stDocName As String
If MsgBox("Do you really want to delete all the SAGE data tables?", vbYesNo + vbQuestion, "Some other text...") = vbYes Then
stDocName = "Delete_SAGE_Tables"
DoCmd.RunMacro stDocName
End If
Exit_Command29_Click:
Exit Sub
Err_Command29_Click:
MsgBox Err.description
Resume Exit_Command29_Click

End Sub

A couple of questions though:-
1) Does it matter that I've inserted the command in a slightly different place to your suggestion?
2) Where can I place a text message that says that either a) the macro has been aborted or b) The macro has been run

 

[KenHigg]

a. Nah...

b.

Private Sub Command29_Click()
   On Error GoTo Err_Command29_Click
   Dim stDocName As String

   If MsgBox("Do you really want to delete all the SAGE data tables?", vbYesNo + vbQuestion, "Some other text...") = vbYes Then
      stDocName = "Delete_SAGE_Tables"
      DoCmd.RunMacro stDocName
      msgbox "The macro has run"
      Goto Exit_Command29_Click:
   Else
      msgbox "The macro has been aborted"
   End if

Exit_Command29_Click:
   Exit Sub
Err_Command29_Click:
   MsgBox Err.description
   Resume Exit_Command29_Click

End Sub

 

[PaulO]

OK ... I think we're just about done, Ken!

Private Sub Command29_Click()
On Error GoTo Err_Command29_Click
Dim stDocName As String
If MsgBox("Do you really want to delete all the SAGE data tables?", vbYesNo + vbQuestion, "Deleting SAGE Data Tables") = vbYes Then
stDocName = "Delete_SAGE_Tables"
DoCmd.RunMacro stDocName
GoTo Exit_Command29_Click:
Else
MsgBox "No tables were deleted"
End If
Exit_Command29_Click:
Exit Sub
Err_Command29_Click:
MsgBox Err.description
Resume Exit_Command29_Click

End Sub

 

[KenHigg]

Glad you have it working -