Duplicate Data Warning

Question

Hello all,

In a form, Users enter client information on a daily basis.  Repeat clients are very common, but Users are not utilizing the dropdowns to search the client and will just enter the info again.  This has created a lot of duplicates.

I would like a "This name is already in the database" warning to popup when a client name is already in the database.  They also require the option to "ignore" if the duplicate name is ok to be added.

The client first and last names are entered in separate fields.

Is it possible to create this in a query?

As always, your help is appreciated

Answer 1

Hi John,

I made all of the necessary changes and the code compiles, but when I add a test duplicate record to the form, there is no message popup. It won't let me leave the duplicate record or add a new one...all I can do is press esc to remove all data.

Any suggestions?

Answer 2

The code needs a closing parenthesis after the vbYesNoCancel line... apologies for the typo. The entire code with your names should be:

Private Sub Form_BeforeUpdate(Cancel as Integer)

Dim iAns As Integer

Dim rs As DAO.Recordset

Dim vUserID As Variant' assuming you have a Long Int or Autonumber UserID

vUserID = DLookUp("UserID", "TblUSERLIST", "Name2 = """ & Me.txtName2 _

  & """ AND Name1= """ & Me!txtName1 & """")

If IsNull(vUserID) Then ' not a match, all is well

   Cancel = False

Else

   iAns = MsgBox("A client of this name is already in the database." _

    & vbCrLf & "Click YES if this is a different person," _

    & vbCrLf & "Click No to go to the existing record," _

    & vbCrLf & "Click Cancel to erase the form and start over:", vbYesNoCancel)

   Select Case iAns

      Case vbYes

           Cancel = False ' let the addition proceed

      Case vbNo

           Cancel = True ' cancel the addition

           Me.Undo ' erase the form

           rs = Me.RecordsetClone

           rs.FindFirst "[UserID] = " & vUserID

           Me.Bookmark = rs.Bookmark

      Case vbCancel

           Cancel = True

           Me.Undo ' just blank the form and quit

    End Select

End If

End Sub

This assumes (since you didn't say) that the Table has fields named Name1 and Name2, and the Form has Controls (not fields, forms don't HAVE fields) named txtName1 and txtName2; and that tblUserList has a Primary Key field named UserID. If your table has a different primary key use it in place of UserID; if it doesn't have a Primary Key... well, then you need to correct the erroneous structure of your database by adding one, and setting up appropriate relationships to other tables.

Answer 3

Thanks very much for your response.  I entered your code as instructed with the appropriate changes, and received an invalid use of property error on the following: (highlighting the rs= only)

rs = Me.RecordsetClone

Can you help?

Answer 4

I assume the form is for entering something like orders or some other transaction associated with a client.  It's difficult to see how duplication can happen by typing in a name in such cases if the Orders (or whatever) table is correctly normalized as the table should not include the client name, but a foreign key column (field), usually a numeric ClientID or similar, which references the primary key ClientID  column (usually an autonumber) of a Clients table.  The name of course cannot be the key as duplication of names is legitimate.

With the combo box's LimitToList and AutoExpand property both set to True (Yes), when a user types in a name the first match should progressively be shown as each character is entered.  If they type an existing client name incorrectly the new name should be rejected, and an error message will be displayed, or if code has been entered in the control's NotInList event procedure, the user will be given the option of adding a new client.

You'll find examples of the use of the NotInList event in NotInList.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

The first form has a combo box of contacts, concatenating the first and last names and distinguishing those of the same name by address, into which a new contact can be typed, which opens the contacts form to insert a row into the Contacts table.  However, as the NotInList event procedure will not be executed if an existing name is entered, this is not entirely satisfactory so the form also illustrates how a new contact can be inserted by clicking the button at the bottom of the form. 

In my demo the first form is unbound, but in your case it would be a bound form, with the combo box bound to the ClientID column.

Answer 5

Sure. You could use code like this in the Form's BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel as Integer)

Dim iAns As Integer

Dim rs As DAO.Recordset

Dim vClientID As Variant' assuming you have a Long Int or Autonumber ClientID

vClientID = DLookUp("ClientID", "Clients", "LastName = """ & Me.txtLastName _

  & """ AND FirstName = """ & Me!txtLastName & """")

If IsNull(vClientID) Then ' not a match, all is well

   Cancel = False

Else

   iAns = MsgBox("A client of this name is already in the database." _

    & vbCrLf & "Click YES if this is a different person," _

    & vbCrLf & "Click No to go to the existing record," _

    & vbCrLf & "Click Cancel to erase the form and start over:", vbYesNoCancel

   Select Case iAns

      Case vbYes

           Cancel = False ' let the addition proceed

      Case vbNo

           Cancel = True ' cancel the addition

           Me.Undo ' erase the form

           rs = Me.RecordsetClone

           rs.FindFirst "[ClientID] = " & vClientID

           Me.Bookmark = rs.Bookmark

      Case vbCancel

           Cancel = True

           Me.Undo ' just blank the form and quit

    End Select

End If

End Sub