form combo box

[bill crumpton]

I have created a combo box on a form that "Looks up values based on the selection in the combo box." However, if I have a list of people with the same last name to choose from in the combo box, I can only choose the first one on the list. For example, if there are two "Smiths" listed in the combo box and I click on the second "Smith" I get the information for the first "Smith". Any help would be grealtly appreciated.

BAC

P.S. When I get the information on the form for the first "Smith" I can use the Navigation buttons on the bottom of the form to go to the next record, but I would like to hide the navigation buttons and just be able to choose from the combo box.

[This message has been edited by bill crumpton (edited 11-29-2000).]

 

[Jack Cowley]

I would suggest that you have three or four columns in your combo box. The unique ID for each customer record, LastName, FirstName and middle initial. This way you can see John R. Smith and John M. Smith and select the correct one, then the uniqueId with that record will find the right Mr. Smith.

If you create the combo box using the Wizard it will create a combo box that will display the record you want based on your selection in the combo box.

If you do not have a unique ID for each record then you will need to look for your record matching first and last name and possibly the middle initial as well.

[This message has been edited by Jack Cowley (edited 11-29-2000).]

 

[bill crumpton]

Thanks for your quick response Jack. However, I do have many columns in the combo box. I have lastname, firstname, mi, dob, race, sex and ss# and the person id#. I just can't figure out why I can navigate fine through the records with the navigation bar at the bottom of the form, but the combo box will only select one of the records where the last name are the same. I used the combo box wizard in creating the combo box on the form. This shouldn't be very difficult. Any help will be greatly appreciated.

BAC

 

[Jack Cowley]

Sounds like you are searching your records by the LastName rather than the PersonID #. PersonID # should be your bound column in the Combo box.

I assume that there is some code similar to this in the After Update event of your Combo box:

Me.RecordsetClone.FindFirst "[PersonID#] = " & Me![ComboName]
Me.Bookmark = Me.RecordsetClone.Bookmark

If it says LastName instead of PersonID# then that is the reason the Combo box finds the first Smith. Be sure that PersonID# is the first column in the Combo box or you will need to modify the code above to get the data from the correct column.

And you could us SS# as your unique ID instead of PersonID#. Whatever works best for you.

 

[SomeGuy]

I had a similar problem with a combobox that I had. It turned out that it was not using the ID#, but rather the name.

I would like to suggest that you use a query that concatonates the names together and you can also sort them alphabetically quite easily.

Something like this might work:
FullName: [LastName] & IIf(IsNull([FirstName]),"",", " & [FirstName]) & " " & [MiddleInitial]

Just a thought because it looks nicer than having several columns in the combobox