By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,636 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,636 IT Pros & Developers. It's quick & easy.

Calculated field in subform

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Rog
To display the total on your subform, create a field with the following
control source:
= [Mon] + [Tue] + [Wed] + [Thu] + [Fri] + [Sat] (don't forget the =
sign)

In your main form, change the name of your employee_number field to
displayno.
Now add a field with the following in the control source:
=[total_leave]-DSum("[Mon]+[Tue]+[Wed]+[Thu]+[Fri]+[Sat]","tblLeave","[employee_number]=displayno")
(use the name of your leave table instead of tblLeave)

Nov 13 '05 #2

P: n/a
Thanks Rog, I'll give it a whirl.

Can you explain why I need to change the employee_number though and
what is the logic behind the formula? I'm very new to Access and need
things explained simply. Be gentle with me. ;-)

Cheers
Mark

In your main form, change the name of your employee_number field to
displayno.
Now add a field with the following in the control source:
=[total_leave]-DSum("[Mon]+[Tue]+[Wed]+[Thu]+[Fri]+[Sat]","tblLeave","[employee_number]=displayno")
(use the name of your leave table instead of tblLeave)

Nov 13 '05 #3

P: n/a
Rog
If you don't change the textbox name the statement would be
[employee_number] = employee_number, which gives the wrong result since
it is always true.

Nov 13 '05 #4

P: n/a
Rog
The formula totals the fields [Mon] through [Sat] in table tblLeave for
those records where the employee_number is the one currently displayed,
and subtracts it from the employee's total leave.

Nov 13 '05 #5

P: n/a
Thanks Rog,

this works great, except that when I add a new record to the
leave_taken subform, the total on the main form for leave_remaining
does not update. Any ideas?

"Rog" <de********@state.gov> wrote in message news:<11*********************@g14g2000cwa.googlegr oups.com>...
The formula totals the fields [Mon] through [Sat] in table tblLeave for
those records where the employee_number is the one currently displayed,
and subtracts it from the employee's total leave.

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.