Validation Rule for combined Text & Number

[solargovind]

Hi,

I need to accept in my Textbox ONLY 5 Alphaphetic 4 numeric and 1 Alphaperic. So, Totally 10 characters only should accept. And there should not be any space..

How can i Set validation rule & show my own message for this...?

Any suggestion would be appreciated GREATLY.

Thanks in advance.

ngr.

[ADezii]

Hi,

I need to accept in my Textbox ONLY 5 Alphaphetic 4 numeric and 1 Alphaperic. So, Totally 10 characters only should accept. And there should not be any space..

How can i Set validation rule & show my own message for this...?

Any suggestion would be appreciated GREATLY.

Thanks in advance.

ngr.

Instead of a Validation Rule why not create an Input Mask? The following Input Mask will allow only 5 Alphas - 4 Numerics - 1 Alpha (no spaces):
LLLLL0000L

[solargovind]

Instead of a Validation Rule why not create an Input Mask? The following Input Mask will allow only 5 Alphas - 4 Numerics - 1 Alpha (no spaces):
LLLLL0000L

============
Thnx...

Fine..But, I tried to show my own message instead of Access message in the message box as below ...But, its not working...

If Not IsNull([Vendor_TAN_No]) Then
If Vendor_TAN_No.InputMask <> "LLLLL0000L" Then
MsgBox "Eh..! The TAN Number must be 5 Alphapetic,4 Numeric, 1 Alphaphetic. Example : ABCDE1234A", vbOKOnly, "Vendor Creation"
Screen.PreviousControl.SetFocus
End If
End If

Can anybody suggest to me ....?

Your suggestion would be appreciated greatly.

Thanks in advance.

ngr.

[ADezii]

============
Thnx...

Fine..But, I tried to show my own message instead of Access message in the message box as below ...But, its not working...

If Not IsNull([Vendor_TAN_No]) Then
If Vendor_TAN_No.InputMask <> "LLLLL0000L" Then
MsgBox "Eh..! The TAN Number must be 5 Alphapetic,4 Numeric, 1 Alphaphetic. Example : ABCDE1234A", vbOKOnly, "Vendor Creation"
Screen.PreviousControl.SetFocus
End If
End If

Can anybody suggest to me ....?
If Me!Vendor_TAN_No.InputMask <> "LLLLL0000L" Then


Your suggestion would be appreciated greatly.

Thanks in advance.

ngr.

Where exactly is the location of this code?

[solargovind]

Where exactly is the location of this code?

-----
Its located in next controls of the gotfocus event..

Even, i tried with After_update of current control as below

If Not IsNull([Vendor_TAN_No]) Then
If Vendor_TAN_No.InputMask <> "LLLLL0000L" Then
MsgBox "Eh..! The TAN Number must be 5 Alphapetic,4 Numeric, 1 Alphaphetic. Example : ABCDE1234A", vbOKOnly, "Vendor Creation"
me!Vendor_TAN_No = null
Screen.PreviousControl.SetFocus
Endif

Your suggestion would be appreciated greatly.

Thanks in advance.

ngr.

[stpark22]

If you want to alert the user before updating the control, place the code in the BeforeUpdate event...

-----
Its located in next controls of the gotfocus event..

Even, i tried with After_update of current control as below

If Not IsNull([Vendor_TAN_No]) Then
If Vendor_TAN_No.InputMask <> "LLLLL0000L" Then
MsgBox "Eh..! The TAN Number must be 5 Alphapetic,4 Numeric, 1 Alphaphetic. Example : ABCDE1234A", vbOKOnly, "Vendor Creation"
me!Vendor_TAN_No = null
Screen.PreviousControl.SetFocus
Endif

Your suggestion would be appreciated greatly.

Thanks in advance.

ngr.

[ADezii]

Hi,

I need to accept in my Textbox ONLY 5 Alphaphetic 4 numeric and 1 Alphaperic. So, Totally 10 characters only should accept. And there should not be any space..

How can i Set validation rule & show my own message for this...?

Any suggestion would be appreciated GREATLY.

Thanks in advance.

ngr.

You must Trap the specifc Validation Error in the Form's Error() Event, display your own Custom Error Message, and tell Access not to display its own Error Message as in:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Const conValidationRuleViolation = 2279
Dim Msg As String
Msg = "Eh..! The TAN Number must be 5 Alphapetic,4 Numeric, 1 Alphaphetic. Example : ABCDE1234A"
 
Select Case DataErr
  Case conValidationRuleViolation
    MsgBox Msg, vbOKOnly, "Vendor Creation"
    Response = acDataErrContinue
  Case Else
    MsgBox Err.Description, vbExclamation, "Error in Form"
End Select
End Sub