By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Syntax for msgbox Function

sickenhoofer
P: 19
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_Change()
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!frmProviderMainDataEntry!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?
Jul 16 '09 #1
Share this Question
Share on Google+
9 Replies


sickenhoofer
P: 19
Sorry, I finally figured it out. I used the following.

Private Sub ProviderLName_Change()
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!frmProviderMainDataEntry!ProviderLName.Undo
Else
End If
End Sub
Jul 16 '09 #2

missinglinq
Expert 2.5K+
P: 3,532
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ProviderLName_BeforeUpdate(Cancel As Integer)
  2.  
  3. Dim bytReply As Integer
  4.  
  5.   If Me.ProviderLName.OldValue <> Me.ProviderLName.Value Then
  6.     bytReply = MsgBox("You have changed the LAST NAME for this provider." & vbCr & "Is this correct?", vbYesNo, "DATA CHANGE DETECTED")
  7.      If bytReply = vbNo Then
  8.        Me.ProviderLName.Undo
  9.        Cancel = True
  10.      End If
  11.   End If
  12.  
  13. End Sub
Jul 17 '09 #3

sickenhoofer
P: 19
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!
Jul 17 '09 #4

missinglinq
Expert 2.5K+
P: 3,532
Glad we could help!

Linq ;0)>
Jul 17 '09 #5

ADezii
Expert 5K+
P: 8,679
@sickenhoofer
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:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub ProviderLName_BeforeUpdate(Cancel As Integer)
    2. Dim intReply As Integer
    3. Dim strMsg As String
    4. Dim intButtons As Integer
    5.  
    6. strMsg = "You have changed the LAST NAME for the Provider [" & Me![ProviderLName].OldValue & _
    7.          "]." & vbCrLf & vbCrLf & "Is this correct?"
    8. intButtons = vbQuestion + vbYesNo + vbDefaultButton1
    9.  
    10. If Me![ProviderLName].OldValue <> Me!ProviderLName Then
    11.   intReply = MsgBox(strMsg, intButtons, "DATA CHANGE DETECTED")
    12.      If intReply = vbNo Then
    13.        Me.ProviderLName.Undo
    14.          Cancel = True
    15.      End If
    16. End If
    17. End Sub
Jul 17 '09 #6

sickenhoofer
P: 19
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 ProviderMiddleName_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![ProviderMiddleName].OldValue & _
"]." & vbCrLf & vbCrLf & "Is this correct?"

intButtons = vbQuestion + vbYesNo + vbDefaultButton1

If Me!ProviderMiddleName.OldValue <> Me!ProviderMiddleName Then
intReply = MsgBox(strMsg, intButtons, "DATA CHANGE DETECTED")
If intReply = vbNo Then
Me!ProviderMiddleName.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.
Jul 17 '09 #7

ADezii
Expert 5K+
P: 8,679
@sickenhoofer
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.
Jul 17 '09 #8

sickenhoofer
P: 19
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 ProviderMiddleName_BeforeUpdate(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![ProviderMiddleName].OldValue & _
")." & vbCrLf & vbCrLf & "Is this correct?"

intReply = MsgBox(strMsg, intButtons, "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
Jul 17 '09 #9

ADezii
Expert 5K+
P: 8,679
@sickenhoofer
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub ProviderMiddleName_BeforeUpdate(Cancel As Integer)
  2. Dim intReply As Integer
  3. Dim strMsg As String
  4.  
  5. intButtons = vbYesNo
  6.  
  7. strMsg = "You have changed the LAST NAME for the provider (" & Me![ProviderMiddleName].OldValue & _
  8.          ")." & vbCrLf & vbCrLf & "Is this correct?"
  9.  
  10. intReply = MsgBox(strMsg, vbYesNo, "DATA CHANGE DETECTED")
  11.  
  12. If Me![ProviderMiddleName].OldValue <> Me!ProviderMiddleName Then
  13.   If intReply = vbNo Then
  14.     Me!ProviderMiddleName.Undo
  15.       Cancel = True
  16.   End If
  17. End If
  18. End Sub
Jul 17 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.