Solved Sum([Unbound Field]) in Continuous Form Using VBA Loop (1 Viewer)

[cdoner]

Asking for help please.

Using an unbound text box in the footer of a subform I am trying to calculate a sum of values from a recordset in a continous form using a loop. Additionally, the field for which I am trying to sum is also an unbound field that holds an expression. dB file attached. From Home select Menu Items, click the hyperlink to view MenuItemDetails.

The example I am testing has three records and when I run the code below I see that it runs three times but instead of adding A + B + C for each record it is adding up A + A + A, or if I move focus to recordset B it will run B + B + B, same for recordset C. What am I missing here?

Private Sub form_current()

Dim dblTotal As Double
With Me.RecordsetClone
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF
        dblTotal = (dblTotal) + Nz(Me.txtComponentSubtotal.Value, 0)
        'Debug.Print dblTotal 'View in Immediate Window "Ctrl +G"
        .MoveNext
    Loop
End With
Me.txtTotal_MenuItemCost = dblTotal
End Sub

 

[Gasman]

You are using form value and not recordset value

 

[cdoner]

You are using form value and not recordset value

I have tried a query approach as well. In the db File look at "qrySUBFORM MenuItemCost". In that query I have an expression which calculates the subtotal for each MenuItemDetailsComponentID as [ComponentSubTotal] but if I try =sum([ComponentSubTotal]) the result is: #Error

 

[Gasman]

I have tried a query approach as well. In the db File look at "qrySUBFORM MenuItemCost". In that query I have an expression which calculates the subtotal for each MenuItemDetailsComponentID as [ComponentSubTotal] but if I try =sum([ComponentSubTotal]) the result is: #Error

So why do you use NZ() with the recordset and not with the query?

 

[Gasman]

This is your source for that form control.

=([txtComponentPrice]/[txtComponentNET])*[NET]

You need to calculate the same in some way within the recordset if you are going to do it that way.

 

[Gasman]

See if this is what you want.
I had to change the query and add the component name into it, plus amend the code to look at the correct data.
I was expecting the data to just be a field in the recordset, not a calculation, which is why I changed the query.

We have another member here who repeatedly uses a recordset clone, but refers to the form control each time.
This I believe, was your error.?
I have no idea as to whether the figures are correct, but I believe you are at least looking at the correct data now?

 

[Pat Hartman]

the field for which I am trying to sum is also an unbound field that holds an expression.

Use the form's footer to sum the data. Add a control and repeat the calculation.

So if you have cntl1 in the detail section and its ControlSource is:
= ((fld1 * fld2) + 3) / fld3

Then the ControlSource of the sum control in the footer would be:
= Sum(((fld1 * fld2) + 3) / fld3)

You CANNOT use
= Sum(cntl1)

 

[cdoner]

We have another member here who repeatedly uses a recordset clone, but refers to the form control each time.

Misery loves company

But jokes aside, you have solved my issue, thank you. I'm taking a closer look at your changes to understand how I could have approached this differently.

 

[cdoner]

@Gasman I think I see what you have done to make the calculation work:

• SUBFORM MenuItemDetails record source was changed from "qrySUBFORM MenuItemDetails" to "qrySUBFORM MenuItemCost"
  • "qrySUBFORM MenuItemCost" has a field with the expression which calculates component subtotal AS [ComponentSubtotal]
• VBA code ammended to show dblTotal = (dblTotal) + Nz(.Fields("ComponentSubtotal"), 0)

However, with the previous query "qrySUBFORM MenuItemDetails" I was able to edit/change values for the various fields which is essential. I understand that when you work with a union query the records are view only.

Here's another approach I was trying but wasn't making it work. Using the query that will allow me to make changes "qrySUBFORM MenuItemDetails".... I have tried building an expression for [ComponentSubtotal] AS:

Expr1: ([MenuItemDetailsComponentID].column(2)/[MenuItemDetailsComponentID].column(3))*[NET] but with this approach I get Undefined function'[MenuItemDetailsComponentID].column' in expression.

 

[Pat Hartman]

Did you try this - I made the names match yours

=([txtComponentPrice]/[txtComponentNET])*[NET]

Becomes:

= Sum(([txtComponentPrice]/[txtComponentNET])*[NET])

Rather than using the code loop.

 

[cdoner]

@Pat Hartman I had tried that and the result is: #Error

=([txtComponentPrice]/[txtComponentNET])*[NET] will work and shows the value correctly for the record selected but as soon as I try to =sum() I get the error.

 

[Pat Hartman]

Since this is in a subform, the total field needs to be in the subform also. Then you can add a control on the main form to reference the field in the footer of the subform:

 

[cdoner]

@Pat Hartman

Since this is in a subform, the total field needs to be in the subform also. Then you can add a control on the main form to reference the field in the footer of the subform:

I believe I have this part correct. The unbound text field is in the footer of the subform, just trying to get the correct control source or VBA to show the sum of the selected field from the continous form. I really think what is gumming this up is the union query but also [NET] is an expression using a switch statement in SQL.

What I can do is create two subforms, one that allows me to make the edits.... and the other which calculates the subtotal after the edit is made. But that would mean running two queries. I can live with that but thought I was overlooking some simple principle of database design once this thing grows in file size.

 

[Pat Hartman]

=([txtComponentPrice]/[txtComponentNET])*[NET] will work and shows the value correctly for the record selected but as soon as I try to =sum() I get the error.

Try it with out the "NET" part to see if that is the issue. Just replace Net with 2, for example

 

[Gasman]

@Gasman I think I see what you have done to make the calculation work:

• SUBFORM MenuItemDetails record source was changed from "qrySUBFORM MenuItemDetails" to "qrySUBFORM MenuItemCost"
  • "qrySUBFORM MenuItemCost" has a field with the expression which calculates component subtotal AS [ComponentSubtotal]
• VBA code ammended to show dblTotal = (dblTotal) + Nz(.Fields("ComponentSubtotal"), 0)

However, with the previous query "qrySUBFORM MenuItemDetails" I was able to edit/change values for the various fields which is essential. I understand that when you work with a union query the records are view only.

Here's another approach I was trying but wasn't making it work. Using the query that will allow me to make changes "qrySUBFORM MenuItemDetails".... I have tried building an expression for [ComponentSubtotal] AS:

Expr1: ([MenuItemDetailsComponentID].column(2)/[MenuItemDetailsComponentID].column(3))*[NET] but with this approach I get Undefined function'[MenuItemDetailsComponentID].column' in expression.

OK, if Pat's solution does not get you there, the key part is to calculate the value in the query, as that is the recordset you are using?
I just changed the query as it used one same table and joined to the other.?

 

[oleronesoftwares]

Hi,
Can you change txtComponentPrice control source to component price
then on the afterupdate event in vba for component id it will be

txtComponentPrice=[MenuItemDetailsComponentID].[column](2)

do the same for txtComponentNET, change the control source to ComponetNet
then on the after update event in vba for txtComponentNET, it will be
txtComponentNET=[MenuItemDetailsComponentID].[column](3)

finally same thing should be done for txtComponentSubtotal

You can then try the formula that is given error message.

 

[cdoner]

@Gasman @Pat Hartman @oleronesoftwares

Thank you all for your assistance. Here's what I ended up doing to work around my issue:

• I created a second subform referencing the query used by Gasman that worked for the desired calculation
• I put that second subform into a page, placed the unbound field into the footer of that subform
  • So now I have two pages, one that I can edit and one that I cannot edit but will calculate the total
• Placed an unbound text box in the form header with its source referencing the unbound text box in the subform footer
• Made the second tab not visible so now it looks like nothing was ever changed visually
• Installed a refresh all command button to update the calculation.

Again, a big thank you for helping me. Let's consider this solved.

 

[Pat Hartman]

Glad you worked it out but I'm pretty sure there must be an easier way.

I didn't look at Gasman's solution because I didn't know what form he changed. But, changing a query to aggregate data, which he might have done, would explain why the form became not updateable.

 

[oleronesoftwares]

Thank you all for your assistance. Here's what I ended up doing to work around my issue:

• I created a second subform referencing the query used by Gasman that worked for the desired calculation
• I put that second subform into a page, placed the unbound field into the footer of that subform
  • So now I have two pages, one that I can edit and one that I cannot edit but will calculate the total
• Placed an unbound text box in the form header with its source referencing the unbound text box in the subform footer
• Made the second tab not visible so now it looks like nothing was ever changed visually
• Installed a refresh all command button to update the calculation.

Again, a big thank you for helping me. Let's consider this solved.

Good to know ,you solved it. well done.

 

[Gasman]

I just used what appeared to be a very similar query in design view, that had the calculation needed.
I suppose the calculation could just as easy be done within the recordset?

Edit: Starting to get sucked off with this new phone's autocorrect.