Creating a custom message box when the input mask in a field is not followed.

[louie310]

I am working on a Access data base as a school project. The data base has forms with fields that need to be filled out. The forms currently are set up where certain fields that are required have custom message boxes if they are omitted.
The one problem I am having is to create a custom message box when the input mask that I have specified for phone number is not followed.
'! \ (999")" 000\-0000;;_' I have tried to insert this mask in the validation rule hoping to create a link to the validation text, I am not having any luck.
Thanks,
Lou

[pbmods]

Heya, Lou. Welcome to TSDN!

I'm going to go ahead and move this thread to the Access forum, where our resident Experts will be better able to help you.

[ADezii]

I am working on a Access data base as a school project. The data base has forms with fields that need to be filled out. The forms currently are set up where certain fields that are required have custom message boxes if they are omitted.
The one problem I am having is to create a custom message box when the input mask that I have specified for phone number is not followed.
'! \ (999")" 000\-0000;;_' I have tried to insert this mask in the validation rule hoping to create a link to the validation text, I am not having any luck.
Thanks,
Lou

Assuming the Phone Number Field is the only Field on your Form for which an Input Mask has been defined, to create a Custom Message Box for an Input Mask Violation on this Field, place the following code in your Form's Error() Event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2279 Then      'Input Mask Violation
  MsgBox "You must enter the Area Code + Phone Number as in: [phone]", vbExclamation, "Invalid Entry"
  Response = acDataErrContinue
Else
  Rersponse = acDataErrDisplay      'let Access handle the Error
End If
End Sub

[louie310]

Assuming the Phone Number Field is the only Field on your Form for which an Input Mask has been defined, to create a Custom Message Box for an Input Mask Violation on this Field, place the following code in your Form's Error() Event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2279 Then      'Input Mask Violation
  MsgBox "You must enter the Area Code + Phone Number as in: [phone]", vbExclamation, "Invalid Entry"
  Response = acDataErrContinue
Else
  Rersponse = acDataErrDisplay      'let Access handle the Error
End If
End Sub

Thank you for your reply. This code works fine if you were closing the form, but there is a SAVE button that updates the table. I can not hit the SAVE button and have my message box show up. A generic Access message box pops up and tells me that the input mask of ……… has been violated. My professor has requested a custom message box if possible. Unfortunately he is not an Access man and I don’t know if that is possible.

[ADezii]

Thank you for your reply. This code works fine if you were closing the form, but there is a SAVE button that updates the table. I can not hit the SAVE button and have my message box show up. A generic Access message box pops up and tells me that the input mask of ……… has been violated. My professor has requested a custom message box if possible. Unfortunately he is not an Access man and I don’t know if that is possible.

If you are using generic, wizard-generated code to SAVE the Record, it won't make a difference, the Form's Error() Event will still intercept the Input Validation Error and display a Custom Message Box. Are you using custom code to write to a Table? If you are, post the code and I'll see if I can figure a work-a-round.

[louie310]

If you are using generic, wizard-generated code to SAVE the Record, it won't make a difference, the Form's Error() Event will still intercept the Input Validation Error and display a Custom Message Box. Are you using custom code to write to a Table? If you are, post the code and I'll see if I can figure a work-a-round.

I'm working with three other students on this project. I am not the one that wrote the code for the SAVE button, but this is a copy of all the code that I could find that deals with the SAVE button:

Private Sub SAVE_BUTTON_Click()
On Error GoTo Err_SAVE_BUTTON_Click
 
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_SAVE_BUTTON_Click:
  RetVal = MsgBox("Invoice Updated", vbOKOnly, "System Alert")
 
  Dim stDocName As String
  Dim stLinkCriteria As String
 
  DoCmd.Close
  stDocName = "Edit Switchboard"
  DoCmd.OpenForm stDocName, , , , acFormAdd
Exit Sub
 
Err_SAVE_BUTTON_Click:
  MsgBox Err.Description
  Resume Exit_SAVE_BUTTON_Click
End Sub

Private Sub Image17_Click()
On Error GoTo Err_SAVE_BUTTON_Click
 
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_SAVE_BUTTON_Click:
  RetVal = MsgBox("Employee Updated", vbOKOnly, "System Alert")
 
  Dim stDocName As String
  Dim stLinkCriteria As String
 
  DoCmd.Close
  stDocName = "Edit Switchboard"
  DoCmd.OpenForm stDocName, , , , acFormAdd
    Exit Sub
 
Err_SAVE_BUTTON_Click:
  MsgBox Err.Description
  Resume Exit_SAVE_BUTTON_Click
End Sub

Private Sub SAVE_BUTTON_Click()
On Error GoTo Err_SAVE_BUTTON_Click
 
Dim stDocName As String
Dim stLinkCriteria As String
 
If Me.Dirty = False Then
  RetVal = MsgBox("Please Input a New Employee!", vbOKOnly, "System Alert")
Else
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  DoCmd.Close
  stDocName = "Add Employee"
  DoCmd.OpenForm stDocName, , , , acFormAdd
End If
 
Exit_SAVE_BUTTON_Click:
  Exit Sub
 
Err_SAVE_BUTTON_Click:
  MsgBox Err.Description
  Resume Exit_SAVE_BUTTON_Click
End Sub

[ADezii]

I'm working with three other students on this project. I am not the one that wrote the code for the SAVE button, but this is a copy of all the code that I could find that deals with the SAVE button:

Private Sub SAVE_BUTTON_Click()
On Error GoTo Err_SAVE_BUTTON_Click
 
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_SAVE_BUTTON_Click:
  RetVal = MsgBox("Invoice Updated", vbOKOnly, "System Alert")
 
  Dim stDocName As String
  Dim stLinkCriteria As String
 
  DoCmd.Close
  stDocName = "Edit Switchboard"
  DoCmd.OpenForm stDocName, , , , acFormAdd
Exit Sub
 
Err_SAVE_BUTTON_Click:
  MsgBox Err.Description
  Resume Exit_SAVE_BUTTON_Click
End Sub

Private Sub Image17_Click()
On Error GoTo Err_SAVE_BUTTON_Click
 
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
 
Exit_SAVE_BUTTON_Click:
  RetVal = MsgBox("Employee Updated", vbOKOnly, "System Alert")
 
  Dim stDocName As String
  Dim stLinkCriteria As String
 
  DoCmd.Close
  stDocName = "Edit Switchboard"
  DoCmd.OpenForm stDocName, , , , acFormAdd
    Exit Sub
 
Err_SAVE_BUTTON_Click:
  MsgBox Err.Description
  Resume Exit_SAVE_BUTTON_Click
End Sub

Private Sub SAVE_BUTTON_Click()
On Error GoTo Err_SAVE_BUTTON_Click
 
Dim stDocName As String
Dim stLinkCriteria As String
 
If Me.Dirty = False Then
  RetVal = MsgBox("Please Input a New Employee!", vbOKOnly, "System Alert")
Else
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  DoCmd.Close
  stDocName = "Add Employee"
  DoCmd.OpenForm stDocName, , , , acFormAdd
End If
 
Exit_SAVE_BUTTON_Click:
  Exit Sub
 
Err_SAVE_BUTTON_Click:
  MsgBox Err.Description
  Resume Exit_SAVE_BUTTON_Click
End Sub

For which Save Operation can you NOT generate the Custom Message for? (1 or 3).

[louie310]

For which Save Operation can you NOT generate the Custom Message for? (1 or 3).

Is there more to your statement or is the answer that I can not generate the Custom Message?

Thanks,
Lou

[louie310]

Is there more to your statement or is the answer that I can not generate the Custom Message?

Thanks,
Lou

I have just seen your question. (For which Save Operation can you NOT generate the Custom Message for? (1 or 3).) I'm not exactly sure if I know what you are asking me, but I think that you are telling me to try the code you wrote in one of the SAVE button existing codes. What I am looking for is that when the form is filled out and the SAVE button is hit an error message pops up if the phone number has been typed in incorrectly. Reading the code you wrote me I was under thinking that I should be generating the code in the form itself not along with the code for the SAVE button.?? Am I on the right track?

Thanks,
Lou

[ADezii]

I have just seen your question. (For which Save Operation can you NOT generate the Custom Message for? (1 or 3).) I'm not exactly sure if I know what you are asking me, but I think that you are telling me to try the code you wrote in one of the SAVE button existing codes. What I am looking for is that when the form is filled out and the SAVE button is hit an error message pops up if the phone number has been typed in incorrectly. Reading the code you wrote me I was under thinking that I should be generating the code in the form itself not along with the code for the SAVE button.?? Am I on the right track?

Thanks,
Lou

Sorry for the confusion, Lou. The code I wrote can only go in the Error() Event of the Form on which the Save Button resides. If you have not already done so, post it there. I've posted it again for your reference, good luck.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2279 Then      'Input Mask Violation
  MsgBox "You must enter the Area Code + Phone Number as in: [phone]", vbExclamation, "Invalid Entry"
  Response = acDataErrContinue
Else
  Rersponse = acDataErrDisplay      'let Access handle the Error
End If
End Sub

[louie310]

Sorry for the confusion, Lou. The code I wrote can only go in the Error() Event of the Form on which the Save Button resides. If you have not already done so, post it there. I've posted it again for your reference, good luck.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2279 Then      'Input Mask Violation
  MsgBox "You must enter the Area Code + Phone Number as in: [phone]", vbExclamation, "Invalid Entry"
  Response = acDataErrContinue
Else
  Rersponse = acDataErrDisplay      'let Access handle the Error
End If
End Sub

I have inspected the event tab in properties for the form, the SAVE button and the home phone field and I do not see anything that is labeled Error so that I can follow your instructions and put the code in the Error() Event of the Form on which the Save Button resides.

I have tried some other stuff and if I can get a message box to pop up for the email field I figured I should get one to pop up for the phone number field. What I have done was:

In the Data tab of the properties section and in the validation rule for my email address I wrote Is Null Or ((Like "*?@?*.?*") And (Not Like "*[ ,;]*")) then in the validation text I wrote Please enter in a valid email address. So when the proper formats as specified in the input mask is not followed the message box pops up.

I tried to write something to go in the validation rule for phone number but I’m not getting it right. I tried Is Null Or ((Like "*!\(000") "000\-0000;1;#*”) And (Not Like “*[ ,, ]*”)) I think if I get this right I might get my message box to pop up. Have you another way to write this code?

[ADezii]

I have inspected the event tab in properties for the form, the SAVE button and the home phone field and I do not see anything that is labeled Error so that I can follow your instructions and put the code in the Error() Event of the Form on which the Save Button resides.

I have tried some other stuff and if I can get a message box to pop up for the email field I figured I should get one to pop up for the phone number field. What I have done was:

In the Data tab of the properties section and in the validation rule for my email address I wrote Is Null Or ((Like "*?@?*.?*") And (Not Like "*[ ,;]*")) then in the validation text I wrote Please enter in a valid email address. So when the proper formats as specified in the input mask is not followed the message box pops up.

I tried to write something to go in the validation rule for phone number but I’m not getting it right. I tried Is Null Or ((Like "*!\(000") "000\-0000;1;#*”) And (Not Like “*[ ,, ]*”)) I think if I get this right I might get my message box to pop up. Have you another way to write this code?

To Copy and Paste code to the Error() Event of your Form:

1. Copy the code block that I had Posted
2. Open the Form in Design View
3. On the Menu Bar Click View ==> Properties
4. Click the Event Tab
5. Select the OnError Event
6. Click on the ... button
7. Select Code Builder ==> OK
8. Your Error() Event should now be visible - Paste or write the code here

[louie310]

To Copy and Paste code to the Error() Event of your Form:

1. Copy the code block that I had Posted
2. Open the Form in Design View
3. On the Menu Bar Click View ==> Properties
4. Click the Event Tab
5. Select the OnError Event
6. Click on the ... button
7. Select Code Builder ==> OK
8. Your Error() Event should now be visible - Paste or write the code here

Thank you for your continued help. I did find the Error() Event when I used the View in the menu bar and was able to install your code and saved the code, but the message box does not pop up when the SAVE button is hit. I do get the error message to pop up if I incorrectly input the phone number and try to close the form (X).

[ADezii]

Thank you for your continued help. I did find the Error() Event when I used the View in the menu bar and was able to install your code and saved the code, but the message box does not pop up when the SAVE button is hit. I do get the error message to pop up if I incorrectly input the phone number and try to close the form (X).

I am sorry, but I cannot make heads or tails of your dilemma. If you have an Input Mask in place on a specific Field and you leave that Field and violate the Input Mask, the Form's Error() Event immediately fires, and in your case the Invalid Input Mask Error is trapped. What this means is that the Click() Event of your Save Button is never executed until you insert the proper Mask or Cancel the entry. I haven't given up yet, but I am running out of options.

[louie310]

I am sorry, but I cannot make heads or tails of your dilemma. If you have an Input Mask in place on a specific Field and you leave that Field and violate the Input Mask, the Form's Error() Event immediately fires, and in your case the Invalid Input Mask Error is trapped. What this means is that the Click() Event of your Save Button is never executed until you insert the proper Mask or Cancel the entry. I haven't given up yet, but I am running out of options.

You are right the SAVE button never executes. When I hit the SAVE button I get an error message generated by Access. Here is a screen shot of the message:



If I hit the (X) I get the custom message. I need to hit the SAVE button and have this message display.


I was hoping that the solution to this problem would be the same as the Email address I talk about before:
In the Data tab of the properties section and in the validation rule for my email address I wrote Is Null Or ((Like "*?@?*.?*") And (Not Like "*[ ,;]*")) then in the validation text I wrote Please enter in a valid email address. So when the proper formats as specified in the input mask is not followed the message box pops up.

I tried to write something to go in the validation rule for phone number but I’m not getting it right. I tried Is Null Or ((Like "*!\(000") "000\-0000;1;#*”) And (Not Like “*[ ,, ]*”)) I think if I get this right I might get my message box to pop up. Have you another way to write this code?

Can I generate any different code that will be accepted because Is Null Or ((Like "*!\(000") "000\-0000;1;#*”) And (Not Like “*[ ,, ]*”)) is giving me an error message when I try to use it.

The screen shots won't copy. Sorry

[ADezii]

You are right the SAVE button never executes. When I hit the SAVE button I get an error message generated by Access. Here is a screen shot of the message:



If I hit the (X) I get the custom message. I need to hit the SAVE button and have this message display.


I was hoping that the solution to this problem would be the same as the Email address I talk about before:
In the Data tab of the properties section and in the validation rule for my email address I wrote Is Null Or ((Like "*?@?*.?*") And (Not Like "*[ ,;]*")) then in the validation text I wrote Please enter in a valid email address. So when the proper formats as specified in the input mask is not followed the message box pops up.

I tried to write something to go in the validation rule for phone number but I’m not getting it right. I tried Is Null Or ((Like "*!\(000") "000\-0000;1;#*”) And (Not Like “*[ ,, ]*”)) I think if I get this right I might get my message box to pop up. Have you another way to write this code?

Can I generate any different code that will be accepted because Is Null Or ((Like "*!\(000") "000\-0000;1;#*”) And (Not Like “*[ ,, ]*”)) is giving me an error message when I try to use it.

The screen shots won't copy. Sorry

This code, placed in the KeyPress() Event of a Field named Phone, will provide similiar functionality to that of an Input Mask. It captures each Keystroke as they are entered, checks for only valid Keystrokes in specific positions such as: "(" in 1st position, ")" in 5th position, "-" in 10th position, etc., and also allows your custom Error Message. I realize that it is a little extreme, but it is functional, so I'll just through it out there for you to look at:

Private Sub Phone_KeyPress(KeyAscii As Integer)
Dim intLen As Integer, Msg As String
 
Msg = "You must enter the Phone Number in the following format (XXX) XXX-XXXX"
intLen = Len(Me![Phone].Text)
 
Select Case KeyAscii
  Case 40   '(
    If intLen = 0 Then      '( in the 1st position is OK
    Else
      MsgBox Msg, vbExclamation, "Invalid Entry"       '( in any other position
      KeyAscii = 0
    End If
  Case 41   ')
    If intLen = 4 Then      '( in the 5th position is OK
    Else
      MsgBox Msg, vbExclamation, "Invalid Entry"       '( in any other position
      KeyAscii = 0
    End If
  Case 32      'space
    If intLen = 5 Then      'space in the 6th position is OK
    Else
      MsgBox Msg, vbExclamation, "Invalid Entry"       'space in any other position
      KeyAscii = 0
    End If
  Case 45     '-
    If intLen = 9 Then      '- in the 10th position is OK
    Else
      MsgBox Msg, vbExclamation, "Invalid Entry"       '-  in any other position
      KeyAscii = 0
    End If
  Case 8
    'Backspace is OK anytime
  Case 48 To 57     'the numbers 0 to 9
    Select Case intLen
      Case 1 To 3, 6 To 8, 10 To 13     'numbers in positions 2,3,4,7,8,9,11,12,13,14 are OK
      Case Else
        MsgBox Msg, vbExclamation, "Invalid Entry"       '-  in any other position
        KeyAscii = 0
    End Select
  Case Else
    MsgBox Msg, vbExclamation, "Invalid Entry"
    KeyAscii = 0
End Select
End Sub

[louie310]

This code, placed in the KeyPress() Event of a Field named Phone, will provide similiar functionality to that of an Input Mask. It captures each Keystroke as they are entered, checks for only valid Keystrokes in specific positions such as: "(" in 1st position, ")" in 5th position, "-" in 10th position, etc., and also allows your custom Error Message. I realize that it is a little extreme, but it is functional, so I'll just through it out there for you to look at:

Private Sub Phone_KeyPress(KeyAscii As Integer)
Dim intLen As Integer, Msg As String
 
Msg = "You must enter the Phone Number in the following format (XXX) XXX-XXXX"
intLen = Len(Me![Phone].Text)
 
Select Case KeyAscii
  Case 40   '(
    If intLen = 0 Then      '( in the 1st position is OK
    Else
      MsgBox Msg, vbExclamation, "Invalid Entry"       '( in any other position
      KeyAscii = 0
    End If
  Case 41   ')
    If intLen = 4 Then      '( in the 5th position is OK
    Else
      MsgBox Msg, vbExclamation, "Invalid Entry"       '( in any other position
      KeyAscii = 0
    End If
  Case 32      'space
    If intLen = 5 Then      'space in the 6th position is OK
    Else
      MsgBox Msg, vbExclamation, "Invalid Entry"       'space in any other position
      KeyAscii = 0
    End If
  Case 45     '-
    If intLen = 9 Then      '- in the 10th position is OK
    Else
      MsgBox Msg, vbExclamation, "Invalid Entry"       '-  in any other position
      KeyAscii = 0
    End If
  Case 8
    'Backspace is OK anytime
  Case 48 To 57     'the numbers 0 to 9
    Select Case intLen
      Case 1 To 3, 6 To 8, 10 To 13     'numbers in positions 2,3,4,7,8,9,11,12,13,14 are OK
      Case Else
        MsgBox Msg, vbExclamation, "Invalid Entry"       '-  in any other position
        KeyAscii = 0
    End Select
  Case Else
    MsgBox Msg, vbExclamation, "Invalid Entry"
    KeyAscii = 0
End Select
End Sub

Again, thank you for the continued help. I entered the code in the KeyPress() Event and found I had to make some minor changes. Phone had to be changed to HomePhone in these two spots:
1) Private Sub Phone_KeyPress(KeyAscii As Integer)
2) intLen = Len(Me![Phone].Text)

When I tried to complete the form and started to enter the home phone number I got the error message:
"You must enter the Phone Number in the following format (XXX) XXX-XXXX"
as soon as I keyed the first number then it stalled right there. I need to turn this in today by 2PM. If you have time to look at it by then that would be great if not thank you for trying and all your time.
Lou

[louie310]

Again, thank you for the continued help. I entered the code in the KeyPress() Event and found I had to make some minor changes. Phone had to be changed to HomePhone in these two spots:
1) Private Sub Phone_KeyPress(KeyAscii As Integer)
2) intLen = Len(Me![Phone].Text)

When I tried to complete the form and started to enter the home phone number I got the error message:
"You must enter the Phone Number in the following format (XXX) XXX-XXXX"
as soon as I keyed the first number then it stalled right there. I need to turn this in today by 2PM. If you have time to look at it by then that would be great if not thank you for trying and all your time.
Lou

I finally came up with an answer for creating a custom message if the input mask is violated. In the properties window for the phone field, open the data tab. In the validation rule I typed in the code Is Null Or (Like "(???) ???-????") which includes making the field not to be required. In the validation text I typed in the custom error message, Please enter a number that is in a [phone] format! This is triggered if there are too many or not enough numbers typed into the field.

[ADezii]

I finally came up with an answer for creating a custom message if the input mask is violated. In the properties window for the phone field, open the data tab. In the validation rule I typed in the code Is Null Or (Like "(???) ???-????") which includes making the field not to be required. In the validation text I typed in the custom error message, Please enter a number that is in a [phone] format! This is triggered if there are too many or not enough numbers typed into the field.

I'm glad you arrived at a suitable solution.