Checking for Possible Duplicate Records

  • Thread starter Thread starter Brennan
  • Start date Start date
B

Brennan

Hello:

I have a database that tracks Bids for a Contractor.
There is a one to many relationship between Jobs and Bids.

After using the application for awhile, it has become
apparent that the folks doing the data entry do not
always check to see if a job has already been entered.
There is a unique identifier for each job but that is
assigned at the time of data entry.

I would like to put in some code that when a record is
entered, the system will check existing jobs for possible
duplicate data in the Job Name and Location field and if
possible dups are found, give the user an option to view
the records.

I tried some possible solutions using the find duplicates
query but was not satisfied with the results.

Any suggestions would be appreciated

Brennan
 
Hi Brennan

If you want to ensure that you NEVER have two jobs with the same JobName and
Location, then you could add a unique composite index to your table,
comprising those two fields.

On the other hand, if you want to allow such records, but with a warning to
the user, you could create a query on your Jobs table with the criteria:
[JobName]=Forms![frmJobEntry]![JobName]
and [Location]=Forms![frmJobEntry]![Location]

Then, in your form's BeforeUpdate event, you can do something like this:

If Me.NewRecord Then
If DCount("*", "qryDuplicateJobs") > 0 then
DoCmd.OpenForm "frmDuplicateJobs", WindowMode:=acDialog
End If
End If

The form would need to be based on the same query and would show the
existing duplicates.
 
I would like to put in some code that when a record is
entered, the system will check existing jobs for possible
duplicate data in the Job Name and Location field and if
possible dups are found, give the user an option to view
the records.
Click to expand...

You can do this in either the Form's BeforeUpdate event, or (with a
little more hassle given the two fields) in the BeforeUpdate events of
both Job Name and Location.

For the Form event, you'ld open a recordset based on the Form's
recordsource, and search it to see if this combination already exists:
something like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim rs As DAO.Recordset
Dim iAns As Integer
Set rs = Me.RecordsetClone
rs.FindFirst "[JobName] = '" & Me!txtJobName & _
"' AND Location = '" & Me!txtLocation & "'"
If Not rs.NoMatch ' i.e. there was something found
iAns = MsgBox("This job already exists! Add it anyway?" _
& vbCrLf & "Click Yes to add, No to jump to existing record, " _
& vbCrLf & "Cancel to go back to editing this record", _
vbYesNoCancel)
Select Case iAns
Case vbYes
' do nothing, let the record be added
Case vbNo
Cancel = True
Me.Bookmark = rs.Bookmark ' move to found record
Case vbCancel
Cancel = True ' suppress update, return to form
End Select
End If
End Sub
 
Back
Top