duplicate records upon entry in an Access 2000 form

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

Guest

I am trying to code a form tied to my data base which will let me know if I
am trying to enter a duplicate record and give me a message box giving the
option of entering the new record or going to the existing record. I am
having trouble in the form design as most of this type of coding is very new
to me - can someone walk me through the actual coding, where it goes - in
other words help me walk through what I am sure is a pretty simple thing for
most - but seems almost impossible for me.

thanks
 
It's impossible to walk you through code as exactly what you mean by
'duplicate' is unclear, but the general way that I'd do it would be:

Create an unbound form allowing the user to enter the fields of data that
determine whether that record is a duplicate or not.
On an OK button, check for existence of a 'duplicate'
If there is one, pop a YesNo box. Do you want to replace?
If yes, delete the old record and append the new (or just clear the other
fields)
If no, go to the old record.

Let us know if you have a problem with any of those steps.
 
I am trying to code a form tied to my data base which will let me know if I
am trying to enter a duplicate record and give me a message box giving the
option of entering the new record or going to the existing record. I am
having trouble in the form design as most of this type of coding is very new
to me - can someone walk me through the actual coding, where it goes - in
other words help me walk through what I am sure is a pretty simple thing for
most - but seems almost impossible for me.

thanks
Click to expand...

This is actually a bit of work, and requires some knowledge of the
database structure (which I don't have, so must here write in
generalities).

Use the Form's BeforeUpdate event, or the BeforeUpdate event of the
control bound to the field which defines a "duplicate" if there is
only one. Let's say there are several fields, I'll just arbitrarily
say LastName, FirstName, Address and City.

In the Form's BeforeUpdate event (on the Form's Properties, Events
tab) click the ... icon and invoke Code Builder. Input code like:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset ' define a variable for a Recordset
Dim strSQL As String ' define a string variable
Dim iAns As Integer
Set rs = Me.RecordsetClone ' get the Form's Recordset data
strSQL = "[LastName] = " & Chr(34) & Me!txtLastName & Chr(34) _
& " AND [FirstName] = " & Chr(34) & Me!txtFirstName & Chr(34) _
& " AND [Address] = " & Chr(34) & Me!txtAddress & Chr(34)
' define a SQL WHERE clause like
' [LastName] = "O'Leary" AND [FirstName] = "Kelly" AND [Address] =
' "333 Main St."
rs.FindFirst strSQL ' See if this name & address exists in the table
If Not rs.NoMatch Then
' There was a match: give the user the option
iAns = MsgBox("This name seems to exist. Add it anyway?" _
& vbCrLf & "Click Yes to add, No to jump to the found record," _
& vbCrLf & "Cancel to erase the screen and start over:", _
vbYesNoCancel
Select Case iAns
Case vbYes ' user clicked Yes, add it anyhow - e.g. do nothing
Case vbNo ' No, don't add, jump instead
Me.Undo ' erase the form
Cancel = True ' cancel the addition to the table
Me.Bookmark = rs.Bookmark ' jump to the found record
Case vbCancel ' neither add nor jump, just cancel
Me.Undo
Cancel = True
End Select
End If
Set rs = Nothing ' clean up after yourself
End Sub


John W. Vinson[MVP]
 
Back
Top