Totals on form (1 Viewer)

[sparklegrrl]

Hello all,

I am working on whichever version of Access comes with 365 (I can't seem to find the version).

I have a form based on a query to enter employee time. I have a field for hours and a drop-down box for earnings codes in the form detail.

In the form footer, I need to total the hours based on the code selected in the drop-down code. For example, all the sick time and all the vacation time.

Thanks in advance for your help!

 

[theDBguy]

Hi. What have you tried? Have you tried using DSum()?

 

[sparklegrrl]

I am unfamiliar with that. Right now I have an if then Text box that pulls the hours based on the code in the drop down box, for example: =iif([Combo45]="100",[Hours],0)

It works fine but I need to total it for all the detail lines in the footer. In the footer I have =Sum([Text45]). I'm getting #error.

 

[theDBguy]

I am unfamiliar with that. Right now I have an if then Text box that pulls the hours based on the code in the drop down box, for example: =iif([Combo45]="100",[Hours],0)

It works fine but I need to total it for all the detail lines in the footer. In the footer I have =Sum([Text45]). I'm getting #error.

Try this:

=Sum(IIf([Combo45]="100",[Hours],0))

 

[sparklegrrl]

It's still giving me the #error.

 

[theDBguy]

It's still giving me the #error.

Yeah, for now, you may have to go with DSum().

Application.DSum method (Access)

Office VBA reference topic

docs.microsoft.com

 

[zeroaccess]

Version is found in the File menu under Account.

Does your form already filter out non-sick and non-vacation records when you choose that from the combo box? If so, then =Sum([fieldname]) in the footer should work.

If not, maybe a DoCmd.ApplyFilter on the After Update of the text box would help.

    DoCmd.ApplyFilter , "[recordsourcename]='" & Me.Text45 & "'"

You'll then need a "Clear" button with the code:

    DoCmd.SetFilter , Null

An alternative would be to set it to Datasheet view and add the totals row at the bottom.

It is my understanding that DSum is only necessary for records not in your recordset. In your case, you are working with fields in your recordset, so there should be better ways to do this. DSum does have a performance penalty.

 

[strive4peace]

hi sparklegrrl,

How about this:

1. make a query with EmployeeID, Code, and Hours. Group By EmployeeID and Code. Sum Hours.

2. use this query to create a form that shows multiple records.

3. Put this form in the footer of the form you asked your question about, as a subform. Set LinkMasterFields and LinkChildFields to EmployeeID (Data tab of Property Sheet for the subform control)

4. In the AfterUpdate event of the mainform, requery the subform with sums

me.subform_controlname.requery

5. If you want sums to show up right away after you change the Code or the Hours, on the AfterUpdate event of each control, save the record

me.dirty = false

 

[strive4peace]

> "I can't seem to find the version "

choose File, Account
on the right, click About Access

 

[sparklegrrl]

Got it! I pulled the numbers in the query then totaled the new field and it seems to be working. Yeah!

 

[theDBguy]

Got it! I pulled the numbers in the query then totaled the new field and it seems to be working. Yeah!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[strive4peace]

happy for you! onward and upward