Validation rule warning

[mondoray]

Guys,

Is there a way to prevent the standard MS Access validation rule warning msgbox appearing and create your own msgbox?

mondoray

 

[GingerR]

sure--write your own code.
without any details, it's hard to help you out.
but for example, say you have a text box and you want the value to be >100.
in the text box's BeforeUpdate event, you'd put something like

Private Sub txtData_BeforeUpdate(Cancel As Integer)
    If Me.txtData <= 100 Then
        MsgBox "Please enter a value > 100!"
        Cancel = True
    End If
End Sub

 

[mondoray]

GingerR,

Thanks. I wasn't sure that it would prevent the standard msgbox from appearing.

My application has the validation rule in the table set to Like"V??"

I will give it a try.

mondoray

 

[GingerR]

take out the Validation Rule.

 

[mondoray]

GingerR,

Thanks a million.

mondoray

 

[mondoray]

GingerR,

How do I express your above code to ensure the data entered has three letters and starts with a "V"?

mondoray

 

[GingerR]

try len() to get length.
try left() to get the left # of characters you specify.

 

[mondoray]

GingerR,

Thks

mondoray

 

[Golom]

The validation rule that you had doesn't specify "... three letters starting with V ...".
It specifies "... three characters starting with V ...".

For example "V12" would pass the test as would "V?,".

To test this try

If NOT ( Len(Value) = 3 And Left$(Value,1) = "V" ) Then
MsgBox "... Invalid input message ..."
Else
' Save the value
End If

 

[mondoray]

Golom,

Thanks for your help. I have used your suggestion as below:

Private Sub Aircraft_Registration_BeforeUpdate(Cancel As Integer)
If Not (Len(Value) = 3 And Left$(Value, 1) = "v") Then
MsgBox "Aircraft registration must start with a V...", vbExclamation, "Invalid data..."
Else
'Save value
End If
End Sub

It hangs up on (Len(Value).

mondoray

 

[lespaul]

Is your field named 'Value'? You need to replace Value with the name of your field.

Leslie

 

[mondoray]

lespaul,

Many thks. I will give it a try.

mondoray

 

[PHV]

Private Sub Aircraft_Registration_BeforeUpdate(Cancel As Integer)
If Not (Aircraft_Registration Like "V??") Then
MsgBox "Aircraft registration must start with a V...", vbExclamation, "Invalid data..."
Else
'Save value
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mondoray]

PHV,

Many thanks

mondoray

 

[mondoray]

Guys,

I would like to ask another question on validation rule. I have a field which the data has to be either: MEL or MJB or PER or ADL.

How do I set the validation rule to have multiple responses?

Can I use the above with multiple like criteria?



mondoray

 

[PHV]

If InStr(",MEL,MJB,PER,ADL,", "," & [name of textbox] & ",") > 0 Then
MsgBox "OK"
Else
MsgBox "ERROR"
Cancel = True
End If


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mondoray]

PHV,

Your a star.

Many thanks

mondoray

 

[mondoray]

PHV,

With the "Like V" above I need to have the cursor remain on that filed when the validation rule is not met. I have tried:

[Aircraft Registration].SetFocus
 & 
SendKeys "+{Tab}"

both default.

Any suggestions?

mondoray

 

[PHV]

Cancel = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mondoray]

PHV,

Many thks.

mondoray