msgbox code problems

jibb

Registered User.
Local time
Today, 15:42
Joined
Dec 1, 2011
Messages
93
I have got a form to change information on a table. There is a button to unlock all text boxes and another button to save the changes. When I click the save button I want it to show a vbYesNoCancel message box, then, if no is clicked, me.undo and lock all text boxes, if yes is clicked, save and lock all text boxes. With the code I'm using it doesn't save the changes when I click yes.

Can anyone help?

Code:
Private Sub Command55_Click()
MsgBox "Do you want to save the changes?", vbYesNoCancel, "Confirm Save"
    If vbNo Then
        Me.Undo
    End If
 
    If vbYes Then
        Dim ctrl As Control
 
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is TextBox Then
                ctrl.Locked = True
                End If
 
Next
End If
Me.Command56.Enabled = False
Combo44.SetFocus
End Sub
 
to save the record stick in :

If Me.Dirty Then
Me.Dirty = False
End If
 
Thanks - where would I put it in my code to save the record and lock all text boxes?
 
looking at the code you posted, the "If vbYes Then" line checks to see if the user wanted to save the record. So I'd pop it in just after that.
 
It now looks like this:

Code:
Private Sub Command55_Click()
MsgBox "Do you want to save the changes?", vbYesNoCancel + vbQuestion + vbDefaultButton1, "Confirm Save"
    If vbNo Then
        Me.Undo
    End If
    
    If vbYes Then
    
        If Me.Dirty Then
        Me.Dirty = False
        End If
        Dim ctrl As Control
        
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is TextBox Then
                ctrl.Locked = True
                End If
Next
End If
Me.Command56.Enabled = False
Combo44.SetFocus
Me.Command54.Enabled = True
End Sub

But when I click yes it just reverts to what the record was like before...
 
Because you don't use the result of your messagebox your code is not working as you expect it to work. Put the result of the messagebox in a variable or use it like:
Code:
Private Sub Command55_Click()
   if MsgBox("Do you want to save the changes?", vbYesNoCancel, "Confirm Save") = vbNo then
        Me.Undo
   Else 'because there are only two choices possible no need for further testing
        Dim ctrl As Control
 
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is TextBox Then
                ctrl.Locked = True
            End If
        Next
   End If
Me.Command56.Enabled = False
Combo44.SetFocus
End Sub
vbYes (value = 6) and vbNo (value = 7) are constants in VBA.
Because the constants are not null they will evaluate as True the way you use them.
 
Last edited:
Because you don't use the result of your messagebox your code is not working as you expect it to work. Put the result of the messagebox in a variable or use it like:
Code:
Private Sub Command55_Click()
   if MsgBox("Do you want to save the changes?", vbYesNoCancel, "Confirm Save") = vbNo then
        Me.Undo
   Else 'because there are only two choices possible no need for further testing
        Dim ctrl As Control
 
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is TextBox Then
                ctrl.Locked = True
            End If
        Next
   End If
Me.Command56.Enabled = False
Combo44.SetFocus
End Sub
vbYes (value = 6) and vbNo (value = 7) are constants in VBA.
Because the constants are not null they will evaluate as True the way you use them.


oooh good spot. I thought Jibb's code looked odd but didn't read it through.
However... careful - That's a vbYesNoCancel not a vbYesNo in there.
The "Else" statements will execute if a user clicks Yes or Cancel.
Test for =vbYes instead of =vbNo so the Else will apply for No & Cancel.

However, if in future you need to perform different actions for yes, no and cancel you could use Select Case instead.
 
Got it!

Code:
If MsgBox("Do you want to save the changes?", vbYesNo, "Confirm Save") = vbNo Then
        Me.Undo
   ElseIf Me.Dirty Then
        Me.Dirty = False
End If

Thanks for your help.
 

Users who are viewing this thread

Back
Top Bottom