If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > Visual Basic > Help!! With IF Dcount for Checking Duplicates

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 #]
Reply With Quote
  #2 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,218
would redesigning the tables to include a primary key which prevented duplicate rows be a smarter call?
__________________
I'd rather be riding my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,218
?
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old
Jaded Developer
 
Join Date: Nov 2004
Location: out on a limb
Posts: 12,218
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 my Versys or my Tiger 800 let alone the Norton
Reply With Quote
Reply

Tags
dcount, duplicate, help needed, if statement, vba

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On