Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Duplicate Records (A97 SR2)

    Aaaaugh!!!
    I have some code (see below) which is designed to prevent duplicate records being added to a Code table. The table has four fields which in combination cannot be duplicated.

    Code works fine when adding a new record. BUT! I get the 'Duplicate Record' error when I try to edit a records description.

    Can anyone please point out the error of my ways.


    Private Function BuildWhere() As String
    '-------------------------------------------------------------
    ' This Private function, (called by the BeforeUpdate event),
    ' concatinates the four parts of the
    ' SAP General Ledger Account Code, and checks the
    ' tblCostCodes table for duplicates. An error message
    ' is displayed if duplicate detected
    '-------------------------------------------------------------
    Dim strWhere As String
    Dim intLoop As Integer
    Dim strField As String
    Dim txt As Variant

    For intLoop = 1 To 4
    strField = Choose(intLoop, "CompCode", "AcctCode", "CostCentre", "JobNumber")
    Set txt = Me(strField)
    If Len(txt & "") > 0 Then
    strWhere = strWhere & "[" & strField & "]='" & txt.Value & "' "
    Else
    strWhere = strWhere & "[" & strField & "] Is Null "
    End If

    If intLoop < 4 Then
    strWhere = strWhere & "AND "
    End If
    Next intLoop

    BuildWhere = strWhere

    End Function

    '------------------------------------------------------------
    ' Form_BeforeUpdate event
    '
    ' Before the Cost Code is added to the tblCostCodes table,
    ' the code is sent to the BuildWhere() function to test
    ' for duplicates
    '------------------------------------------------------------
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strWhere As String
    strWhere = BuildWhere()

    If DCount("*", "tblCostCodes", strWhere) <> 0 Then
    Cancel = True
    MsgBox "This record already exists! Please enter another Code"
    End If
    End Sub

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Records (A97 SR2)

    When you are editing a record the BuildWhere function find the record you are editing.
    You should add a condition to BuildWhere that exclude the current record.
    Something like :
    BuildWhere = BuildWhere & "AND [MyID] <> " & Me.MyID
    Replace MyID with the ID field of your table/form.
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Duplicate Records (A97 SR2)

    To quote from another post..........

    "Merci buckets"

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Duplicate Records - simpler solution (A97 SR2)

    An esaier way to do this is to create an index on the table made up of the multiple fields, and set the index to no duplicates.
    Then Access can worry about protecting the table. This has the added advantage of protecting the table even if the user tries to update it not throught the form.

Posting Permissions

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