Combo box will not display the correct record?

[MariaL]

Hello everyone. I have a combo box that sorts by last name. If I have three "Smiths", and I choose the second "Smith" on the list, it still displays the first "Smith" on the list. How can I get it to display the record for the actual "Smith" chosen on the list? Thank you for your time and help. Have a great day.

 

[Rolliee]

MariaL,

Did the wiz write that combo box search for you? If so, the only way to tie first and last names that I know of is to do just that; tie the last & first together all and 'allName' and click that. Otherwise you have to write your own VB - which again in my opinion is "gooder."


Rollie E

 

[jfgambit]

When you create a combobox with multiple data you need to ensure that you also include the unique identifier for the records. Assuming you have an ID number in the table make sure that number is also in your combobox and that the Bound Column property of the combobox is set to that number. Otherwise, Access will default to the first available record with the Last Name. If you make the above changes, you will select the Unique ID number for the second smith and that one will show up on your form.

Access has a wizard that will guild you through the process and even hides the unique ID field so that it is not visible in the Combobox.

HTH


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[MariaL]

Thank you very much for your reply. If I wanted to write this in VBA, how would I go about doing so. I appreciate your help.

 

[Rolliee]

MarieL,

Use a textbox or combo box to fill in the desired Names. That entry has a keyField no doubt. The combo will have to 'know' the key field. It will probably be field zero (the commbo list is zero based) then use the following:

DIm rs as Dao.recordset, okay as boolean,chkKey as long

okay = false
chkKey = me.combo0.column(0) ' chk to be sure this is the 1
set rs = me.recordsetclone
rs.movelast
rs.movefirst

do while not rs.eof
if chkKey = rs("FieldKey&quot then
okay = true
me.bookmark = rs.bookmark
exit do
endif
rs.movenext
loop
rs.close
set rs = nothing

exit sub


Rollie E

 

[dvannoy]

There is a easy way to do this..

Create a macro

1. GotoControl = ID your Primary or unique identifier
2. FindRecord = Enter in Find What - =[comboBox] - Match = Whole Field
3. GotoControl = comboBox

Then call this macro in the after update event of the combo box..