BeforeUpdate to determine length of entry and allow or not the entry

[ohi]

Hi,

Briefly what I'm trying to do is determine if the user entered correctly the Vin number for a vehicle and warn them if they have or have not.

I can go about it two ways.

#1: There's a field in my table that references vehicle type, I only need to match if the VIN is 17 characters long on VehTypeID 1, if it's anything else, it can be any amount of characters.

Or #2, and the way I'm trying to do it cause tbh I really don't know what I'm doing is see if the vin is <= 16 and tell them theres too few characters and give them a msg to continue if it's what they meant to do, and another for >=18 stating there's too many characters.

Here's the code I'm trying to work with, any help would be fantastic!

.

If Len([Vin] <= 16) Then Cancel = False
MsgBox "Your VIN number contains LESS than the standard 17 characters for a Passenger Vehicle. You may modify your entry or continue if it is not a Passenger Vehicle."
ELSEIf Len([Vin] >= 18) Then Cancel = False
MsgBox "Your VIN number contains MORE than the standard 17 characters for a Passenger Vehicle. You may modify your entry or continue if it is not a Passenger Vehicle."
ELSEIf Me.Vin.Value = DLookup("[Vin]", "tblVehicles", "[Vin] = '" & Me.Vin.Value & "'") Then
Cancel = True
MsgBox "A duplicate VIN was found. Check your entry and try again."
End If
End Sub

 

[missinglinq]

I believe this covers all of your requirements. You have to do different kinds of Validation in different events.

This assumes that both VIN and VehTypeID are defined as Text. The syntax would be slightly different if either were defined as a Number.

Private Sub Vin_BeforeUpdate(Cancel As Integer)
 If DCount("[Vin]", "tblVehicles", "[Vin] = '" & Me.Vin.Value & "'") > 0 Then
   MsgBox "A duplicate VIN was found. Check your entry and try again."
   Cancel = True
  End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Nz(Me.VehTypeID, "") = "" Then
 Cancel = True
 MsgBox "You Must Enter a Vehicle Type!"
 Me.VehTypeID.SetFocus
 Exit Sub
End If
If Nz(Me.Vin, "") = "" Then
 Cancel = True
 MsgBox "You Must Enter a VIN Number For This Vehicle!"
 Me.Vin.SetFocus
 Exit Sub
End If

If Me.VehTypeID = "1" Then
 If Len(Me.Vin) <> 17 Then
   MsgBox "VIN Numbers for Passenger Vehicles Must Be 17 Digits Long!"
   Cancel = True
   Me.Vin.SetFocus
 End If
End If

End Sub

Linq ;0)>

 

[ohi]

VehTypeID is the PK for a table and the FK for this one, so it's an autonumber/number, I apologize I should have mentioned that, easy fix though!

Thanks for the assistance, it makes sense now!

Made some minor corrections and it worked as intended, again, thanks so much!!!

 

[missinglinq]

Glad we could help!

Good luck with your project!

Linq ;0)>