434,636 Members | 1,945 Online
+ 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
5 Replies

 P: n/a 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 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 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" 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.