[Solved] Use two fields from Combobox SQL?

[2byte]

New guy here, using base with MYSql database.

I have a form with subform. The main form displays customers and the subform shows their orders.
The customers table has a autovalue primary key of cust_id.
There is a comobox loaded with all of the customer names that when selected filters the main form to show the customer's info and the subform is reloaded to match.

I have discovered that when I apply a filter to the main form such as name = 'Ajax' then the main form cannot alter the customer's name in the textbox linked to the name field of the table. This makes sense to me.

If I modify the SQL for the combobox to
SELECT DISTINCT `name` `cust_id` FROM `company`.`customers`
how can I retrieve the cust_id from the combobox or its recordset and use that to filter the form?

If that is not possible is there a work-around?

Thanks

[QuazzieEvil]

use a list box instead, as a combo does not allow multiple columns in source. A list box can be made to look like a combo by selecting the dropdown option in the props dialog.
_________________
Free Docs @ http://www.baseprogramming.com/resources.html
Book @ lulu.com http://www.lulu.com/content/2455551

[2byte]

Thanks for the quick reply! I have done as you suggested and switched to a listbox. I unbound the field as it is not to modify any table field itself. The query for it is SELECT `name` `cust_id` FROM `company`.`customers` .

I am still baffled as to how to get the value of cust_id from the current selected listbox item. Is there an explanation or example available?

Thanks

[2byte]

Ah, now I see. The listbox data field must be the same as the pk field in the form.