fill a value in a textbox when an item from a combobox is selected - Access 2007

[robert_anisoiu]

Hello,

I have a problem and I don't know how to solve it.

I have a table, Table1, with three fileds: Table1FieldId, Table1FieldName, Table1FieldNumber.

I have another table, Table2 with: Table2FieldId, Table1FieldId, Table1FieldNumber

On the form attached to Table2 the Table1FieldId is in a combobox. The displayed value in the combobox is Table1FieldName and the controlsource of the combobox is set to Table1FieldId.

I want, when I select an item from this combobox, the textbox with Table1FieldNumber to be filled with the corresponding value(value of Table1FieldNumber) from Table1.

I tried with LostFocus event, AfterUpdate event, DLOOKUP and so on. I was not able to read the Table1FieldNumber from Table1 and to put this information in the textbox value.

Please give me some ideas.
Regards,
Robert

 

[JANR]

No need for dlookup since the information you want is already availible in yor combobox, Column number 3.
If your textbox is suppose to be Unbound, then put this in it's controlSource:

=[NameOfCombobox].Column(2)

In VBA put this in the After_Update event of your combox:

Me.NameOfTextbox = Me.NameOfCombobox.Column(2)

I assume that the combobox has a columncount property of 3 and the widths: 0;2cm;0

JR

 

[John Big Booty]

In the text box's Control Source put;

=ComboName.Column([B][COLOR="Red"]X[/COLOR][/B])

Where X is the column in the combo that hold the data you wish to display in the text box. Remember that the columns of Combo and List boxes are zero indexed, i.e. they are numbered from zero up. Usually column 0 (zero) is hidden and holds the item ID that is being stored.

 

[MStef]

You don't need a "Table1FieldNumber" data in the Table2, because you have
got it in the Table1. You don't need the same data in 2 tables, except the foreign
key.

 

[robert_anisoiu]

Thank you all,

it works when I set the ControlSource of the textbox.

Thank you again,
Robert

 

[robert_anisoiu]

You don't need a "Table1FieldNumber" data in the Table2, because you have
got it in the Table1. You don't need the same data in 2 tables, except the foreign
key.

I need "Table1FieldNumber" data in the Table2 because it can change and I need to know what was the value in the moment when the record was created.