0

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.

user97804
  • 13
  • 5

1 Answers1

0

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

Minty
  • 1,616
  • 1
  • 8
  • 13
  • so to be clearer I re-named the field on the form to [TxtRefNum]. This field contains numbers with a letter in the middle - I imagine this should be treated as text (keeping single quotes). I replaced my code with =DCount("*","tracker","[TxtRefNum]= '" & [Me].[Reference number] & "'") This still gives me a #name Error. – user97804 Dec 13 '16 at 17:16
  • Assuming you haven't changed the field name on the table you should use – Minty Dec 13 '16 at 17:25
  • =DCount("*","tracker","[Reference Number] = '" & Me.TxtRefNum & "'") Me.TxtRefNum is your control on the form. As I said in my edit this will work in VBA not as a control source on the form. – Minty Dec 13 '16 at 17:29
  • I tried this with and without the single quotes, without the Me. since it is in control source. Still not working. Does it have something to do with the Event Property? I am at a loss. Thank you for the help! – user97804 Dec 13 '16 at 17:46