0

I am using DCount function in my user access form to prevent duplicates of reference number keyed by multiple users simultaneously. however, it is prompting "Ref already exists" even though the field is blank. any idea why?

If DCount("[UNIQUE_REF]", "DATA_TABLE","[UNIQUE_REF] = "'& Me.INPUTREF.Value & "'") > 0 Then 
     MsgBox("Ref already exists.")
     Me.Undo
     Cancel = True
     Exit Sub
End If
June7
  • 19,874
  • 8
  • 24
  • 34
missingdev
  • 1
  • 1
    Welcome to SO. When does this code executes? Timing is important sometime and putting the code in the wrong event can make this kind of things – Foxfire And Burns And Burns May 28 '21 at 11:08
  • Hi, this is executed when user clicks a button to add the record to the database - hence at the on click event. Should I place it under before update event instead? – missingdev May 28 '21 at 13:46
  • We don't know exactly how you designed your database, but probably you want to check the Unique Ref in the event `Form_BeforeUpdate` [Form.BeforeUpdate event (Access)](https://learn.microsoft.com/en-us/office/vba/api/access.form.beforeupdate-event) – Foxfire And Burns And Burns May 28 '21 at 15:26
  • Quote mark and apostrophe are transposed in posted expression which should trigger a compile error as well as run-time error if attempt to run. Correct code in button click event works for me. – June7 May 28 '21 at 18:02
  • 1
    You say this is in button click event so why the `Cancel = True`? – June7 May 28 '21 at 18:11
  • Thank you for all the help! Wanted to give an update — I had placed it under before update event and it works fine now. Though the form is a tad lag (I’m guessing it has to search through the thousands of entries to ensure that the reference isn’t in the database) - do you have any suggestions to improve this? – missingdev Jul 08 '21 at 01:42

0 Answers0