Sum in unbound text box returns #error (1 Viewer)

[aymarhoon]

Hi everybody

Is there a way to sum unbound box. I read several threads and tried their suggested solutions but none of them worked. I attach the database to give you an idea about the problem.

Any idea would be greatly appreciated.

Thanks

 

[June7]

Aggregate function must reference a field, not a control. Otherwise, have to repeat all the calculations within the Sum() function. And those calculations must use fields, not form controls.

Also, problem with subform pulling value from main form is the subform actually loads first, which means the values on main form are not available to the subform calcs.

Change subform RecordSource to:

SELECT CostDetails.*, Costings.TotHrs, Costings.DayHours, Costings.Employees FROM Costings RIGHT JOIN CostDetails ON Costings.ItemID = CostDetails.ItemID;

Then calculate Quantity:
=IIf([Unit]="Hour",[TotHrs],IIf([Unit]="Day",RoundUp([TotHrs]/[DayHours]),IIf([Unit]="Employee",[Employees],IIf([Unit]="Group",1,Null))))

And the Sum():
=Sum(Nz(IIf([Unit]="Hour",[TotHrs],IIf([Unit]="Day",RoundUp([TotHrs]/[DayHours]),IIf([Unit]="Employee",[Employees],IIf([Unit]="Group",1,Null))))*[Rate],0))

 

[arnelgp]

you may also try this.
added a function to compute the "Quantity" (see modUtil).
added a query and calls the function.
make the query as the recordsource of your subform.
modified the total cost textbox.

 

[aymarhoon]

Thank you very much, both solutions worked very well.

I have another question... How can I make the incremental change of the spinner button by 0.1 not a whole number?

Thanks a million times

 

[arnelgp]

open costing form in design view.
go to vba (alt-f11), then change the increment:

Option Compare Database
Option Explicit
Public varAct As Long

Private Sub SpinBtnHrs_Updated(Code As Integer)
varAct = Me.DayHours
Select Case SpinBtnHrs
    Case Is < varAct
      Me.DayHours = Me.DayHours - 0.1 '1
    Case Is > varAct
     Me.DayHours = Me.DayHours + 0.1 '1
End Select
Me.Dirty=False
[Forms]![frmCostings]![frmCostings_sfrm].Requery
End Sub

Private Sub SpinBtnProf_Updated(Code As Integer)
varAct = Me.ProfitMargin
Select Case SpinBtnProf
    Case Is < varAct
      Me.ProfitMargin = Me.ProfitMargin - 0.1 '1
    Case Is > varAct
     Me.ProfitMargin = Me.ProfitMargin + 0.1 '1
End Select
Me.Dirty=False
Forms!frmCostings_sfrm.Form.Requery
End Sub

Private Sub SpinBtnTrns_Updated(Code As Integer)
varAct = Me.Trainees
Select Case SpinBtnTrns
    Case Is < varAct
      Me.Trainees = Me.Trainees - 0.1 '1
    Case Is > varAct
     Me.Trainees = Me.Trainees + 0.1 '1
End Select
Me.Dirty=False
[Forms]![frmCostings]![frmCostings_sfrm].Requery
End Sub