Preventing duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I posted this a couple of days back. I would really appreciate some help
with this.

I have a form, frmClientDataEntry, containing basic details of a client.
When the user clicks a button they can edit these details. One of the
controls has the underlying table field set to no duplicates and I would
therefore like to have some code which will presumably go in the before
update event of the control so that the user sees a message box after the
application finds a duplicate saying "Duplicate entry. View record?" with
Yes and No buttons where clicking Yes takes the user to that record and the
entry in the control is reset to the original entry.

I am fairly new to VBA but keen to learn more.

Any help is appreciated

Thanks

Chris
 
Chris said:
Hi

I posted this a couple of days back. I would really appreciate some help
with this.

I have a form, frmClientDataEntry, containing basic details of a client.
When the user clicks a button they can edit these details. One of the
controls has the underlying table field set to no duplicates and I would
therefore like to have some code which will presumably go in the before
update event of the control so that the user sees a message box after the
application finds a duplicate saying "Duplicate entry. View record?" with
Yes and No buttons where clicking Yes takes the user to that record and the
entry in the control is reset to the original entry.

I am fairly new to VBA but keen to learn more.

Any help is appreciated

Thanks

Chris
Click to expand...

Actually, the Error event is the one you want. Something like this:

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

Dim strKey As String

Select Case DataErr
Case 3314
If MsgBox("Duplicate Entry - View Record?", vbYesNo) = vbYes
Then
strKey = Me!CustomerID
Me.Undo
With Me.RecordsetClone
.FindFirst "CustomerID=""" & strKey & """"
Me.Bookmark = .Bookmark
End With
End If
Response = acDataErrContinue
Case Else
Response = acDataErrDisplay
End Select

End Sub
 
Thanks Brian I will give this a try.

Couple of questions though:

I presume this code will go in the on error event of the form and if so does
this mean there will be no indication of a problem until the form is closed
after the customer id has been changed to a duplicate value? ( I was rather
hoping for something that picked it up on leaving the control).

Secondly I am not that familier yet with manipulating record sets. In the
code you have given does CustomerID mean the name of the text box control or
the field name of the underlying table ie the control source.

Thanks in advance for any further help or advice.

Chris
 
Chris said:
Thanks Brian I will give this a try.

Couple of questions though:

I presume this code will go in the on error event of the form and if so does
this mean there will be no indication of a problem until the form is closed
after the customer id has been changed to a duplicate value? ( I was rather
hoping for something that picked it up on leaving the control).

Secondly I am not that familier yet with manipulating record sets. In the
code you have given does CustomerID mean the name of the text box control or
the field name of the underlying table ie the control source.

Thanks in advance for any further help or advice.

Chris

:
Click to expand...

This code will execute when the user attempts to save the record (which
could be because they are attempting to close the form, or could equally be
because they are moving to a new record, or a subform, or have explicitly
requested the record to be saved).

If you want to test it when the control showing the primary key is actually
updated, you would need to code the AfterUpdate event for the control (NOT
the AfterUpdate event for the form). This has two disadvantages: (i) you
would need to explicitly read the database to see whether the value already
exists (the Dlookup function would be the easiest, though not necessarily
the most efficient, way to do this) and (ii) assuming this is, or may
become, a multi-user database, you must STILL code the form's error event.
Suppose that you test the key when the control is updated: there will then
be a period of some length before the record actually gets saved, during
which time another user could enter a record with the same key.

CustomerID is the name of the field, not the control.
 
Back
Top