create a list box for duplicate client names

[Steve]

I have a client data table/form. To navigate to different client data forms,
I have created a combo-box on a pop-up form. When a last name is entered into
this field the selected client data form is opened and the pop-up form closes
(see code below). In situations where there are duplicate last names (for
example, there are several "Smiths" in the database) I would like to populate
a list box (DuplicateNames List Box) on the pop-up form with the list of
duplicate Names, DOB, CLient number from which the user can choose the
correct client. Any help would be greatly appreciated. Thanks.

Private Sub SearchLastName_AfterUpdate()
On Error GoTo SearchLastName_AfterUpdate_Err
If IsNull(Me![SearchLastName]) Then
response = MsgBox("You Did Not Select a Record to Search For", 32, "No
Record Selected")
Else
DoCmd.SelectObject acForm, "FrmClient"
DoCmd.GoToControl "ClientNumber"
DoCmd.FindRecord Me![SearchLastName]
DoCmd.GoToControl "Last Name"
SendKeys "{ESC}", True
DoCmd.Close acForm, "frmFindClient"
End If
Exit Sub

SearchLastName_AfterUpdate_Err:
MsgBox "Error is " & Error$, 16
Exit Sub
End Sub

 

[Jeff Boyce]

Steve

If you are already using a combo box to list last names, why not just add
the additional fields that your user would inspect to differentiate among
the "Smiths"? This would allow the user to select the correct Smith without
having to inspect another list (and would save you the coding to do the
additional listing of "duplicates"). Or have I misunderstood -- are you
saying that you have more than one Smith, AND more than one duplicate DOB,
Client Number, etc.?

 

[Steve]

Yes. That will work. I was trying to be unnecessarily complicated/fancy. How
do I get the combobox to open automatically to display the names (without
clicking the arrow on the right side of the box).

Steve

Steve

If you are already using a combo box to list last names, why not just add
the additional fields that your user would inspect to differentiate among
the "Smiths"? This would allow the user to select the correct Smith without
having to inspect another list (and would save you the coding to do the
additional listing of "duplicates"). Or have I misunderstood -- are you
saying that you have more than one Smith, AND more than one duplicate DOB,
Client Number, etc.?

--
Regards

Jeff Boyce
<Office/Access MVP>

I have a client data table/form. To navigate to different client data forms,
I have created a combo-box on a pop-up form. When a last name is entered into
this field the selected client data form is opened and the pop-up form closes
(see code below). In situations where there are duplicate last names (for
example, there are several "Smiths" in the database) I would like to populate
a list box (DuplicateNames List Box) on the pop-up form with the list of
duplicate Names, DOB, CLient number from which the user can choose the
correct client. Any help would be greatly appreciated. Thanks.

Private Sub SearchLastName_AfterUpdate()
On Error GoTo SearchLastName_AfterUpdate_Err
If IsNull(Me![SearchLastName]) Then
response = MsgBox("You Did Not Select a Record to Search For", 32, "No
Record Selected")
Else
DoCmd.SelectObject acForm, "FrmClient"
DoCmd.GoToControl "ClientNumber"
DoCmd.FindRecord Me![SearchLastName]
DoCmd.GoToControl "Last Name"
SendKeys "{ESC}", True
DoCmd.Close acForm, "frmFindClient"
End If
Exit Sub

SearchLastName_AfterUpdate_Err:
MsgBox "Error is " & Error$, 16
Exit Sub
End Sub

 

[Jeff Boyce]

Steve

If I recall correctly, there's a command that drops the list down. You
could create an event procedure for the GotFocus event.

Is there a reason you have chosen a combo box over a list box?

--
Regards

Jeff Boyce
<Office/Access MVP>

Yes. That will work. I was trying to be unnecessarily complicated/fancy. How
do I get the combobox to open automatically to display the names (without
clicking the arrow on the right side of the box).

Steve

Steve

If you are already using a combo box to list last names, why not just add
the additional fields that your user would inspect to differentiate among
the "Smiths"? This would allow the user to select the correct Smith without
having to inspect another list (and would save you the coding to do the
additional listing of "duplicates"). Or have I misunderstood -- are you
saying that you have more than one Smith, AND more than one duplicate DOB,
Client Number, etc.?

--
Regards

Jeff Boyce
<Office/Access MVP>

I have a client data table/form. To navigate to different client data forms,
I have created a combo-box on a pop-up form. When a last name is

entered
into

this field the selected client data form is opened and the pop-up form closes
(see code below). In situations where there are duplicate last names (for
example, there are several "Smiths" in the database) I would like to populate
a list box (DuplicateNames List Box) on the pop-up form with the list of
duplicate Names, DOB, CLient number from which the user can choose the
correct client. Any help would be greatly appreciated. Thanks.

Private Sub SearchLastName_AfterUpdate()
On Error GoTo SearchLastName_AfterUpdate_Err
If IsNull(Me![SearchLastName]) Then
response = MsgBox("You Did Not Select a Record to Search For", 32, "No
Record Selected")
Else
DoCmd.SelectObject acForm, "FrmClient"
DoCmd.GoToControl "ClientNumber"
DoCmd.FindRecord Me![SearchLastName]
DoCmd.GoToControl "Last Name"
SendKeys "{ESC}", True
DoCmd.Close acForm, "frmFindClient"
End If
Exit Sub

SearchLastName_AfterUpdate_Err:
MsgBox "Error is " & Error$, 16
Exit Sub
End Sub

 

[PC Datasheet]

Yes!

Me!NameOfCombobox.DropDown


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

If you can't get the help you need in the newsgroup, I can help you for a
very reasonable fee. Over 1000 Access users have come to me for help.
Need a month calendar or 7 day calendar? Need appointment scheduling? Need
room reservations scheduling? Need employee work scheduling? Contact me!

Steve

If I recall correctly, there's a command that drops the list down. You
could create an event procedure for the GotFocus event.

Is there a reason you have chosen a combo box over a list box?

--
Regards

Jeff Boyce
<Office/Access MVP>

Yes. That will work. I was trying to be unnecessarily complicated/fancy. How
do I get the combobox to open automatically to display the names (without
clicking the arrow on the right side of the box).

Steve

Steve

If you are already using a combo box to list last names, why not just add
the additional fields that your user would inspect to differentiate among
the "Smiths"? This would allow the user to select the correct Smith without
having to inspect another list (and would save you the coding to do the
additional listing of "duplicates"). Or have I misunderstood -- are
you
saying that you have more than one Smith, AND more than one duplicate DOB,
Client Number, etc.?

--
Regards

Jeff Boyce
<Office/Access MVP>

I have a client data table/form. To navigate to different client data
forms,
I have created a combo-box on a pop-up form. When a last name is entered
into
this field the selected client data form is opened and the pop-up
form
closes
(see code below). In situations where there are duplicate last names (for
example, there are several "Smiths" in the database) I would like to
populate
a list box (DuplicateNames List Box) on the pop-up form with the list of
duplicate Names, DOB, CLient number from which the user can choose
the
correct client. Any help would be greatly appreciated. Thanks.

Private Sub SearchLastName_AfterUpdate()
On Error GoTo SearchLastName_AfterUpdate_Err
If IsNull(Me![SearchLastName]) Then
response = MsgBox("You Did Not Select a Record to Search For",
32, "No
Record Selected")
Else
DoCmd.SelectObject acForm, "FrmClient"
DoCmd.GoToControl "ClientNumber"
DoCmd.FindRecord Me![SearchLastName]
DoCmd.GoToControl "Last Name"
SendKeys "{ESC}", True
DoCmd.Close acForm, "frmFindClient"
End If
Exit Sub

SearchLastName_AfterUpdate_Err:
MsgBox "Error is " & Error$, 16
Exit Sub
End Sub

 

[Keith W]

Even advertising while *asking* questions... Nice!

Arno R

With a bit of luck he'll contact himself and disappear up his own arse.