Update fields with Combo Box?????

[Guest]

Hi

I Have a form with a sub form. On the main form I have a Staff ID and Staff full name field (TEXT). When I navigate through the records using the buttons, the subform updates to show clients assigned to each staff member.

I changed the Staff ID text field to a Combo box in order to locate staff members quicker. This updates the Sub form perfectly but the Staff Full Name field remains the same and does not update. I am a newbie to Access so detailed assistance would be greatly appreciated.

Thanks in advance

Malcolm Davidson

 

[Sandra Daigle]

Hi Malcolm,

If you just want to display the value from the StaffFullName column of the
combo the easiest thing would be to make it the first column of the combo
that has a width greater than zero. This way, even though the StaffID is
bound to the combo, the StaffFullName column will be the one displayed in
the control when it is not expanded.

Another way is to create a new textbox that shows another related column
from the combo. First make sure that the related field is included in the
rowsource of the combo and make sure that you increase the Column Count
property to include any new fields. Adjust the ColumnWidths property
according to whether you want to see the values when the combo is dropped
down. Then for each related field you want to display create a textbox. In
the ControlSource of the new textbox put

=me.MyCombo.column(3)

Then because the column property is indexed starting with 0, replace 3 with
the column number minus 1 of the field you want to display. IOW, the above
will cause the value from the 4th column to be displayed in the textbox.

I am curious to know a couple of things - since you didn't describe your
main form - is your main form bound (ie is there a table or query in the
RecordSource property of the form)? Often I see people make the mistake of
using a bound combo (ControlSource property has a field name) for record
navigation. Instead of moving to a different record, they are actually
changing the value on an existing record. This is fine as long as it is what
you intend to happen.