Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Question Unanswered: Field Validation

    Access MDB connected to SQL Server. Working with Inventory form.

    Need to check a value of BookID (Text) field to prevent duplicates in Books table.

    The Inventory form already has several lines of code checking for empty fields: BookID, Author, etc. when Ok button pushed.

    Need to make sure that the new code (BookID field validation) is bypassed when users modify old records. Otherwise users will be getting ("BookID already exists") error any time they try to modify any field of the Inventory form.

    Trying:

    -------------------------------------------------------------------
    Private Sub Form_BeforeInsert(Cancel As Integer)
    If (Not IsNull(DLookup("[BookID]", "Books", "[BookID] ='" & Me!BookID & "'"))) Then
    MsgBox "BookID already exists", vbCritical, "Error"
    DoCmd.GoToControl "BookID"
    DoCmd.CancelEvent
    End If
    End Sub
    -------------------------------------------------------------------

    And it doesn’t work.

    Any help would be appreciated.
    Please be as specific as possible.

    Thanks in advance for your prompt respond.

  2. #2
    Join Date
    Apr 2004
    Posts
    7
    Than you for reply!

    I tried.
    Getting the same effect as with “Private Sub BookID_BeforeUpdate()” - can’t enter any BookID.
    Receiving:
    MsgBox "BookID already exists", vbCritical, "Book ID Error"

    Even if the entered BookID is not in Books table.

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Try this then:
    Code:
    Private Sub BookID_LostFocus()
       If Not IsNull(DLookup("[RecordID]", "Books", "[BookID] ='" & Me.BookID & "'")) Then
          MsgBox "BookID already exists", vbCritical, "Book ID Error"
          Me.BookID.Setfocus
       End If
    End Sub
    Where: RecordID is your table autonumber field.


  4. #4
    Join Date
    Apr 2004
    Posts
    7
    Books table doesn’t have an autonumber field. BookID is a Primary Key, and it is entered by users.

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Why don't you just set the BookID Index property in table to Yes (No Duplicates)?


  6. #6
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    100
    You can do a SQL statement looking for the new PK the user enters. If the SQL returns something, it means that already exists one record with this PK.

  7. #7
    Join Date
    Apr 2004
    Posts
    7
    Originally posted by CyberLynx
    Why don't you just set the BookID Index property in table to Yes (No Duplicates)?

    It is a PK already.

  8. #8
    Join Date
    Apr 2004
    Posts
    7
    Originally posted by pipeio
    You can do a SQL statement looking for the new PK the user enters. If the SQL returns something, it means that already exists one record with this PK.

    Unfortunately, I don’t know how.

Posting Permissions

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