How to populate the value of a Text Box based on the value in a Combo Box in MS Access 2007?

Question

I have a combo box which is of a lookup type, i.e., I've selected the source to be a column from a table and am storing the selected value in another table. The table which I am looking up has another column and I need the value in this column to be displayed in a text box and each time I change the value in the combo box, I need the corresponding value to be displayed in the text box. How can I do this? What I have done so far is to write a Select query that selects the appropriate column based on the combo box's value. Is there a more decent way of doing this? Please help me!

Bushbuck

Answer 1

Make the source of the combo box to your 2 fields e.g. SELECT id, name FROM Customers
Make sure you set the Column Count property of the combo to 2 and Column Widths like 0";1", accordingly.
Then make your unbound text box source equal to =MyCombo.Column(1) (from memory, this Column index is zero based).
That's it, zero code required.

Sheasheaf

I tried this method. But it works only the first time it is created. When I close and re-open it, the text box, which has been set to ComboBox.Column(1) shows #Name?. Why is this happening? What is wrong? – Bushbuck

Need = sign =ComboBox.Column(1) and use actual name of combobox. – Cronus

However, if you just want to display customer name, the combobox accomplishes that so unless there is a third value needed, this textbox is unnecessary. – Cronus

Answer 2

It's nicer to use an event of the combo box e.g. onChange, so when a selection is made the event sets the value of the text box.

me!txtTextBox1 = me!cboComboBox1.column(1)

That way it will work everytime.

You could also use a button with onClick etc. but the choice is yours (and as mentioned in the previous post, alter the column number based on its row source with 0 being the first.

Eadie

Setting an unbound textbox to depend on the combo as illustrated by @Sheasheaf works every time with no code at all, so it is a better solution, IMHO. – Vacua

Answer 3

After reading the question and answers I tried the following, and it seems to work well (so far):

In order to display more than one column of a combobox selection, I have resorted to the following:

Place a textbox over the combobox, Size it by sampling other fields, so that it covers the text frame of the combobox. I have purposely have left a small space to the right of the NEW textbox to indicate that it Is NOT part of the combobox.

In the Control Source for the textbox enter the expression below:

=[DefaultAcct].[Column](1) & "  " & [DefaultAcct].[Column](2)

Then, in the ‘onchange’ event of the combobox, set focus to the textbox.

Private Sub DefaultAcct_Change()
    txtConcatenate1.SetFocus   
End Sub

Capriola