Listbox of Potential Duplicates

[Mike Vytal]

I have a "New Entry" form which pops up a msgbox when a duplicate name is entered. Is there a way to pull up a listbox of the potential matches? The user should be able to select the correct one, and either double-click or hit a yes button, which would take the user directly to the selected record?

Thanks...

 

[DCrake]

Simple Software Solutions

Presumeably you code performs some sort of validation routine based on the user entry? Correct?
If so, then your next step is to create a small modal popup form that contains a listbox, Cancel button and a 'Use Me' button.

Senario:
User types in ACME in to customer name field, you perform a count and find you have 5 companies with the phrase "ACME" in their name - so which one does the user want?

Open your new form so that the list box displays the customer id, company name, first line of the address, etc. where the company name Like "*ACME*". This is useful, in this senerio, if you allow partial string comparison.

Have your 'Use Me' button disabled on form open. When the user clicks on the listbox to identify the correct company this will do two things a) enable the 'Use Me' button, b) pass the company ID to a preset variable.

When the user clicks on the 'Use Me' button your code will set the filter on the preceeding form based on the company id in the list box selected item in the popup form. Finally the popup form closes.

Code has been simplified for brevity.

CodeMaster:

 

[Mike Vytal]

Thanx DCrake...I'm pretty new to this...could you be please be more specific? I understand how to make the new popup form, and all of the buttons, but,

a. how do I 'perform a count'
b. what is partial string comparison
c. how do I write the code to set the filter with the 'Use Me' button?

Below is the code I've used for validation. It's on the after_update event on the last name field. I appreciate your help.

Private Sub txtLastName_AfterUpdate()

If Not IsNull(Me.txtLastName) And Not IsNull(Me.txtFirstName) Then
strContact = Me.txtLastName & Me.txtFirstName
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Participants", 2)
rst.FindFirst "[LastName] & [FirstName] Like '*" & strContact & "*'"
With rst
If .NoMatch Then
.Close
GoTo Exit_txtFirstName_BeforeUpdate
Else
If MsgBox("A " & !FirstName & " " & !LastName & " already exists. Continue update?", 260) = vbYes Then
.Close
GoTo Exit_txtFirstName_BeforeUpdate
Else
Cancel = True
Me.Undo
.Close
End If
End If
End With
End If

Exit_txtFirstName_BeforeUpdate:
Set dbs = Nothing


End Sub

 

[Mike Vytal]

Almost Got It!!!

DCrake, ok, here's what I figured out thus far...

I have created a 'New Entry' form, with txtFirstName and txtLastName fields. Once I type in the last name, if there are similar names that already exist, I get the 'Select Participants' form to open, which has a listbox that includes Name, HomeStName, and DOB, but it shows all records. I only want it to show potential matches to what's been entered in the 'New Entry' form.

I have figured out how to have what I choose in the 'Select Participants' form to open the matching record in the 'Participants' form.

All I need now is to figure out how to do the above highlighted in red.

Thanks...

 

[Mike Vytal]

OK DCrake, Think I've got it now!!!

What I did was create a query called qryMatches. Included the fields name, id, dob, homestname. In the name field criteria I used "Like ([forms]![new entry].[txtFirstName]&"*") & " " ([forms]![new entry].[txtLastName]&"*"). I use this query as the record source for my 'Select Participants' popup form. Seems to be working...I'll keep you posted.