Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    75

    Question Unanswered: Check field values for dups

    Hello,

    If anyone can help... please do!!!

    When entering data through a form into a table how to check for dups and create custom message box that would display error message? The field in underlying table does not allow duplicates. Access displays error mes. that new value would create duplicates in value and indexes and that record can not be saved. But how I can customaze that?

    Thank you for your help!

  2. #2
    Join Date
    Apr 2002
    Location
    Germany
    Posts
    32
    You can use the BeforeUpdate event to check for duplicate values.

    Let's assume the following:
    Table name is 'tblData'.
    Field name in table is 'Name'.
    Form name is 'frmShow'.
    Field name in form is 'txtName'.

    Create the following event code:
    Code:
    Private Sub txtName_BeforeUpdate(Cancel As Integer)
        If (DLookup("Name", "tblData", "Name =" & Forms!frmShow!txtName)) Then
            MsgBox "Duplicate value in field txtName!"
            Me.Undo
        End If
    End Sub
    The DLookup function looks for a duplicate value. It compares the form field value with the corresponding table field values. If the check is true you can send a message. The Undo method resets all changes in the current form.

    It's a simple solution but it works.

    peter

  3. #3
    Join Date
    Apr 2002
    Posts
    75
    Hi Peter,

    thanks for your response.

    I tried your code, but gives me error message: err.#3464, "Data type mismatch in expression"
    Here is my code:
    Private Sub txtBargUnitNum_BeforeUpdate(Cancel As Integer)
    If (DLookup("BargUnitNum", "tblBargainingUnit", "BargUnitNum = " & Forms!frmAddEditBargUnit!txtBargUnitNum)) Then
    (If...then - one line in code)
    MsgBox "Duplicate value in Bargaining Unit Number!"
    Me.Undo
    End If
    End Sub

    BargUnitNum is a text type field restricted to 3 chars.

    Could you give a clue what causes the error?...

    Thank again.
    (If...then - one line in code) frmAddEditBargUnit!

  4. #4
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Use the inside error!

    In an event, FormCurrent, or OnClick of a add record button type your code betwen the a procedure call:
    Private Sub Comando1_OnClick()
    On Error GoTo Err_Comando1_Click


    DoCmd.GoToRecord , , acNewRec

    Exit_Comando1_Click:
    Exit Sub

    Err_Comando1_Click:
    If Err.Number = 2105 Then
    Err.Description = MsgBox("You Can not use the same value twice")
    Else
    MsgBox Err.Description
    End If
    Resume Exit_Comando1_Click
    end sub

Posting Permissions

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