Need help with required field - different character lengths (1 Viewer)

[bdhtexas]

I need help with a required field that has different character lengths.

I have an input mask that covers the first 7 characters, however, the users are still entering the incorrect policy numbers, I need to put a stop to it.

I have 3 different policy number lengths involved, 7, 8 and 9.
They are different kinds as well, alpha, alpha/numeric and numeric.

Example below:
P###### - 7 characters
02###### - 8 characters
02M##### - 8 characters
NX####### - 9 characters
HX###### - 8 characters
HXL##### - 8 characters
HXM##### - 8 characters

My question is this -- do I have to create 7 different forms or can I program it to accept only the certain (policy#) characters above. Or can I create a query to insert into my form.

Any help is appreciated, Thanks as always.

 

[RuralGuy]

Putting this code in the BeforeUpdate event of your Policy Number TextBox should get you started:

Private Sub [b]PolicyNo[/b]_BeforeUpdate(Cancel As Integer)


Select Case Left(Me.[b]PolicyNo[/b], 1)

Case "P"
   If Len(InString) <> 7 Then
      MsgBox "This Policy Number should have 7 characters"
      Cancel = True
   End If
Case "0"
   If Len(InString) <> 8 Then
      MsgBox "This Policy Number should have 8 characters"
      Cancel = True
   End If
Case "N"
   If Len(InString) <> 9 Then
      MsgBox "This Policy Number should have 9 characters"
      Cancel = True
   End If
Case "H"
   If Len(InString) <> 8 Then
      MsgBox "This Policy Number should have 8 characters"
      Cancel = True
   End If
Case Else
      MsgBox "Invalid Policy Number"
      Cancel = True
End Select

End Sub

Of course replace the Bold name with the name of your control.

 

[bdhtexas]

Thanks, but it didn't work

When I was inputting the code, under Case Else, after typing in the policy number, I got an error message that said "Compile Error - Expected end of statement"

Then when I went to test it I got this message "Compile Error - Variable Not Defined"

 

[RuralGuy]

My bad. You need to change all of the InString to the Me.NameOfYourTextBox.