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,353
    would redesigning the tables to include a primary key which prevented duplicate rows be a smarter call?
    http://i792.photobucket.com/albums/y.../miniDGR-0.jpg I've taken part in the Distinguished Gentlemans ride, and although I enjoyed the day the main focus was to raise awareness of Prostrate Cancer and raise some funds for research. Many thanks to the many sponsors who helped me raise some 360 towards this cause, its much appreciated and rather humbling.

    http://www.gentlemansride.com/rider/healdem

  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,353
    ?
    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") & "#"....
    http://i792.photobucket.com/albums/y.../miniDGR-0.jpg I've taken part in the Distinguished Gentlemans ride, and although I enjoyed the day the main focus was to raise awareness of Prostrate Cancer and raise some funds for research. Many thanks to the many sponsors who helped me raise some 360 towards this cause, its much appreciated and rather humbling.

    http://www.gentlemansride.com/rider/healdem

  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,353
    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......
    http://i792.photobucket.com/albums/y.../miniDGR-0.jpg I've taken part in the Distinguished Gentlemans ride, and although I enjoyed the day the main focus was to raise awareness of Prostrate Cancer and raise some funds for research. Many thanks to the many sponsors who helped me raise some 360 towards this cause, its much appreciated and rather humbling.

    http://www.gentlemansride.com/rider/healdem

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
  •