Results 1 to 4 of 4
  1. #1
    bornfattom23 is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2010
    Posts
    5

    Validate Form Field based on Duplicate Data

    Hey all,
    This one's probably pretty easy, but I can't seem to find the right search term to find my answer. I have a form that is designed to insert new students into a database. The form is pretty long, and doesn't check to see if the primary key (StudentID) already exists in the table until the entire form is complete and the record created.

    My question is this: can I create a validation rule for the StudentID field which will check whether or not the student already exists before allowing the rest of the form to be filled out? I'm thinking of a Lost Focus event that will check the value, see if it already exists in the table, and protest if it does exist. What would the validation rule be?



    I'm pretty new to Access, but I've done some previous programming. Any help you can give would be greatly appreciated.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    The after_update event is better.
    the code will like this, for example, let's say the textbox name is studentID, the table name is tblStudent, the field name is studentID:
    Code:
    Private Sub StudentID_BeforeUpdate(Cancel As Integer)
        If Not IsNull(DLookup("StudentID", "tblStudent", "studentid=" & StudentID)) Then
            MsgBox ("Student ID exists.")
            StudentID.SelStart = 0
            StudentID.SelLength = 100
            Cancel = 1
        End If
    End Sub

  3. #3
    bornfattom23 is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2010
    Posts
    5
    If I use after Update, won't it wait until the entire form is completed and the record is updated? I very well could be wrong; I'm not super-experienced with Access. Thanks a bunch for the response, BTW.

  4. #4
    bornfattom23 is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2010
    Posts
    5
    Please disregard the ignorance of my last post. I tried the code and it worked, other than one thing. These lines:

    StudentID.SelStart = 0
    StudentID.SelLength = 100
    caused a runtime error, claiming that the method didn't exist for the requested object. However, the code seems to do what I need it to do without them. What exactly were they for? Anyway, I really, really appreciate the help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Limit data entry based on another field
    By chemengr in forum Forms
    Replies: 5
    Last Post: 01-02-2014, 01:21 PM
  2. Replies: 5
    Last Post: 08-20-2010, 09:10 AM
  3. Populate one field based upon another fields data
    By BigBrownBear in forum Queries
    Replies: 1
    Last Post: 03-23-2010, 04:27 PM
  4. using Functions to validate a date field
    By jamin14 in forum Programming
    Replies: 1
    Last Post: 03-18-2010, 12:46 PM
  5. Entering duplicate data in Form
    By cotri in forum Forms
    Replies: 1
    Last Post: 01-06-2010, 11:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums