Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    6

    Help!! With IF Dcount for Checking Duplicates

    Hey guys,
    I have built an awards tracker that we have 6 different people inputing awards into. With this many people involved I have written an IF Dcount( statement to try to prevent duplicate entries. I have gotten it to do ALMOST what I want, but still havent got it perfect. The following is where I am at on the code...

    If DCount("*", "tblAwardsLog", "[PO #] & LastName & DateInitiated = '" & Me.[PO #] & Me.LastName & Me.DateInitiated & "'") > 0 Then
    DoCmd.CancelEvent
    DoCmd.OpenForm "frmRecordExists"
    End If

    If DCount("*", "tblAwardsLog", "LastName & Left(FirstName,3) & Recommended & DateInitiated & Nz([PO #]) = '" & Me.LastName & Left(Me.FirstName, 3) & Me.Recommended & Me.DateInitiated & Nz(Me.[PO #]) & "'") > 0 Then
    DoCmd.CancelEvent
    DoCmd.OpenForm "frmRecordExists"
    End If

    If DCount("*", "tblAwardsLog", "LastName & Left(FirstName,3) & Recommended & DateInitiated = '" & Me.LastName & Left(Me.FirstName, 3) & Me.Recommended & Me.DateInitiated & "'") > 0 Then
    DoCmd.CancelEvent
    DoCmd.OpenForm "frmRecordExists"
    Cancel = True
    End If

    The problem is if I don't add a [PO #] when I first add the record, then I try to go back to add a PO # it tells me the record already exists. The fields I need checked is [LastName], [Left(FirstName, 3)], [Recommended], [DateInitiated], [PO #]

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,568
    would redesigning the tables to include a primary key which prevented duplicate rows be a smarter call?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2012
    Posts
    6
    Well healdem it is possible to have these show up many times, just not at the same time on the same record twice

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,568
    ?
    proper data design should eliminate duplicates
    if you cannot do that then youneed to look at your stateemtns
    Im suspicious of
    Code:
    If DCount("*", "tblAwardsLog", "[PO #] & LastName & DateInitiated = '" & Me.[PO #] & Me.LastName & Me.DateInitiated & "'") > 0 Then
    DoCmd.CancelEvent
    DoCmd.OpenForm "frmRecordExists"
    End If
    i woudl have expected it to be somethign simialr to:-
    Code:
    If DCount("*", "tblAwardsLog", "[PO #] = " & Me.[PO #] & " AND LastName '" & Me.LastName & "' AND " DateInitiated = " & me.DateInitiated ) > 0 Then
    DoCmd.CancelEvent
    DoCmd.OpenForm "frmRecordExists"
    End If
    you may need to bracke the dateinitiated with # to denote its a date. you may also ned to specify the date as a US date mm/dd/yyyy
    eg:-
    Code:
    ..." AND dateinitiated = #" & format(me.dateinitiated,"mm/dd/yyyy") & "#"....
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2012
    Posts
    6

    Solution to Problem

    Thanks healdem for your timely response, what I did was put..

    Code:
    If Me.NewRecord Then
    
    If DCount("*", "tblAwardsLog", "LastName & Left(FirstName,3) & Recommended & DateInitiated = '" & Me.LastName & Left(Me.FirstName, 3) & Me.Recommended & Me.DateInitiated & "'") > 0 Then
    DoCmd.CancelEvent
    DoCmd.OpenForm "frmRecordExists"
    
    End If
    End If
    This allowed it to check the fields necessary for duplicates, but only apply to new records instead of the ones I go back to edit with certain information.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,568
    are you creating a primary key which is the name, first 3 letters of the forename and dateinitiated as a single value in the DB....


    I sincerely hope not......
    I'd rather be riding on the Tiger 800 or the Norton

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
  •