ComboBox Union Query bound to column 1

[Danick]

I'm trying to have a combobox look up results from two columns but still bound to column 1.

That is, with Column 1 [Last Name] and Column 2 [First Name], I'd like the combobox to filter the list by both columns, but then bound the selection to Column 1 [Last Name]

I'm trying to do that with a union query, but can't find a way to bound it to Column 1.

 

[plog]

Here's a thread that details how to get a combo box to use on field but display another:

M

Thread 'show name in a combo box but store id'

May 24, 2022

hello, i have a table [coordinator] and table [program] linked together through table [coordinator_program] which contain primary keys of both.. i have a form with combo box that let the user choose the coordinator of program but it shows the id because the record source is coordinator id from [coordinator_program] table to avoid duplicates name.. is there a way to show the names in the combo box but store the id value?

• MK1999
• Replies: 29
• Forum: General

However, your further description of what you are trying to do seems odd. Usually a combo box shows a value meaningful to a human and then works with a unique ID behind the scenes. But the field you are using behind the scenes ([Last Name]) isn't guaranteed to be unique so I questioin the big picture objective of what you are doing.

Also, UNION queries are a red flag--they often signal poorly structured tables. Can you explain why you need a UNION query? Why don't all the first and last name values exist in just one datasource?

 

[Gasman]

Concatenate Last and First as a column.
I would be having an ID as bound column, always first for me, and the second column holding the concatenated fields.

 

[Danick]

Concatenate Last and First as a column.
I would be having an ID as bound column, always first for me, and the second column holding the concatenated fields.

Thanks for the quick reply. Actually found that there was value in showing both columns on the form. So basically ended up creating two combo boxes both bound to the same field in column 1. So this way you could look up either one and show both Columns 1 and 2 on the form.

 

[Danick]

Here's a thread that details how to get a combo box to use on field but display another:

M

Thread 'show name in a combo box but store id'

May 24, 2022

hello, i have a table [coordinator] and table [program] linked together through table [coordinator_program] which contain primary keys of both.. i have a form with combo box that let the user choose the coordinator of program but it shows the id because the record source is coordinator id from [coordinator_program] table to avoid duplicates name.. is there a way to show the names in the combo box but store the id value?

• MK1999
• Replies: 29
• Forum: General

However, your further description of what you are trying to do seems odd. Usually a combo box shows a value meaningful to a human and then works with a unique ID behind the scenes. But the field you are using behind the scenes ([Last Name]) isn't guaranteed to be unique so I questioin the big picture objective of what you are doing.

Also, UNION queries are a red flag--they often signal poorly structured tables. Can you explain why you need a UNION query? Why don't all the first and last name values exist in just one datasource?

Sorry Plog, for some reason, your message only showed up after I had already responded to Gasman.

Just to explain what I was trying to do. Image a contacts table and an order table. the contacts table has a contacts id as well as first and last name fields. The order table picks from the contacts table bound to the contact id. But the combo box would only filter the last name or the first name, not both. So you have to either use a union or Concatenate in another column as Gasman suggested. But in the end, I just went with two combo boxes and the user can use either one to filter because both are bound to the same contact id.

Appreciate you taking the time to respond.

 

[Gasman]

Please give us real examples in future.
Getting really tired of people giving us very poor examples of their setup and then getting feedback as to why, only to be told 'Oh that is not really what I am doing'.

 

[Danick]

Please give us real examples in future.
Getting really tired of people giving us very poor examples of their setup and then getting feedback as to why, only to be told 'Oh that is not really what I am doing'.

I removed that line since although this particular database was for something else I was doing, I actually used the information provided and implemented it into a database that benefitted from your post.

 

[Pat Hartman]

I just went with two combo boxes and the user can use either one to filter because both are bound to the same contact id.

That is the best solution. I'm not sure a union would actually work since it would duplicate the uniqueID field - once for First, Last and again for Last, First.