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).

[NeoPa]

Considering you are, as you say, pretty green, let me try to explain things in more layman terms :-

When you have linked data such as that (An ID & a linked description / name / etc) it is recommended that you don't try to store the linked information again (It should already be stored in your reference table - [tblHotel] or whatever you've called it). Rather, that you link to it whenever it's required. One of the main reasons for this is that otherwise you create a rod for your own back trying to ensure the data never slips out of step. There are other more complicated reasons (read the linked article for the full SP).

[mshmyob]

In the AFTERUPDATE event of your combo box put the following line in code (ie: code builder).

Me.txtYourTextControlName = Me.cboYourComboControlName.Column(1)
 

Replace txtYourTextControlName and cboYourComboControlName with your text and combo control names.

cheers,

@tfurubay

[mshmyob]

My understanding from his 1st post is that he has a combo box that has 2 columns (Hotel ID and Hotel Name)

After selecting a hotel from the combo box he wishes to save the 2nd column value (Hotel Name) to a text control on his form.

If that is what he wants then a simple AFTERUPDATE event for the combo box to transfer column(1) from the combo box to the text control should do what he wants. (see my solution in previous post)

But I have been fooled before for what an OP wants :)

cheers,

@NeoPa

[tfurubay]

you hit the nail on the head. i am trying to control two fields with one combo box. i was hoping to put some vba code to grab the related field. i see where you're going with respect to normalization best practices. i see what you mean b/c let's say we change the hotel name, then my historical hotel names won't be updated. i can instead create the relationship and let the hotel ID always pull the current hotel. one for me to ponder. i guess i could bound the form to a query with all the links but my concern in doing so is the ability to update data direcly on the form.

[NeoPa]

@mshmyob
I'm not sure who Todd is replying to in his latest post, but as we're both essentially saying the same thing here I suspect it doesn't matter too much. The upshot is clearly that your understanding is correct.

@Todd, This looks as if you have a reasonable understanding of the issue now. I'm not sure building the form over a query gives you much in this case, but your other comments make sense. As Mshmyob says, there is a way to handle your original request (you'll find pointers in posts from both of us on that if you look) but I suspect you will decide that's not too good a plan when you've thought it through.

[tfurubay]

both of you are spot on. thank you! i hope one day to help others as you do in my own little way.

neo if i designed this the optimal way, then how would i get the form to show the hotel? by building a subform and linking on hotel ID? the challenge is we are converting from excel to access so we have to stick with the current table formats in order to view historical data.

mysh, thanks i do understand the event procedure functions in the field settings. i may have trouble with the syntax so i may post one last time. i really want to thank both of you for everything.

[NeoPa]

@tfurubay
As I understand it Todd, you already have a ComboBox designed that shows both the ID & the name of the hotel, with the ID being bound to the underlying data. This being the case, it will automatically show the related name whenever there is a valid ID in place. I'm not sure what else you would need (or want).

If the table formats somehow complicate the question, you will probably need to post what these formats are that we will need to consider.

[tfurubay]

neo, currently, i have one table linked to the form that stores hotel and hotel ID along with other customer info. Both are blank until someone selects hotel ID. i am stuck with this simple design. i can see the hotel ID and name when i click the combo box however only the hotel ID is visible on the form after the hotel ID is selected.

thanks

[mshmyob]

On your column width setting for the combo box set it to something like this

0;3


cheers,

@tfurubay

[NeoPa]

You're quite right Todd (I don't get to play with form stuff much. Much of what I post I have to dig up first).

In that case, it would make sense to use the technique posted earlier (by both Mshmyob and myself). In this case though, the separate control would be a Label. The code in the AfterUpdate event of the ComboBox would have to update the .Caption property of the Label with the .Column(1) value from the ComboBox control.

Let us know if you need help setting up this code (You will need to include names of the controls you've set up for the task).

[NeoPa]

@mshmyob
Good thinking Mshmyob. That shows the name, but at the expense of hiding the ID.

[tfurubay]

hi,

i used this but it didn't work:

me.hotel =me.combo67.column(1)

"hotel" is the control source for the field i'd like to update
"combo67" is the name of the control box.

error message is "MS Access can't find the macro "me" i know i am not "getting" something

[mshmyob]

Is HOTEL the name of your field? I thought you wanted to update a text control on your form.

You are using the combo box to choose a hotel ID and hotel name and then trying to change the hotel name in your table based on your hotel name you chose??? That does not make sense.

If you want to change a text box or label control to display the name of the hotel chosen then give the text box a name like txtHotel or a label a name like lblHotel, then change your code to (be sure you are in VBA code and not the Macro screen):

Me.txtHotel = Me.Combo67.Column(1)
 

cheers,

@tfurubay

[tfurubay]

sorry, my terminology stinks... the form has a field named "hotel". This field is controled by the hotel field on the table. i am hoping to update the value of "hotel" with column "x" from the combo box. does this make sense.

i'm still not getting it. I even tried this do see if half of the logic worked by using this: =Hotel="test"

i'm going to plug away at this. thanks!

[mshmyob]

That is OK.

First the form does not have FIELDS - you have a control (ie: text control) that is bound to a field in your table or unbound (meaning not related to any field in any table (or queries etc.).

What I suggest is this....

1. Keep your combo box as is.
2. Create a text box that is UNBOUND and call it txtHotel on your form
3. Put the code I told you in the AFTERUPDATE event of the combo box

See if that works like you want.


By the way you cannot change a bound control like you are trying - you could use an update query but you actually don't want to update your bound control for what you are trying to accomplish.

cheers,

@tfurubay

[tfurubay]

hi Msh,

success! i had to put the code in the event procedure. didn't work in the expression builder.

and i was able to bound the hotel field to the table. everything worked. you and neo are godsends and wonderful teachers. thanks again!

[NeoPa]

@tfurubay
Todd,

I would echo what Mshmyob said. "Don't bind the new Hotel control to the underlying recordset."

Binding causes it to be saved away into the record. We discussed this earlier and I got the impression you understood the concepts.

I would also suggest (diverging from Mshmyob here) that you use a Label control here rather than a TextBox. Assigning a value to the Caption property of a Label causes it to be displayed, but there's no fiddling around trying to configure it so that it cannot be edited other than by using the associated ComboBox control (problems you will need to consider if using a TextBox control).

[mshmyob]

@NeoPa
Diverge away. I would also use the caption property of a label for this purpose.

cheers,

[tfurubay]

hi there. in this case, i do need to save the hotel to the table. i realize it's contrary to proper relational db design but i am saving the data to a legacy table that is a flat file.

i did lock the hotel text box so ppl will not be able to edit the box. i will figure out how to use a label in place of this method, thanks!

My next challenge is to create required field logic and corresponding error messages. will post a new string for that. thanks

[NeoPa]

In that case Todd, it will need to be a TextBox after all. You cannot bind labels to anything.

We'll look out for your new thread :)

[tfurubay]

okay. got it and thanks. i am humbled by your patience and benevolence! i wish i could express how appreciative i am of people like you and mish. a 1000 blessings to both of you!

[NeoPa]

It's not often I'm accused of having patience :D

That gives you a bonus point Todd.

It's always nice to hear we've helped though :)

[tfurubay]

you do and you did. thanks!