disallow duplicate entries

[Sadie Hewgill]

error message for duplicate entry

I have two primary keys and when duplicates are entered in the form, it is not allowed. I would like to display a custom error message that gives the user the option to go back and change the information they entered, or to be taken to the form with the duplicate information. So far my code is doing half of this. The Cancel option works fine, but when I press OK to be taken to the duplicate record, I am just taken back to the first record. Here's my code:

Private Sub Form_Error(DataErr As Integer, Response As Integer)


Dim strWhere As String
Dim rst As DAO.Recordset

On Error GoTo HandleErrors

Select Case DataErr
Case 3022 'Duplicate record
If MsgBox("This record already exists. Click OK to go to the existing record, or Cancel to change your entry", vbOKCancel, "Duplicate record ") = vbOK Then
strWhere = "MfrPartNumbers_MfrPartNumber ='" & Me.MfrPartNumbers_MfrPartNumber & "' AND MfrPartNumbers_Mfr" = " & MfrPartNumbers_Mfr & "
Me.Undo
Set rst = Me.RecordsetClone
With rst
.FindFirst strWhere
Me.Bookmark = rst.Bookmark
.Close
End With
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
Case Else
End Select

ExitHere:
Exit Sub

HandleErrors:
Select Case Err.Number
Case Else
MsgBox Err.Description & " (" & Err.Number & ")"
End Select
Resume ExitHere
End Sub

Thank you,
Sadie

 

[John Big Booty]

The way I would do this is not put any constraints on the data at table level, but use Dlookup(), in the field's Before Update event to check to see if the value already exists. If it does you can then use the existing ID collected by the Dlookup() function to Find the existing record and display it in your form.

 

[Sadie Hewgill]

Ok, I am pretty new at using VBA, so most of the code I have has been modified from stuff I've found online. So you are suggesting I use "before update" instead of "on error", and DLookup will replace the line: strWhere = "MfrPartNumbers_MfrPartNumber ='" & Me.MfrPartNumbers_MfrPartNumber & "' AND MfrPartNumbers_Mfr" = " & MfrPartNumbers_Mfr & "
Will the rest be the same?

 

[Sadie Hewgill]

Also, there are two fields that need to be unique together, but can have their own duplicate values. How does Before update work with two fields? Right now they are my primary keys, so an error message displays any ways.

 

[Sadie Hewgill]

Still using the same code I have for now: I've just noticed that when I select OK, the record that I want it to go to appears for a second before the user is taken to the first record. Is there a way to make this record stay?

 

[boblarson]

Use the Form's BEFORE UPDATE EVENT. You can use a DCount to find out if the combination exists and if it does you can cancel the event before it even gets to be an error (which is much better). Also you don't need to worry about bookmarks or anything because the user never gets off of the current record if you use

Cancel = True

if the validation fails. You don't rely on the table validation to do this. If you do it is too late and is more painful for you.

 

[Sadie Hewgill]

how do I do Dcount with two fields?

 

[boblarson]

how do I do Dcount with two fields?

Something like this:

If DCount("*", "tableNameHere", "[Field1] = " & Me!FieldName & " AND [Field2] = " & Chr(34) & Me!Field2Name & Chr(34)) > 0 Then
[B]Cancel = True[/B]
Msg "Some Message here"
End If

So I gave you an example of both how you use numbers and text in the DCount. The first one Field1 is how you do it if numeric and the secont Field2 is if it is text.

Also, You don't need an ELSE with this because if the count comes back as zero (0) then you can just let it update which would need no user interaction.

Also use the "*" (asterisk inside two double quotes) is what you choose for the DCount so it just counts records that match your criteria. No need for a field name in this instance.

Hope that helps.

 

[Sadie Hewgill]

I implemented the suggestions you gave me, and they work great! Thanks a lot! I was wondering though, I want the error message to take the user to view the record that was already existing with the values they entered if they select OK. if I don't use bookmarks, how do I do this?

 

[Sadie Hewgill]

Here is my code as it stands, but I still have the same problem of the the OK option taking me to the first record instead of the one that has the values that the user typed. It also gives me the error message before every update, not just the ones that have problems.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "Fasteners_mm", "[Mfr] = " & Chr(34) & Me!Mfr & Chr(34) & " AND [MfrPartNumber] = " & Chr(34) & Me!MfrPartNumber & Chr(34)) > 0 Then
Cancel = True
If MsgBox("The record you have entered already exists, Press Ok to be taken to the existing record, or Cancel to edit this record.", vbOKCancel, "Duplicate record ") = vbOK Then
strWhere = "MfrPartNumber ='" & Me.MfrPartNumber & "' AND Mfr" = " & Mfr & "
Me.Undo
Set rst = Me.RecordsetClone
With rst
.FindFirst strWhere
Me.Bookmark = rst.Bookmark
.Close
End With
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End If

End Sub

 

[boblarson]

Here is my code as it stands, but I still have the same problem of the the OK option taking me to the first record instead of the one that has the values that the user typed. It also gives me the error message before every update, not just the ones that have problems.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
If DCount("*", "Fasteners_mm", "[Mfr] = " & Chr(34) & Me!Mfr & Chr(34) & " AND [MfrPartNumber] = " & Chr(34) & Me!MfrPartNumber & Chr(34)) > 0 Then
Cancel = True
If MsgBox("The record you have entered already exists, Press Ok to be taken to the existing record, or Cancel to edit this record.", vbOKCancel, "Duplicate record ") = vbOK Then
strWhere = "MfrPartNumber ='" & Me.MfrPartNumber & "' AND Mfr" = " & Mfr & "
Me.Undo
Set rst = Me.RecordsetClone
With rst
.FindFirst strWhere
Me.Bookmark = rst.Bookmark
.Close
End With
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End If

End Sub

Your strWhere is malformed. It would need to be just like the DCount one.

strWhere = "[Mfr] = " & Chr(34) & Me!Mfr & Chr(34) & " AND [MfrPartNumber] = " & Chr(34) & Me!MfrPartNumber & Chr(34))

 

[Sadie Hewgill]

I swapped the strWhere line for the one you gave me, but it is giving me a compiler error at that line. I didn't change anything else about the code, but here it is again. There's probably something small wrong with it, but I don't know enough about VBA syntax to pick it out.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "Fasteners_mm", "[Mfr] = " & Chr(34) & Me!Mfr & Chr(34) & " AND [MfrPartNumber] = " & Chr(34) & Me!MfrPartNumber & Chr(34)) > 0 Then
Cancel = True
If MsgBox("The record you have entered already exists, Press Ok to be taken to the existing record, or Cancel to edit this record.", vbOKCancel, "Duplicate record ") = vbOK Then
strWhere = "[Mfr] = " & Chr(34) & Me!Mfr & Chr(34) & " AND [MfrPartNumber] = " & Chr(34) & Me!MfrPartNumber & Chr(34))
Me.Undo
Set rst = Me.RecordsetClone
With rst
.FindFirst strWhere
Me.Bookmark = rst.Bookmark
.Close
End With
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End If

End Sub


Thank you!

 

[Sadie Hewgill]

oh, never mind, I just needed to delete a bracket. It's working now, thank you so much for your help!

 

[Sadie Hewgill]

Just one more thing,
I am going to do a similar thing to this using a different form and tables, only I am using an index of 10 fields to see if any duplicates exist. I want the fields to be ignored if they are empty though. I would just set this on the description of the index, as well as the unique property, but for some reason Access is not allowing me to do this. I can create indexes, but I can't change the properties where I am supposed to be able to. I used to have this functionality, but for whatever reason it has disappeared. Is there a way to get around this or should I reinstall Access? And if I do, will this wipe out my database?
Thanks,
Sadie

 

[Sadie Hewgill]

Is there a way I can use a "find duplicates" query instead of an index? Maybe write macro to have it run before update, and then if it finds a duplicate, it will display an error message? The difference is with this other table, there may be a very similar part that will have all the same fields as another record except for the ID field. I just want a warning that a similar record exists to pop up, so the user can check if they are accidentally entering the same thing. I don't want to completely forbid it. Any ideas?

 

[Sadie Hewgill]

I put all of the fields i would like to check for duplicates in the same code. I would like it to allow me to leave these fields blank though, and it can count blank fields as being identical too, or just ignore them. Here's my code so far:


Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "Fasteners_mm", "[RawMaterial] = " & Chr(34) & Me!Combo164 & Chr(34) & " AND [RawMaterialType] = " & Chr(34) & Me!Combo156 & Chr(34) & " AND [ThicknessDiameter] = " & Me!Combo148 & "AND [Width] = " & Me!Combo140 & " AND [Length] = " & Me!Combo132 & " AND [Colour] = " & Chr(34) & Me!Combo124 & Chr(34) & "AND [Finish] = " & Chr(34) & Me!Combo116 & Chr(34) & " AND [HeatTreat] = " & Chr(34) & Me!Combo100 & Chr(34) & " AND [ReleaseDate] = " & Chr(34) & Me!ReleaseDate & Chr(34) & " AND [Designer] = " & Chr(34) & Me!Combo62 & Chr(34)) > 0 Then
Cancel = True
If MsgBox("The record you have entered already exists, Press Ok to be taken to the existing record, or Cancel to edit this record.", vbOKCancel, "Duplicate record ") = vbOK Then
strWhere = "[RawMaterial] = " & Chr(34) & Me!Combo164 & Chr(34) & " AND [RawMaterialType] = " & Chr(34) & Me!Combo156 & Chr(34) & " AND [ThicknessDiameter] = " & Chr(34) & Me!Combo148 & Chr(34) & "AND [Width] = " & Chr(34) & Me!Combo140 & Chr(34) & " AND [Length] = " & Chr(34) & Me!Combo132 & Chr(34) & " AND [Colour] = " & Chr(34) & Me!Combo124 & Chr(34) & "AND [Finish] = " & Chr(34) & Me!Combo116 & Chr(34) & " AND [HeatTreat] = " & Chr(34) & Me!Combo100 & Chr(34) & " AND [ReleaseDate] = " & Chr(34) & Me!ReleaseDate & Chr(34) & " AND [Designer] = " & Chr(34) & Me!Combo62 & Chr(34)
Me.Undo
Set rst = Me.RecordsetClone
With rst
.FindFirst strWhere
Me.Bookmark = rst.Bookmark
.Close
End With
Response = acDataErrContinue
Else
Response = acDataErrContinue
End If
End If

End Sub

Thanks!

 

[Sadie Hewgill]

This applies to some earlier posts about the original code for an error message:
It took me awhile to notice this, but the error message pops up when ever I edit data that is not part of my primary key in the form. I guess it is looking through the table and sees that the same record already exists and thinks it's a duplicate. Is there a way to make it only perform this operation of checking for duplicates when the primary keys Mfr and Mfr Part Number are changed?
Thanks,
Sadie