Duplicate record check prevents modifying of records

[Ranma13]

Hello, I have a duplicate record check written in VB for a check in/check out database. Here's the pseudocode, written for the BeforeUpdate property on the form:

If DCount(search for records with the same TimeIn and TimeOut) > 0 Then
MsgBox("Duplica te record found")
Undo and set focus
End Sub

This code works fine if you try to add a new record that has duplicate times for TimeIn and TimeOut, but when I try to modify an already-inputted record using the form, it gives me the duplicate record error.

The reason this happens, as I can deduce it, is because the check is performed before the record is updated. However, I can't think of any way to get around this without removing the check itself. Is there any way to differentiate between adding a new record and simply updating one? Thanks!

[Stewart Ross]

Hi. You could use similar checking code called from the After Update events of your TimeIn and TimeOut controls. That way you are using the updated values in your lookup. To undo the changes if these result in a duplicate you just use the undo method of that control after displaying your error message to undo the changes.

-Stewart

[missinglinq]

As you say, you need to differentiate between new records and existing records and then only do your dup check on new records. This is done by wrapping your code inside of Me.NewRecord:

  If Me.NewRecord Then
  If DCount(search for records with the same TimeIn and TimeOut) > 0 Then
  MsgBox("Duplicate record found")
  Undo and set focus
 End If
End If 
 

Welcome to TheScripts!

Linq ;0)>