Restricting characters in a field

Topic

New Reply

Hi, At work we use an Access 2007 database where one of the fields in a table is a policy number. I want to restrict users to use 9 to 12 mix of alpha and numeric characters but ensure that the string does not contain any spaces or dashes (this field is used elsewhere as a lookup and these characters cause the lookup to fail). How do I do this? The only format I am using currently is > to force any alpha characters into upper case

Regards,
Maria

Reply | Quote

Replies

Maria,

Add the following code to your Policy Number field:

Code:

Private Sub PrjNo_BeforeUpdate(Cancel As Integer)

   If Len(Me.PrjNo)  12 Then

      MsgBox "Project Number must be between 9 and 12 characters in lenght." & _
             vbCrLf & vbCrLf & "Please Correct...", vbCritical + vbOKOnly, _
             "Error: Invalid Project Number Length"
      Cancel = True
      
   End If
    If InStr(Me.PrjNo, " ") > 0 Or InStr(Me.PrjNo, "-") Then

      MsgBox "Project Number must not contain blanks or dashes." & _
             vbCrLf & vbCrLf & "Please Correct...", vbCritical + vbOKOnly, _
             "Error: Invalid characters in Project Number"
      Cancel = True
      
   End If
     
End Sub

Note: You’ll have to change the 5 references to PrjNo to the actual Field Name you use for your field.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote