Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2004

    Question Unanswered: Getting Total for field in subform...

    I can't figure this out!!! I have a table for employees, employee certifications(stores each employees certs), and certifications(stores a bunch of certifications). I created a form for the employee information and a subform for the multiple certs that the employee has. Each cert has a value(1, 2, or 3) that I defined in the certification table. I need to add the certs for each employee to show a total cert value, but I don't no where to start. I'am having a brain freeze ... I would appreciate any help.

    I.T. Intern

  2. #2
    Join Date
    Nov 2002

    Wink Two possibilities

    You could, via a query, have the certValue for each line as an invisible field and then, in the subform's form footer, have a field named something like txtTotal whose control source is =Sum(certValue). I have, though, ran into problems with this method where it doesn't update the sum until a new record is added, and then the value of the new line isn't part of the sum. So, what I started doing was making totals queries. For you, I suspect it would be something like just having the employeeCerts and Certs table in one query, with groupby pulled down for employeeID and sum pulled down for CertValue. Then you could put code under the afterupdate event for the CertID on the subform that first did a recalc and then did a dlookup("Sum of CertValue", "totalsQuery","employeeID = " & me!employeeID") and set the txtTotal box to this value. You may also have to do a docmd.gotorecord certid of the cert that you just entered, as the recalc will tend to move your cursor back to the first record. Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts