textbox value from a combobox in a continuous form

[eggyong via AccessMonster.com]

I have a combobox cboOperationID and textbox txtOperationDesc in a continuous
form. User chooses an operationID in the combobox and textbox automatically
displays the description of that operationID using

Me.txtOperationDesc.Value = Me.cboOperationID.Column(1)

in the afterupdate event of cboOperationID

My problem is whenever i move to the next entry in my continuous form and
chooses another operationID from the combobox, the description in the
txtOperationDesc from the first entry also changes to that of the 2nd entry.

 

[Allen Browne]

There's a couple of issues here: what you store, and what you display.

Firstly, could there ever be a case where the OperationDesc should *not*
match the OperationID? Unless there is a valid reason why it should not
match, you must not store the OperationDesc in your table. Just display the
value. More info in:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

The second issue is about what is displayed. Access does not create a whole
query for the combo on every row in your continuous form. Column(1) of the
combo can therefore refer only to the value in the current row, and so every
row shows the same thing (as you found.)

A workaround for this is to create a query to use as the RecordSource for
your form. The query will use the table your form is currently using, plus
the table the combo gets its data from (its RowSource.) This means you are
able to get the description column from the combo's table, and have it
available to display in your form, i.e. you can change the Control Source
property of txtOperationDesc so that it is bound to the field from the
combo's lookup table. This will display correctly on every row.