Calculated Fields/Subforms...

  • Thread starter Thread starter Mark Hughes via AccessMonster.com
  • Start date Start date
M

Mark Hughes via AccessMonster.com

Hi all,

I'm completely new to access and am trying to create a simple form/subform.

The main form will include:
employee_number,
forename,
surname,
total_leave,
leave_remaining (to be calculated)

The subform will include:
week_beginning,
Mon,
Tue,
Wed,
Thu,
Fri,
Sat,
total (to be calculated)

What I'm trying to achieve is this:

When the user inputs into the main form the employee's details, they will
input the employees leave entitlement. Then in the subform they will input
any leave taken. I would like the system to work out a total for that week
(Mon+Tue+Wed, etc) and then show this in a 'total' field. THEN, the system
to give a running total on the main form of the leave remaining
(total_leave minus the subform_total).

I've tried this using queries for the calculations, but when I do a subform
based on the query I keep getting 'You have chosen fields from record
sources which the wizard can't connect.

Any ideas anyone? I'm getting sooooo confused with the way access handles
calculated fields...

Thanks
Mark
 
You can refer to the results of calculations in expressions. That is, you
can't use [mon]+[tues]....=[wktotal], then try to use
[wktotal]*4=[mthtotal].
You'll need to either store the result (not recommended) or recalc the
entire expression every time it's used in forms/reports.
-Ed
 
Oops! Meant to say:
You CAN'T refer to the results of other calculations in expressions.
-Ed

Ed Robichaud said:
You can refer to the results of calculations in expressions. That is, you
can't use [mon]+[tues]....=[wktotal], then try to use
[wktotal]*4=[mthtotal].
You'll need to either store the result (not recommended) or recalc the
entire expression every time it's used in forms/reports.
-Ed

Mark Hughes via AccessMonster.com said:
Hi all,

I'm completely new to access and am trying to create a simple
form/subform.

The main form will include:
employee_number,
forename,
surname,
total_leave,
leave_remaining (to be calculated)

The subform will include:
week_beginning,
Mon,
Tue,
Wed,
Thu,
Fri,
Sat,
total (to be calculated)

What I'm trying to achieve is this:

When the user inputs into the main form the employee's details, they will
input the employees leave entitlement. Then in the subform they will
input
any leave taken. I would like the system to work out a total for that
week
(Mon+Tue+Wed, etc) and then show this in a 'total' field. THEN, the
system
to give a running total on the main form of the leave remaining
(total_leave minus the subform_total).

I've tried this using queries for the calculations, but when I do a
subform
based on the query I keep getting 'You have chosen fields from record
sources which the wizard can't connect.

Any ideas anyone? I'm getting sooooo confused with the way access
handles
calculated fields...

Thanks
Mark
Click to expand...
Click to expand...
 
Thanks, That's how I can't do it, but how DO I do it?

This is what I'm trying at the moment:
In my 'leave_taken' subform, I have the following formula in the control
source of the 'total' field:

=[Mon]+[Tue]+[Wed]+[Thu]+[Fri]+[Sat]

Which works great.

Then, on my Main Form, I have the following formula in the control source
of the 'leave_remaining' field:

=[leave_entitlement]-DSum("[Mon]+[Tue]+[Wed]+[Thu]+[Fri]+[Sat]
","Leave_taken","[employee_no]=Emp_No")

This too works, but not very well. It will give the answer correctly when
I first go into the form, but when I add another subform record, the total
does not automatically update.

Any ideas?
 
You need to requery the control source after any update of the related
controls in the subform. To do that, put:

Forms!myMainForm!myCalcControl.Requery

in the After Update property/event of each control /in the subform that is
referenced in the main form calculation.

BTW, why did you decide on a field for each day of the week. In a better
data structure, you would have just one date field (perhaps two for
start/stop), then use functions like DateDiff to calculate the difference
between dates.
-Ed
 
I have each day of the week to emulate the paper 'leave sheet' that an
employee has, and to make it easier for the staff to input the data.
I don't need any fancy reporting from it, just need it to be easy to input
data and view records.

e.g.

Main Form
---------
employee_number,
forename, surname,
total_leave, leave_remaining

Subform
-------
week_beginning, Mon,Tue,Wed,Thu,Fri,Sat, Total
< hours booked per day >

Example
=======
Main Form:
Employee No: 1351234
Name: Mark Hughes
Total Leave: 100, Leave Remaining: (total_leave - sum of subform totals)

Subform:
07/02/2005 7,0,0,0,0,7 Total: 14
14/02/2005 0,0,7,7,7,0 Total: 21
21/02/2005 0,7,0,0,0,0 Total: 7
 
So...

If my Main Form is: 'employee_details'
and my subform is: 'Leave_taken subform'

Then
in the Calculated total field of the subform, in the 'After Update'
control, I'd put

Forms!employee_details!leave_remaining.Requery

Does this look right???


where 'leave_remaining' is the field on my main form which I'm trying to
auto update, containing the formula:
=[leave_entitlement]-DSum("[Mon]+[Tue]+[Wed]+[Thu]+[Fri]+[Sat]
","Leave_taken","[employee_no]=Emp_No")
in the control source
 
Looks OK to me, but you can test it much faster then I, simply by trying it
and noting the results. You'll probably need to enclose all those object
names that contain underscores within square brackets (e.g.
[employee_details] ) and the reference is actually to the control name (not
necessarily the same as the bound field name/control source name.
-Ed
 
Back
Top