Open a form showing existing duplicates

[dryc]

Could someone tell me how to setup a query that will look for duplicate records in a field after update and only have a form with the duplicates popup if there are duplicates?

 

[papparuff]

Sure,

In the field you want to check on, in it's After Update event you could use code such as:

Private Sub txtMyField_AfterUpdate()
' Go to Tools > References and insure that
' there is a reference to
' Microsoft DOA x.x Object Library
' where x.x is the latest version you have

Dim rst As DAO.Recordset
Dim varBookmark As Variant

If Not IsNull(txtMyField) Then
Set rst = Me.RecordsetClone

'Find the record that matches the control
With rst
varBookmark = Me.Bookmark
.FindFirst "[CustomerName] = " & chr(34) & txtMyField & chr(34)

If not .NoMatch Then
DoCmd.OpenForm "YourFormName", , , "CustomerName = " & Chr(34) & txtMyField & Chr(34)
End If

.Close
End With
Set rst = Nothing
End If

End Sub

In the form that you want opened, set the recordsource so that you display the info you want to show when there are duplicates. John Ruff - The Eternal Optimist