getting a combo box to store multiple values

[tfurubay]

Hi,

I am setting up a database and would like a combo box on a form to store two values. for example, there's a ID and a Description. I can easily get the combo box to show both the ID and the Description. however the combo box is tied in to one field only, in this case, the ID.

I'd like the form to store both the ID and then display the Description in a field next to the ID.

your assistance is much appreciated, thanks!

Todd

[NeoPa]

I'm not sure what you're asking Todd.

Your explanation seems to contradict anything I think you're asking :S

[tfurubay]

sorry about that!

i've built a form that needs to store hotel ID, hotel with other client info. the hotel id and hotel are stored in a seperate table. when the combo box is opened, hotel id and hotel appear in the pull down. however b/c the combo box is built on a field controled by hotel ID, only the hotel ID is transferred to the form. the hotel field remains blank. i was going to build a macro and create an update query (im a GUI person) to update the hotel field but i'd prefer to build an expression that based on the hotel ID, can update the Hotel. hope that makes a little more sense.

thanks

todd

[mshmyob]

Use the 'column' parameter with the combobox value.

Me.txtYourTextBoxName = Me.cboYourComboName.Column(0)
' or
Me.txtYourTextBoxName = Me.cboYourComboName.Column(1)
 

Each column number refers to each column in your combo box.

Does this help

cheers,

[NeoPa]

I'm sorry Todd. If all the words made perfect sense in sequence, the punctuation would possibly not matter so much. As it is, I can only guess which words are in the right place from the punctuation, which is missing or misplaced in most cases. Most of the words are probably wrong because you don't have a very good grasp of the terminology, which is quite understandable, but it makes taking care to punctuate and not drop random words, all the more important.

What do you have as the RowSource of your ComboBox (I'm assuming the pull down from one sentence is actually referring to the combo box from the next)?
What are the values for Column Count, Column Widths & Bound Column in your ComboBox control?

[tfurubay]

MSHMYOB,

sorry pls bear with me. when i create the combo box, i use the wizard therefore many of these underlying controls are not visible. what i do is i use the wizard to prepare the box. here are the steps:

1) wizard asks me the source, i tell it the hotel table
2) asks me which fields would i like to see in the pull down. i tell it hotel ID and hotel
3) asks me where i'd like this data stored. i tell it hotel ID.

is there a back end control or expression where i could tell it to do what you recommend?

thanks again

[tfurubay]

neopa,

we obviously have a communication barrier but i appreciate your dedication the the cause.

here are the current settings:

control source: Hotel ID
Row Source: SELECT Hotels.HID, Hotels.Hotel FROM Hotels;
bound column: 1

so from this:

"Control Source" is the Hotel ID and is the field on the form that is updated from the combo box
"row source" determines which fields are displayed in the combo box pull down
"Bound Column" is the field that gets stored

again, i'd like to store the both the hotel id and hotel however it doesnt seem to allow you to do that in the GUI.

Thanks NEO.

[NeoPa]

There's certainly a way of finding the info I asked for.

If you view the form in Design mode, click on the newly created ComboBox control and select Properties (Alt-Enter will do this for you).
Look through the properties and note (& post) the values of the properties I requested.

A wizard is merely a way of doing ordinary designs more easily. What it produces can be done manually just as well (but not so easily for someone short of experience).

PS. Just caught your latest post and now processing...

[tfurubay]

Hi,

yes, i rarely design forms so i am pretty green...

here are those values, thanks!

Row Source: SELECT Hotels.HID, Hotels.Hotel FROM Hotels;
Column Count: 2
Column Widths: 0.3646";1.8646"
Bound Column: 1

[NeoPa]

@tfurubay
I think you're starting to grasp some of the concepts here Todd.

I also think I've started to grasp what you're trying to ask (originally). If I'm not mistaken, you're asking how to get the ComboBox control to maintain two fields in the underlying recordset (that the form is built upon). Is that about the size of it?

If so, then I'm afraid that facility is not supported. There can be only one Control Source for the control. However...

It is possible to put some VBA code in the AfterUpdate event of the control, such that a separate control (bound to a separate field from the underlying recordset) is updated. Another however coming I'm afraid...

This is not a good idea as Normalised Relational Databases (Normalisation and Table structures) are designed to work differently. This would be de-normalising your data (which is not a great idea - trust me).