Select record from List Box

[Hank.School]

Hello everyone,

I have a form that uses a search example from John Big Booty (which works flawlessly, thanks John!) that populates a list box with search results as a user types. I would like to take it a step further and populate text boxes on the same form with the currently selected record in the list box.

The code for the original example is in the Sample Database forum titled 'Dynamically search multiple fields', sorry, can't post links yet.

Basically, it uses a List box which is populated from a query. The query criteria comes from a text box and updates with the text box's On Change event.

What I am trying to figure out is how can I reference the record selected in the list box to populate the new text boxes from the table. I guess I would do this in the List Box's On Click event but would prefer a 'on selection change' event so whatever is selected is displayed. I can set the Text Boxes to the field names but I can't reference one particular (selected) record.

Any help would be appreciated! Thanks
Hank

 

[pbaldy]

This method should work, as long as the listbox is not multiselect:

http://www.baldyweb.com/Autofill.htm

I'd probably use the after update event.

 

[Hank.School]

Thank you for the response! I believe you are pointing me in the right direction... I have added my key field to the query (in a hidden column) and I set a text box source to the new column and it does display the value in that field (the autonumber record index). What I would like to do is have the text box display a field from the table that is not a column in the query (text boxes for all of them actually). I guess I could do this by including all the fields in the query and hiding the ones I don't want in the list box but I figured I could reference them easier by setting the text box's control source to something like [table_name]. (Obviously I don't know the syntax to refer to the specific record in the table).

I guess, to simplify, how would I set an unbound text box to a value from a specific field from a specific table based on the key value of that record?

Thank you again
Hank

 

[pbaldy]

You can't refer to the table directly from a textbox like that (unless the form is bound to the table). The method shown is probably simplest, as you mention adding fields to the row source and hiding them. You could open a recordset on the table using the key field from the listbox, and then populate the textboxes from the recordset. You could use DLookup(), but for multiple fields that would be inefficient.

 

[Hank.School]

Ok, I will just add the fields to the Query and refer to them with the column numbers. Thanks

 

[pbaldy]

Happy to help! Don't forget to change the column count property of the listbox.