• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

Using Dcount for finding duplicates

How to use Dcount to find duplicates when the record has
multiple attributes in the primary key. I cannot get the
sample in ACC97 to work. Running this as a BeforeUpdate macro fails to update the DCount parameters when I add a new record.
0
lamalcz
Asked:
lamalcz
  • 3
  • 2
1 Solution
 
Victor SpiridonovCommented:
Pls, post the code that fails.
0
 
devthaCommented:
This is what I do on a before update event of the field
[SocialSecurityNumber]. How ever this is to check if the entry is valid and matches to any existing valid SSN in the HRIS employee Table. The same can be done in your case.

SSN is the field name in HRIS table.
In your case you can use the same condition and let the user proceed if the count = 0 else cancel the event.
Devtha

If DCount("[SSN]", "HRIS", "[SSN] = FORMS![TRAINING DATA ENTRY]![SocialSecurityNumber]") = 0 Then
MsgBox "SSN NOT IN THE DATABASE"
SendKeys "{ESC}{ESC}{ESC}"
Cancel = True
End If

0
 
lamalczAuthor Commented:
Single attribute keys are easy, try this mixed variable case.
DCount("*","tblBank","DocumentID = '" & Forms!frmDEBank.cboDocumentID & "' And BankID = Forms!frmDEBank.BankID")>0
BankID is an integer, cboDocumentID is a combo box on a string.
As I enter new BankIDs and DocumentIDs the code the runs my "duplicate key" form but the form always shows the last Bank record, not the one I am currently entering! That is the Dcount keeps using the last record in the table and not the new record I am entering.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lamalczAuthor Commented:
Adjusted points to 125
0
 
devthaCommented:
Instead of using dcount establish a recordset. I am posting theis from home I shall give you the correct syntax tomorrow morning.
But this is how it goes.

Dim dbs As Database, rst As Recordset,rcount as integer

' Modify this line to include the path to dbname on your computer.

      Set dbs = OpenDatabase("dbname.mdb")

Set rst = dbs.OpenRecordset("SELECT statement  ;")
' select statement is the query that will bring the recordcount you are looking for
      
      ' Populate the Recordset.
      rst.MoveLast
      
rcount = rst.recordcount
if rcount > 0 then
msgbox rcount
else...
end if          'You got the idea?


      dbs.Close




0
 
devthaCommented:
Ignore the posting from home comments.....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now