How to use a combo box in a bound form?

[Billy Smith]

In an Access 2000 application, I have a table of records and I want to
create a
form that is bound to a query of that table such that the user can edit the
records.
Specifically, one field is EmployeeID, a numeric field.

I want the user to be able to change the employee ID in the record only by
selecting the employee name from a combobox. I can easily get the employee
names and IDs from a table and put them into a combobox.

If I were to let the user change the EmployeeID via a text box it would be
easy
to bind the text box to the EmployeeID field. But using a combo box with
names
linked to employee IDs is indirect. Giving the EmployeeID field in the
table a
lookup control is not an option because I'm afraid that would mess up the
many other
queries and forms that currently use the field as it is.

I know how to use an invisible bound column in a combobox so I know I can
get the ID
when the user selects an employee in the combobox. But how can I link the
ID value to
the database table so the value gets changed in the record?

I'm fluent in VBA for Access. Thanks in advance.

 

[Klatuu]

I would suggest you use an unbound combo to look up the employee name and ID
and a bound text box to allow the user to change the employee id.

Since the text box is bound, it will contain the current employee id and the
user can then change it.

If the employee id is the record's primary key and there are any child
tables like Payroll info, etc. This could be very dangerous.

 

[Billy Smith]

Klatuu,

Thanks for you reply. The employee id is not the primary key of the table
I'm changing so that is not a problem

It sounds like you're suggesting that the user get the ID from a combo box
and then manually transfer it to the bound text box. I was hoping for an
automated procedure: the combo box comes up with the current employee name
selected. If the user selects another employee from the list in the combo
box then the ID in the record is changed with no other action by the user.
Is there not a way to connect the combo box and the text box so that this
happens?

I've thought about using an invisible text box bound to the ID and then, in
the combo box event procedure code write the ID to the invisible text box.
I think that will work but I was hoping for something built in that would do
this.

Thanks.

 

[Klatuu]

Use the After Update event of the combo to populate the text box:

Me.MyTextBox = Me.MyComboBox