Assign value to bound textbox in VBA

[RMAL]

I thought I had a simple task until I tried it :-)

I have a form with multiple controls bound to fields on an underlying table. In one case I have a combo box whose values come from a query that has a few columns of data. The first column of those values is bound to a numeric data field of the table. A second control on this form is a text box that is bound to another field in the underlying table that happens to be a short text field.

What I want to do is this: When you select a value from the combo box, I want to set the value of the text box automatically. So I put some simple code in the After Update event of ComboBox2 that looks like this:

Me.txtBox1.Value = Me.ComboBox2.Column(2)



That doesn't seem to work. I put a Msgbox statement in there to confirm that I'm capturing the right event and the correct column of the combo box to get the value I want. But the txtBox1 value doesn't change. I tried adding this after my attempt to change txtBox1 to force the issue. No luck

Me.txtBox1.Requery

I also tried to reference the text box without the "Value" property, like this...

Me.txtBox1 = Me.ComboBox2.Column(2)

The general goal is allow the user to make a selection from the combo box, but have it update two different fields in the underlying table. The combo box is bound to a numeric field and the text box is bound to a short text field.

Any help is appreciated.

[June7]

Why do you want to duplicate this data into another table?

The code should work. Does for me.

[RMAL]

Hi June7

I'm not duplicating this data into another table. It's all taking place on one table and one form.
That table has two underlying values: A short text field (in this case a contact's last name) and another numeric field in which I am storing the ID number of that contact in another table that has all contacts. But I think that reference to another table is sort of beside the point. When the use selects a choice from the combo box, I can capture the text value of what they selected, and I just want to write that to a text box that is on the same form. Eventually I want to make that text box invisible to the user -- they don't need to know that this is happening -- all they see is the combo box.

It's interesting that this works for you. I'm not seeing anything extraordinary about the legacy form I'm working on.

[RMAL]

Oh my goodness. I'm embarrassed to report that the issue was a typo in my code that referred to a different control on the form. Case solved. Thanks for saying the code worked for you -- it sort of prompted me to look at the whole thing again.

[June7]

I assume your combobox pulls list from 'lookup' table and ID of selected item is saved into data table. If you also save other info from combobox then you are duplicating data. Info (other than ID) from lookup table is also saved into data table. Why?

[RMAL]

Sorry for the very late response to your question, June7.
You are correct -- there is definitely a duplication of pretty much the same information in a single table. A previous programmer set this up in a way that served certain needs at the time (storing a string value) but didn't anticipate other possible uses. I was faced with a strict deadline to get something done, and getting rid of that string value and doing this right would have required a modification to a couple of dozen reports and retesting all. I opted to accept this duplication of data as a temporary workaround until I have the time to get this back to a single stored value. Not a great excuse, I know. But sometimes scheduling pressures force us to do stuff that is a little ugly :-)