Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validating a field (XP/2000)

    Hello,

    I have an 8-character field that is formatted like this:

    00AAA000

    The 3 letters can be one of several dozen three-letter combinations. I'd like to put these 3-letter combinations in a table, and have a validation rule to check to make sure that the three letters entered by the user are a valid 3-letter combination (one that exists in the table). How can I do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validating a field (XP/2000)

    You cannot do this in a validation rule in the table; you will have to use use VBA code behind a form based on the table.

    Say that you store the valid three-letter combinations in a field named Code in a table named tblCodes. The text box on the form containing the 8 character field is named txtSomething. Put code in the Before Update event of the text box:

    Private Sub txtSomething_BeforeUpdate(Cancel As Integer)
    Dim strText As String

    ' Don't bother is text box is blank
    If IsNull(Me.txtSomething) Then Exit Sub

    ' Extract three-letter code
    strText = Mid(Me.txtSomething, 3, 3)
    ' Does it occur in the table?
    If DCount("*", "tblCodes", "Code = " & Chr(34) & strText & Chr(34)) = 0 Then
    ' Notify user
    MsgBox "The code " & strText & " is not correct.", vbExclamation
    ' Cancel the update
    Cancel = True
    End If
    End Sub

    Substitute the names you are using.

  3. #3
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating a field (XP/2000)

    Hans,

    Thanks!

    The code sample worked exactly as advertised.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •