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:

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

The employee_allocations_by_contract subform:

1. Is a continuous form.
2. 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.
3. 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.
4. 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?

Answer 1

An unbound control on a continuous form can only refer to the record that has the focus. There fore, if data is entered into an unbound control, you will see the same data for every record in the continuous form.

Answer 2

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 collum 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 collum.

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

Answer 3

You cannot reference controls "individually" ("at the line level") in a MS Access continuous form.

Your only solution here is to bound these controls to an underlying recordset\recordsource where corresponding fields hold the values to be displayed.

For example, if you want to display a time period as the difference between a "date in" and a "date out", your recordset\recorsource could be something like:

select id_person,dateIn,dateOut,dateDiff(xx,dateOut, dateIn) as timeIn from ...

Then your time calculation control has to be bound to the 'timeIn' field of the recordset.

(*) please check the dateDiff arguments. I do not have any help available here ..