Combobox Search defaulting to primary key rather than selection

[dwilson]

I have generated a combobox search for my form and I am able to select names that are different and the information is generated on the form. However, when I have two names that have the same last name my selection will default to the primary key instead of the name I selected in the combobox. Here is the code I am using:

Here is my RowSource: SELECT [Client Info].CltLname, [Client Info].CltFname, [Client Info].DOB, [Client Info].[Last 4 SSN] FROM [Client Info] ORDER BY [Client Info].CltLname, [Client Info].CltFname;

I have run a query to sort by CltLname and then by CltFname

Private Sub cboClientSearch_AfterUpdate()


'Moves to Customer Name text box and
'finds the record of whatever name is selected in the combo box
DoCmd.ShowAllRecords
Me!CltLname.SetFocus
DoCmd.FindRecord Me!cboClientSearch

'Set value of combo box equal to an empty string
Me!cboClientSearch.Value = ""
End Sub

Does anyone know how to get the combobox to display what I have selected instead of defaulting to the earliest primary key associated with the same last name?

[Beetle]

Does anyone know how to get the combobox to display what I have selected instead of defaulting to the earliest primary key associated with the same last name?

Yes. Have the Row Source query of the combo box return the Primary Key value in the bound column, instead of the Last name, and search based on the Primary Key.

[dwilson]

Thank you for your prompt response, but the problem I am experiencing is that the earliest primary key value is what is returned. For example, suppose I have someone by the name of Smith, John with Social Security Number 1234 with a primary key assigned as 5 and another Smith, John with Social Security number 6789 with primary key of 6. When they are listed in the combobox and I select Smith, John with Social Security number 6789 primary key 6 my selection will default back to Smith, John Social Security number 1234 primary key 5 even though I specifically selected Smith, John SSN 6789.

Long story short, I'm trying to get the combobox to select the correct name instead of defaulting to the earliest same name entered that has the lowest primary key number assigned to it.

Thanks for looking into this!

[Beetle]

Looking at the Row Source query you posted, you're not returning the PK value to the combo box at all. If the bound column in this combo box is the first column, then what the combo is storing is CltLName. So, when you perform the search you're really just telling the db to find a record with a last name of "Smith", with no regard to the primary key at all.

What's the name of the PK field in the table that is used as the Row Source of the combo box?

[dwilson]

The name of my PK is "Client ID". I originally had it set up to locate a client based on the PK, but then the CB would only display the PK and I wasn't able to search by name. I tried to work around this by having it search by name instead. If you know of a way for me to search by name, have the name displayed in the CB, and sort by PK that would be perfect!

Thank you for your help!

[Beetle]

Use the Column Widths property of the combo box to control what's displayed. For example, if your combo box had the following Row Source;

Select ClientID, CtlLName & ", " & CltFName As LastFirst
Order By CltLName

Then you set the Column Widths to;

0", 2" (or whatever width works best for the second column)

The combo box will store the ClientID but it will display the Client Name (in the above example that would be the alias field LastFirst which returns - Smith, John)

So when you search on the combo box you are actually searching the ClientID value, not the name, so an exact match can be returned.

[dwilson]

Hi Beetle,

When I do what you suggested I am unable to type in the client's name in order to search. The only way it is letting me search is if I know the client's PK#. However, when I select a client it does only display the client's name which is helpful, but I need to be able to search by name and not PK#. Any suggestions?

Thanks

[Beetle]

When using a combo box you can begin typing and the list will automatically move to records (in this case names) that match what you type. So if you type the letters Sm, the list should move to names like Smith, Smitty, Smithers, etc. Then the user only needs to select the appropriate one and the After Update event of the combo box will find the matching record based on the ClientID.

Maybe I'm misunderstanding what you want. I thought you wanted to find a specific record. If you just want to be able to enter the name Smith and have it return all records with that name, then this is the wrong approach.

[dwilson]

Exactly, I think we are beginning to understand one another. I do want the list to generate all the people with a last name of "Smith". What I'm running into is that when all the Smith, Johns are generated and I try to select the second Smith, John it defaults back to the first Smith, John because the first Smith, John has the lowest PK. What I'm trying to figure out is how it will specifically select the second Smith, John when that record is selected rather than defaulting back to the first Smith, John.

Do you have any creative solutions to this? I've also started looking at a search box based on a txt box and list box, but I'm so new to access I have to research how to do this. If you know how to do this I would love to see how it is done.

Thanks again for all of your help!

[Beetle]

So you have a combo box which displays all the names (and stores the ID value). When a user wants to quickly find the name Smith they simply start typing in the combo box and the list filters for that name, then they simply choose the correct one (based on the values displayed in the other columns of the combo box).

This is basically default behavior for a combo box. You're saying for some reason this won't work for you?

[dwilson]

Yes, I am able to start typing a name and select a name to generate the associated record as long as it is a different name. The problem occurs when I have two people with the same name. When I try to select someone with the exact same name the combobox will return the same name with the lowest PK instead of the name that was actually selected. So if there are two people with the name Smith, John and I select the second Smith, John the combobox generates the record for the first Smith, John instead of the second Smith, John that was actually selected.

I hope that makes sense. I'll try to explain it differently if it does not.

Thanks again!

[dwilson]

Ok, I believe this has been fixed. I found that I needed to link the combobox to a table rather than a query. Thank you for all of your persistence and assistance.

Take care