How do I combine three columns into one drop box in order to perform a search?

[stateemk]

I have a db with a table that has many columns of info for various entities. In this table, there are three columns called, entity name, second entity name and third entity name. I need to combine all three columns into one drop box so a search can be done. Once the correct entity name is found, I need to be able to select it and bring up a form with all the data for that entity. For example, say there is an entity that has a name change so the new name becomes the entity name and the old name is the second entity name. If I search by the old name, I want to be able to select it and have all of that data come up in the form including the current entity name. I know this is probably very confusing so please help and let me know if I can clarify anything.

[ChipR]

What kind of search are you trying to do with a combo box? Would the user visually search through the data? This seems like something that could be done via DLookup, but more details are required.

[stateemk]

The search is just a form with one drop box that would only have all the entity names in it whether it be the first, second or third entity name. Once the user finds the name they are looking for, I could either have a button and do an OnClick command or just do an AfterUpdate command. After either command, another form would open with the rest of the data for that entity. Does this help?

[ChipR]

Did you try the combo box wizard and add your key field and the 3 name fields? The key field would be the bound column of the combo box and that would be the value that you use to filter the other form.
I think that AfterUpdate is good with a subform or other controls on the same form, but a button is preferable if you are going to pop up another form.

[stateemk]

I tried the combo box wizard, but it lays it out as a drop box with three separate columns. I want all the columns combined into one column.

[ChipR]

Perhaps you can use a UNION to get them all together.

SELECT id, name1 FROM myTable
 UNION 
SELECT id, name2 FROM myTable
 UNION 
SELECT id, name3 FROM myTable

[stateemk]

Sorry, forgot to mention that. I did a union and that got all the entities into one column, but then I ran into another issue. When I would click on any entity name that was in the second or third entity name column of the table, the subform that was opened would not populate with any data. If I would click on an entity that was in the first entity name column, it would populate the subform with all the data.

[ChipR]

There must be an issue with the key field then. Did you try keeping the key column visible in the combo box for testing or msgbox the filter value before opening the other form? The problem could be in the underlying query SQL, the combo box setup, or the code to show the record. If you have switched to a subform, are you setting the source or using Master/Child link fields?

[stateemk]

I think the union query that I have will work, but I can't figure out how to get an id field in there. There is an id field in the table that the entity names are coming from, but how do I combine that to the entity name in the union query?

[ChipR]

What's your SQL for the Union, in comparison to mine in Post #6?

[stateemk]

That does work. I hadn't looked at it closely enough, but now the problem is that there won't necessarily be something populated in every field, so there are a lot of blanks in the drop list. Also, how do I hide that ID field now?

[ChipR]

To hide the first column, change the Column Widths property of the combo box to
0";X"
The X being the width of the second column.

I think you can eliminate the Nulls just by changing the query to

SELECT id, Name1 FROM myTable WHERE Name1 <> NULL
UNION
etc.

[stateemk]

You are wonderful!!!! It is working perfectly now. Thank you so much for your much needed help!!!!

[ChipR]

You're very welcome.

[stateemk]

Okay, one more question with the same db. I have another column that I need to do the same thing with. It is a modification number column. There are four that I combined into one. There are duplicates in the columns though, so I just need the specific mod number listed once no matter which column it comes from in the table. I still need to have the id number in order to populate my table correctly. Any suggestions?