Display two fields in a single combo box

[Hulas]

Guys, I am still having problem fixing this. Let's say I have two fields in a single table called "Identification " and those fields are called ;ID1 and ID2. How do I get these two fields in a single combo box in a form. I know how to do for one. It should be set up in a such a way that when I scroll down I can select either ID1 or ID2.

As always thanks for your time.

Hulas

[PianoMan64]

Guys, I am still having problem fixing this. Let's say I have two fields in a single table called "Identification " and those fields are called ;ID1 and ID2. How do I get these two fields in a single combo box in a form. I know how to do for one. It should be set up in a such a way that when I scroll down I can select either ID1 or ID2.

As always thanks for your time.

Hulas

Hulas,

Since either ID number will Identify the selected record if they are unique, then there isn't a simple way without create a list of all posible ID numbers and then have to create a crossreference list.

If one of the ID's uniquly ID's one of the records, then what you're going to need to do is put them in to separate Pulldown Menus.

If you really want to do this, my best suggestion would be to build the routine in VBA cause it's not something MS Access knows how to do itself without some help.

I don't know how many records we're talking about here but let's deal with that issue first.

Can you let me know how many records we're talking about here. Post back and I can then suggest how to proceed.

Thanks,

Joe P.

[Hulas]

Joe,

I made up those ID's so let me tell you exactly what I have and what I need. I have 'Act Name' and 'Headliner' as two fields of the same table 'Entertainment' . I have these two fileds as two combo boxes of the form 'Add Entertainment'. The records of these fields can go upto 1000. Now, I want to set up the 'Headliner' combo box in such a way that it will not only show me its own records but it will also show me the records for the 'Act Name' as well. So, that when I scroll down the Headliner como box I can see the records for both the Headlier and Act Name. Both of these fields are text.

Thank you for your time.

Hulas

[PianoMan64]

Joe,

I made up those ID's so let me tell you exactly what I have and what I need. I have 'Act Name' and 'Headliner' as two fields of the same table 'Entertainment' . I have these two fileds as two combo boxes of the form 'Add Entertainment'. The records of these fields can go upto 1000. Now, I want to set up the 'Headliner' combo box in such a way that it will not only show me its own records but it will also show me the records for the 'Act Name' as well. So, that when I scroll down the Headliner como box I can see the records for both the Headlier and Act Name. Both of these fields are text.

Thank you for your time.

Hulas

Ok Hulas,

If I understand your request,

You want to display the Headliner/Act Name as one field in a combo box so when selecting Headliner/Act that you how how they are paired?

Is that what you're trying to do?

If so, paste this into your RowSource Column, and that should do the trick:

[code=vb]
SELECT [Headliner] & " " & [Act Name] AS [Headliner-Acts] FROM Entertainment ORDER BY [Headliner] & " " & [Act Name];
[/code]

[Hulas]

Sorry Joe, it still doesn't works the way I want. It pops up a massage that asks me to enter the Act Name. I am looking for a cmobo box to store both the Headliner and the Act Name information so that when I scroll down I can pick the one I need. For example let's say I have two fileds: Country and State. I have a form and I have a combo box for State. Now, when I scroll down the combo box for State, I should have the list for the records that are in the State and Country field.

Thanks for your help Joe.

Hulas

[PianoMan64]

Sorry Joe, it still doesn't works the way I want. It pops up a massage that asks me to enter the Act Name. I am looking for a cmobo box to store both the Headliner and the Act Name information so that when I scroll down I can pick the one I need. For example let's say I have two fileds: Country and State. I have a form and I have a combo box for State. Now, when I scroll down the combo box for State, I should have the list for the records that are in the State and Country field.

Thanks for your help Joe.

Hulas

The reason it would be asking for the Act Name is because that's not the field that the act name is stored in. If you go back to your Entertainment table you should see the name of the field that as the Act Name in it. Then change the field name that I've suggested with the actual field in the Entertainments table. That should solve your problem.

If not, let me know.

Joe P.

[FishVal]

Hi, Hulas.

Is your combo bound to any field of form's recordsource?

Regards,
Fish.

[Hulas]

I fixed the pop up box problem, but that's not what I am looking at. The formula concatenates the two fields from Headliner and Act Name, but I don't want that. Lets's say if I have Michael, Jordan, and Brian for Headliner, and Ashley, Jannifer, and Britney for Act Name. Now, with the combox for Headliner, I should be able to scroll down to see all these six names but they should not be concatenated. Right now I am getting Michael Ashely, Jordan Jannifer, and Brian Britney.

Thank you for your help.

P.S.

[Hulas]

No.

Thank you for your concern

[FishVal]

I fixed the pop up box problem, but that's not what I am looking at. The formula concatenates the two fields from Headliner and Act Name, but I don't want that. Lets's say if I have Michael, Jordan, and Brian for Headliner, and Ashley, Jannifer, and Britney for Act Name. Now, with the combox for Headliner, I should be able to scroll down to see all these six names but they should not be concatenated. Right now I am getting Michael Ashely, Jordan Jannifer, and Brian Britney.

Thank you for your help.

P.S.

To the best of my knowledge Access.ComboBox control has no ability to display multiple fields in textbox portion, only in dropdown listbox. But you may easily concatenate your fields with string constants to make combobox content quite representative.

Like
[code=sql]
[Headliner] & ' | ' & [Act Name] AS txtCombinedFiel d
'Headliner: ' & [Headliner] & ', Act Name: ' & [Act Name] AS txtCombinedFiel d
[/code]
or whatever you prefer.

Regards,
Fish.

[SanTang]

A way out of this is by using a Union query twice on your table. The two fields will be in the same column and displayed alpha if you need.

[SanTang]

Sorry I got lost somewhere in Hyperspace :)
Back to the UNION query...

Let's say you want to display Filed1 and Field2 form one table into the dsame column of whatever control. Use a UNION query statement to first build the recordset thes use : MyCombo.RowSour ce = SQLstatement

The SQL statement would go something like this:

SELECT KeyValue, Field1 as Main from YourTable;
UNION
Select KeyValue, Field2 as Main from YourTable;