I am using a data entry form to enter new data into one of the tables.How can we prevent entry of duplicate records? Duplicacy shall be checked on the first two fields only.
Please help.
regards
bijon
Let me be more elaborate. I am trying to prevent duplicate entries on a combination of two fields, eg, HQ_File_Ref & HQ_File_Date.
I am using the following code in the event before update in the field HQ_File_Date
Private Sub HQ_File_Date_BeforeUpdate(Cancel As Integer)
If DCount("[HQ_File_Ref]", "tblPropMain", "[HQ_File_Ref] =" & Me.[HQ_File_Ref] & " AND [HQ_File_Date] = " & Me.[HQ_File_Date]) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "HQ File reference no...." _
& HQ_File_Ref & " has already been entered." _
& vbCr & vbCr & "Click OK to revert back....", vbInformation _
, "Duplicate Information"
End If
End Sub
This doesn’t work out. Please help
Thanks in anticipation
Regards
bijon
You are, in fact, using these two fields like a Composite Primary Key, even if you're not declaring them as such! That's what a Composite PK is, two (or more) fields that together create a unique value. The datatype of HQ_File_Date, I would assume, is Date/Time; what is the datatype of HQ_File_Ref?
Okay, bijon! I think I've got all of your object names plugged in correctly here. A couple of things:
First, when using criteria in Access VBA, whether it's in DCount, DLookup or whatever, the syntax varies slightly, depending on the datatype of the field you're trying to match. The difference has to do with the delimiters used for each type. Numeric fields require nothing, Text fields require Single Quotes, and Date/Times fields require the Pound Sign.
Next, since you can't be sure what order the user will fill in the form, you have to have your code in the BeforeUpdate event of both textboxes, HQ_File_Ref and HQ_File_Ref. We don't want the code to run until both fields are filled, so in each BeforeUpdate event, we'll check to make sure that the other field isn't Null. If it is Null, the code doesn't run; it'll only run after both fields are populated.
Lastly, I moved the Me.Undo line from before the messagebox until after the messagebox; if you dump your record first, the Reference number is not available to appear in the message.
Code:
Private Sub HQ_File_Ref_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.HQ_File_Date) Then
If DCount("[HQ_File_Ref]", "TblPropMain", "[HQ_File_Ref] ='" & Me.[HQ_File_Ref] & "' AND [HQ_File_Date] = #" & Me.[HQ_File_Date] & "#") > 0 Then
'Message box warning of duplication
MsgBox "HQ File reference no...." & HQ_File_Ref & " has already been entered." _
& vbCr & vbCr & "Click OK to revert back....", vbInformation, _
"Duplicate Information"
Me.Undo
End If
End If
End Sub
Code:
Private Sub HQ_File_Date_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.HQ_File_Ref) Then
'
If DCount("[HQ_File_Ref]", "TblPropMain", "[HQ_File_Ref] ='" & Me.[HQ_File_Ref] & "' AND [HQ_File_Date] = #" & Me.[HQ_File_Date] & "#") > 0 Then
'Message box warning of duplication
MsgBox "HQ File reference no...." & HQ_File_Ref & " has already been entered." _
& vbCr & vbCr & "Click OK to revert back....", vbInformation, _
"Duplicate Information"
Me.Undo
End If
End If
End Sub
Private Sub HQ_File_Ref_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.HQ_File_Date) Then
If DCount("[HQ_File_Ref]", "TblPropMain", "[HQ_File_Ref] ='" & Me.[HQ_File_Ref] & "' AND [HQ_File_Date] = #" & Me.[HQ_File_Date] & "#") > 0 Then
MsgBox "more than one"
'Message box warning of duplication
MsgBox "HQ File reference no...." & HQ_File_Ref & " has already been entered." _
& vbCr & vbCr & "Click OK to revert back....", vbInformation, _
"Duplicate Information"
Me.Undo
End If
End If
End Sub
Private Sub HQ_File_Date_BeforeUpdate(Cancel As Integer)
If Not IsNull(Me.HQ_File_Ref) Then
'
If DCount("[HQ_File_Ref]", "TblPropMain", "[HQ_File_Ref] ='" & Me.[HQ_File_Ref] & "' AND [HQ_File_Date] = #" & Me.[HQ_File_Date] & "#") > 0 Then
MsgBox "more than one"
'Message box warning of duplication
MsgBox "HQ File reference no...." & HQ_File_Ref & " has already been entered." _
& vbCr & vbCr & "Click OK to revert back....", vbInformation, _
"Duplicate Information"
Me.Undo
End If
End If
End Sub
Give it a whirl, and post back if you run into any problems. Remember, since you're pasting the code into the code window, to go into the properties sheet and make sure you see [Event Procedure] beside the BeforeUpdate event for both fields. Sometimes they code hooks up automatically and sometimes you have to do it manually.
In the form, the first field that is populated is the HQ_File_Ref, then the HQ_File_Date. So when the first field HQ_File_Ref is entered, the second field is still blank. I think the DCount function shall have to be applied only after the first field is entered & before the HQ_File_Date is updated,i.e. as a before update event of HQ_File_Date.
Is there any other logic or process by which we can get this?
How do you know this? Are these two fields being filled by the user or automatically thru code? If they're being filled in by the user, how are you insuring that HQ_File_Ref is being filled in first?
The bottom line is, the code won't be run until both fields are filled.
Both these fields are filled up by user. In the form that has been designed, the first field to be entered is HQ_File_Ref & the second is HQ_File_Date.
Yes, u have got it right.... "The bottom line is, the code can't be run until both fields are filled."
The problem is that users don't always enter data in the manner that the developer assumes it's going to be entered in! They start entering a record, realize they're missing a piece of data, skip that field and enter the rest of the record, then go and find the missing data and enter it.
A printing company here used to have an advertising poster that had these clever sayings printed in dozens of fonts, the idea being to show the various fonts available to customers. The one I always remember is
"The problem with trying to make something idiot-proof is thst idiots are so darned ingenious!"
What linq is saying is that the user may not enter the data in the order that you intended. Maybe the user won't have have the value to fill in the HQ_File_Ref at that moment so he/she skips it and fills in the rest of the fields (including HQ_File_Date), then goes back to fill in HQ_File_Ref. Or maybe the user just isn't paying attention and accidentally click on another field and enters it first. Of course, this may not happen, but you are better off preparing for instances such as this.
The point here bijon is the fact you are not 'getting it' is why you posted the question in the first place. So, why not try the advice of those who 'do get it' and then maybe you will.
Honestly, not trying to be a smart-allec. But what linq is trying to tell you is correct. Unless you are presenting the fields for entry one-at-a-time and having them persist until valid data is being entered, then IT IS IMPOSSIBLE TO KNOW HOW A USER WILL ENTER THE DATA. Even then, you will need to test for valid data before progressing to the next step?
Well said, doco! The sad thing is, the code is tested and works! Bijon's object names have been plugged in! All he has to do is copy and paste the code in!
