I want to have a form / database that the data goes into automatically search for duplicate entries. Duplicates can either be by reference number (which contains numbers an a letter) or by name.
I have tried this dcount on the control source for [counter] in form "Add New" that is bound to database "tracker" to count reference numbers that are duplicates, so that anything higher than a "1" count in the database would be flagged, but i can't get it to to count based on the value that the user enters into [reference number] on form "add new".
=DCount("*","tracker","'[Reference number]'= '[Tracker]![Reference number]'")
I want it to search all records in the table "tracker", where [counter] on form "add new" equals the number of records in "tracker" that have the same reference number as the user enters into the form control [retention number].
I ave set the "on Change" property to [Event Procedure} to trigger this count anytime that there is a change on the form. What am I doing wrong? Open to other approaches to solving the problem as well.
Your syntax is a little out, this assumes [Reference number] is a text string, and that the control on your form is also called [Reference Number];
=DCount("*","tracker","[Reference number]= '" & Me.[Reference number] & "'")
If its a number remove the single quotes.
This also demonstrates why its a good idea to rename controls on forms so you know it is the control and not the field. So if your control was called txtRefNumber you would know what you were referencing (and take the spaces out of field names - it makes for lots of extra typing of square brackets and mistakes.
Further Edit : If you put in the after update event of your txtRefNum field the following
Me.YourCountControlName = DCount("*","tracker","[Reference number]= '" & Me.txtRefNum & "'") It should update. Remove the control source from the YourCountControl