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

Validation Rule for combined Text & Number

P: 41
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.
Jun 9 '07 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,597
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
Jun 9 '07 #2

P: 41
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.
Jun 14 '07 #3

ADezii
Expert 5K+
P: 8,597
============
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?
Jun 14 '07 #4

P: 41
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.
Jun 15 '07 #5

P: 8
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.
Jun 23 '07 #6

ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Error(DataErr As Integer, Response As Integer)
  2. Const conValidationRuleViolation = 2279
  3. Dim Msg As String
  4. Msg = "Eh..! The TAN Number must be 5 Alphapetic,4 Numeric, 1 Alphaphetic. Example : ABCDE1234A"
  5.  
  6. Select Case DataErr
  7.   Case conValidationRuleViolation
  8.     MsgBox Msg, vbOKOnly, "Vendor Creation"
  9.     Response = acDataErrContinue
  10.   Case Else
  11.     MsgBox Err.Description, vbExclamation, "Error in Form"
  12. End Select
  13. End Sub
Jun 23 '07 #7

Post your reply

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