Syntax for msgbox Function

[sickenhoofer]

I am attempting to create a message box function to make sure that when a last name is changed, it is really the desired action. My syntax is below.

Private Sub ProviderLName_C hange()
Dim bytReply As Byte
MsgBox "You have changed the LAST NAME for this provider." & vbCr & "Is this correct?", vbYesNo, "DATA CHANGE DETECTED"
If bytReply = 7 Then
Forms!frmProvid erMainDataEntry !ProviderLName. Undo
Else
End If
End Sub

Upon testing, I changed the name. The message box appears. However, it does not undo the change. Can anyone tell me what I have done wrong?

[sickenhoofer]

Nevermind

Sorry, I finally figured it out. I used the following.

Private Sub ProviderLName_C hange()
Dim bytReply As Byte
bytReply = MsgBox("You have changed the LAST NAME for this provider." & vbCr & "Is this correct?", vbYesNo, "DATA CHANGE DETECTED")
If bytReply = 7 Then
Forms!frmProvid erMainDataEntry !ProviderLName. Undo
Else
End If
End Sub

[missinglinq]

I believe you'll find out that your message box will fire with every letter you enter in the textbox, because that's when the OnChange event fires.It will also fire when you're first entering a name. I don't believe either of these are your intent. Try this:

Private Sub ProviderLName_BeforeUpdate(Cancel As Integer)

Dim bytReply As Integer

  If Me.ProviderLName.OldValue <> Me.ProviderLName.Value Then
    bytReply = MsgBox("You have changed the LAST NAME for this provider." & vbCr & "Is this correct?", vbYesNo, "DATA CHANGE DETECTED")
     If bytReply = vbNo Then
       Me.ProviderLName.Undo
       Cancel = True
     End If
  End If

End Sub

[sickenhoofer]

That is perfect. Thank you!

In my testing, I was only changing 1 character, just to see if it was working. So I did not catch the problem. Again, much appreciated!

[missinglinq]

Glad we could help!

Linq ;0)>

[ADezii]

Sorry, I finally figured it out. I used the following.

Private Sub ProviderLName_C hange()
Dim bytReply As Byte
bytReply = MsgBox("You have changed the LAST NAME for this provider." & vbCr & "Is this correct?", vbYesNo, "DATA CHANGE DETECTED")
If bytReply = 7 Then
Forms!frmProvid erMainDataEntry !ProviderLName. Undo
Else
End If
End Sub

Just a couple of minor points to mention:

1. The Return Value of the MsgBox() Function is an Integer.
2. You may wish to make the Message Box a little more User Friendly and visually appealing, and at the same time the code more readable, as in:
   

   Private Sub ProviderLName_BeforeUpdate(Cancel As Integer)
   Dim intReply As Integer
   Dim strMsg As String
   Dim intButtons As Integer
   
   strMsg = "You have changed the LAST NAME for the Provider [" & Me![ProviderLName].OldValue & _
            "]." & vbCrLf & vbCrLf & "Is this correct?"
   intButtons = vbQuestion + vbYesNo + vbDefaultButton1
   
   If Me![ProviderLName].OldValue <> Me!ProviderLName Then
     intReply = MsgBox(strMsg, intButtons, "DATA CHANGE DETECTED")
        If intReply = vbNo Then
          Me.ProviderLName.Undo
            Cancel = True
        End If
   End If
   End Sub

[sickenhoofer]

Not Working for me

ADezii . . . I tried this (as shown below), but I can't get it to work. Can you possibly tell me what I'm doing wrong?

I used a different name field so that I could test this before replacing the code on the last name field (working code).

Used this syntax:
Private Sub ProviderMiddleN ame_BeforeUpdat e(Cancel As Integer)
Dim intReply As Integer
Dim strMsg As String
Dim intButtons As Integer

strMsg = "You have changed the LAST NAME for the provider [" & Me![ProviderMiddleN ame].OldValue & _
"]." & vbCrLf & vbCrLf & "Is this correct?"

intButtons = vbQuestion + vbYesNo + vbDefaultButton 1

If Me!ProviderMidd leName.OldValue <> Me!ProviderMidd leName Then
intReply = MsgBox(strMsg, intButtons, "DATA CHANGE DETECTED")
If intReply = vbNo Then
Me!ProviderMidd leName.Undo
Cancel = True
End If
End If
End Sub

I checked for errors, but I'm just not seeing the problem. The result is that I can change or delete data and it does not respond at all.

I appreciate your feedback.

[ADezii]

ADezii . . . I tried this (as shown below), but I can't get it to work. Can you possibly tell me what I'm doing wrong?

I used a different name field so that I could test this before replacing the code on the last name field (working code).

Used this syntax:
Private Sub ProviderMiddleN ame_BeforeUpdat e(Cancel As Integer)
Dim intReply As Integer
Dim strMsg As String
Dim intButtons As Integer

strMsg = "You have changed the LAST NAME for the provider [" & Me![ProviderMiddleN ame].OldValue & _
"]." & vbCrLf & vbCrLf & "Is this correct?"

intButtons = vbQuestion + vbYesNo + vbDefaultButton 1

If Me!ProviderMidd leName.OldValue <> Me!ProviderMidd leName Then
intReply = MsgBox(strMsg, intButtons, "DATA CHANGE DETECTED")
If intReply = vbNo Then
Me!ProviderMidd leName.Undo
Cancel = True
End If
End If
End Sub

I checked for errors, but I'm just not seeing the problem. The result is that I can change or delete data and it does not respond at all.

I appreciate your feedback.

Rechecked the code again at my end, and all seems well. Puzzling... The only thing that pops up is your absolute Reference to the Control, but I cannot see how that would have a bearing on the code context. Let me get my thinking cap back on and I'll return later.

[sickenhoofer]

I tweaked it a little and was able to get it to work. Would you mind looking at this and let me know if you can see any problem with it? I am pretty new to VBA and want to make sure I don't have something out of order.

Private Sub ProviderMiddleN ame_BeforeUpdat e(Cancel As Integer)
Dim intReply As Integer
Dim strMsg As String
Dim intButtons As Integer

intButtons = vbYesNo

strMsg = "You have changed the LAST NAME for the provider (" & Me![ProviderMiddleN ame].OldValue & _
")." & vbCrLf & vbCrLf & "Is this correct?"

intReply = MsgBox(strMsg, intButtons, "DATA CHANGE DETECTED")


If Me![ProviderMiddleN ame].OldValue <> Me!ProviderMidd leName Then
If intReply = vbNo Then
Me!ProviderMidd leName.Undo
Cancel = True
End If
End If
End Sub

[ADezii]

I tweaked it a little and was able to get it to work. Would you mind looking at this and let me know if you can see any problem with it? I am pretty new to VBA and want to make sure I don't have something out of order.

Private Sub ProviderMiddleN ame_BeforeUpdat e(Cancel As Integer)
Dim intReply As Integer
Dim strMsg As String
Dim intButtons As Integer

intButtons = vbYesNo

strMsg = "You have changed the LAST NAME for the provider (" & Me![ProviderMiddleN ame].OldValue & _
")." & vbCrLf & vbCrLf & "Is this correct?"

intReply = MsgBox(strMsg, intButtons, "DATA CHANGE DETECTED")


If Me![ProviderMiddleN ame].OldValue <> Me!ProviderMidd leName Then
If intReply = vbNo Then
Me!ProviderMidd leName.Undo
Cancel = True
End If
End If
End Sub

Looks good to me, sickenhoofer. The only suggestion that I could make is that if you are only using the Yes/No Button Option, then there is no need for the Variable Declaration and Assignment:

Private Sub ProviderMiddleName_BeforeUpdate(Cancel As Integer)
Dim intReply As Integer
Dim strMsg As String
    
intButtons = vbYesNo
    
strMsg = "You have changed the LAST NAME for the provider (" & Me![ProviderMiddleName].OldValue & _
         ")." & vbCrLf & vbCrLf & "Is this correct?"
    
intReply = MsgBox(strMsg, vbYesNo, "DATA CHANGE DETECTED")
    
If Me![ProviderMiddleName].OldValue <> Me!ProviderMiddleName Then
  If intReply = vbNo Then
    Me!ProviderMiddleName.Undo
      Cancel = True
  End If
End If
End Sub