Ms-access – MS Access – auto populate field based on another selection

ms-access

I'm working in Access. One of the fields in the Main table is the Employee lookup field form the employees table. Also in the employees table is that rate per hour for each employee. So how do I set the rate field in the Main table automatically to update when the employee is selected?

MainTable
EmployeeID            Rate
123                   

EmployeesTable
EmployeeID             Rate 
123                    330.00

Best Answer

You have 2 options, depending on wether you want to store again that rate in the "MainTable" (what a bad name!) or just display it in the form.
1) if you just want to display the rate, include that field in the RowSource of the Employee combo (eventually with a Width of 0 to hide it). Then, bind the Rate control on you form to an expression like
=Employee.Column(2) (note that Column method is 0 based, so Column(2) is the 3rd column).

2) if you want to save the historical value of the Rate in the Main table, then include that field in the Employee combo RowSource as before, and add the following code to the Employee.AfterUpdate event: Rate = Employee.Column(2)

This is a quick and rough overview, but I think it's enough to google your solution.

Related Topic