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!
Asked
Active
Viewed 8.3k times
6
CodingInCircles
- 2,565
- 11
- 59
- 84
3 Answers
14
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, accordingly.
Then make you unbound text box source equal to =MyCombo.Column(1) (from memory, this Column is zero based).
That's it, zero code required.
iDevlop
- 24,841
- 11
- 90
- 149
-
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? – CodingInCircles Jun 27 '11 at 06:48
4
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.
-
2Setting an unbound textbox to depend on the combo as illustrated by @iDevlop works every time with no code at all, so it is a better solution, IMHO. – Fionnuala Aug 31 '12 at 18:49
0
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
