How to Reference Individual Form Elements in a MS Access Continuous Form

Question

MS Access Scenario:

I am using a form (we'll call it the select_contract form) and a subform (we'll call it the employee_allocations_by_contract subform).

The select_contract form:

Is unbound.
Contains a combobox that allows the user to select a contract id.

The employee_allocations_by_contract subform:

Is a continuous form.
Is bound to a table (which I'll call the hours_allocation table) that contains the number of hours that each employee is allocated to each contract.
Binds only one field, the employee_id field, to the hours_allocation table. The row source for this field is a query that returns the ids of employees that have hours allocated to the contract that the user has selected in the select_contract form.
Contains twelve other, unbound fields, one for each month of the year. These fields are intended to display the number of hours allocated to the employee listed in the employee_id field for each month of the year.

The Problem: In order to display the number of hours by month that are allocated to each employee listed on the employee_allocations_by_contract subform, the queries for each month field need access to the employee_id field of the row on which they appear. I haven't been able to figure out how to reference this field. Because the employee_allocations_by_contract subform is a continuous form, a reference to the the employee_id field name appears to reference each row on the form.

I have searched several forums and have found related continuous form issues, but nothing that clearly addresses this problem. Any thoughts?

I find your explanation confusing. What do you want displayed in the subform? A calculated total of hourse for each employee associated with the contract? — David-W-Fenton

Albert D. Kallal Albert D. Kallal · Accepted Answer · 2010-10-22T21:18:20

Well, you could build a sub-query for each of the month columns you need that total for.

Something like:

Select id, employee_ID, bla, bla, bla, 
     (select sum(hours) where month = 1 and year = 2010 and 
       employee_id = ehours.Employee_id from tblProjectHours)
 as month1,
     (select sum(hours) where month = 2 and year = 2010 and 
      employee_id = ehours.Employee_id from tblProjectHours) 
 as month2,
      (select sum(hours) where month = 3 and year = 2010 and 
       employee_id = ehours.Employee_id from tblProjectHours)
  as month3 
  etc. for each column needed

from ehours

Note I used month and year as columns and you likely have to use month([SomeDate]) = 2 and year([SomeDate]) = 2010, but you get the idea.

Also, while you can't address each row and stuff a value into that un-bound text box, you CAN in fact bind the text box to a function that returns the value/expression you need.

txtBoxMonth1                               txtboxMonth2              etc.
=MyMonth(1,[employee_id])                  =MyMonth(2,[Employee_id])

And, then in the form, you have a public function called

Public Function MyMonth(intMonth as interger, lngEMPID as long) as long

      ' code here to get total based on employee id and month

End Funciton

So, you can bind a function to those text boxes, and the current row emp ID can thus drive what each text box displays. Just remember that this function should have all of the data pre-processed, and you don't want to have to re-run and re-load the data for each function call as that will be FAR too slow. However, I have used this approach to much success. So, each row of the form only has one real column (empID), the rest of the columns are derived from text boxes bound to the public function as per above with the month being passed along with the emp id to return the value for that column.

So, above is two possible approaches I used with success.

How to Reference Individual Form Elements in a MS Access Continuous Form

3 Answers