By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,584 Members | 3,447 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,584 IT Pros & Developers. It's quick & easy.

Duplicate record check prevents modifying of records

P: 1
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("Duplicate 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!
Mar 12 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 2.5K+
P: 2,545
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.

Mar 15 '08 #2

Expert 2.5K+
P: 3,532
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:

Expand|Select|Wrap|Line Numbers
  1.   If Me.NewRecord Then
  2.   If DCount(search for records with the same TimeIn and TimeOut) > 0 Then
  3.   MsgBox("Duplicate record found")
  4.   Undo and set focus
  5.  End If
  6. End If 
Welcome to TheScripts!

Linq ;0)>
Mar 15 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.