Validation of data in two fields

[Guest]

Hi

Have two fields in a form (Plate and Pin respectively).
There are two plate sizes dictated by there letter designation ie B1, B" etc
and C1,C2etc for the larger plates
I need a restriction on the Pin field that if a B plate ref is entered in
Plate then a number less than 17 needs to be entered into the field titled
Pin and also if a C plate reference is entered then a number less than 49 is
entered

Many thanks
Richard

 

[Al Campagna]

Richard,
Several ways to do that... but a basic method...
(Use the BeforeUpdate event of PIN)

Private Sub Pin_BeforeUpdate(Cancel as Integer)
IF InStr(Plate,"B") Then
IF Pin > 16 Then
MsgBox "Pin must be less than 17"
Cancel = True
Pin.Undo
End If
ElseIf InStr(Plate,"C") Then
IF Pin > 48 Then
MsgBox "Pin must be less than 49"
Cancel = True
Pin.Undo
End If
End If

--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Need further help

I have another field called "Result", which if there is an entry in the
"Plate" field then there must be an entry in the "Result" field.
I have tried using the code you provided earlier but am unable to find a way
of saying that if the "Result" field has no data in it return the error
message

Below is my version of your code

Private Sub Result_BeforeUpdate(Cancel As Integer)
If InStr(Plate_Number, "B") Then
If Result = 0 Then
MsgBox "Field Must Have A value"
Cancel = True
Result.Undo
End If
ElseIf InStr(Plate_Number, "C") Then
If Result = 0 Then
MsgBox "Field Must Have A Value"
Cancel = True
Result.Undo
End If
End If
End Sub

The Result = 0 tries to signify that the field is empty

Many Thanks

Richard

 

[Jon Lewis]

Private Sub Result_BeforeUpdate(Cancel As Integer)
If Len(Plate_Number & "") <> 0 Then
If Len(Result & "") = 0 Then
MsgBox "Field Must Have A value"
Cancel = True
End If
End If
End Sub

Make sure that Plate_Number and Result have no default values

HTH

 

[Guest]

Have copied the code below, however not creating the message when testing.
The Plate Number field and the results field are combo boxex, could this have
any effect.

Richard

 

[Jon Lewis]

Sorry Richard

The code should be in the Form Before Update event and so will kick in when
you attempt to save the record with a Plate_Number but no Result

HTH