Sum unbound textbox on continuous form footer

[dkelly]

Sorry, me again.

Now I am absolutely sure this has been raised before so pardon my ignorance but I am trying to sum an unbound textbox in a continuous form and getting #Error no matter what I try.

Context

I have a textbox which is bound to a table [hours], and a textbox that is unbound using a DLookUp to find a cost rate [cost]. I have a third unbound textbox on my form that looks at [hours] * [cost] called [TC]

In my form footer I want to sum the results for [TC], i.e. if my form has 3 rows @ £100 each my form footer should show £300.

I have tried =Sum([TC]) and =Sum([frm]![TC]). Both give me a #Error result. A quick google search led me to the following solution for unbound boxes =Sum([hours] * [TC]) which gives me the same error so now I am at a complete loss.

Is there a very obvious, simple explanation? My Access skills are not brilliant, so it's very plausible to assume I have made a very basic, fundamental mistake somewhere!

 

[Micron]

I won't have time to test in order to refresh my memory but here's the thing. On a cf form there is only of any control in the detail section. What you're looking at is repeated instances of it, so you really can't sum over one control when it's not bound. If you can get your calculated values returned by a query and base your form on that query you should be able to DSum over that query field. You could try DSum on your field, but if I understand, it is not bound to anything so I doubt that will work either. DSum is a domain aggregate function - it sums over the records in a field, which is why I'm suggesting the query. I do think you can Sum over a bound field though.
Gotta run.

 

[dkelly]

Would it be possible to add a bit of VBA code that copies the calculated field into column on the table after the hours are updated?

So loop to the ID of the row in question updated the total cost column with the calculated result in [TC] and then DSum on table field using the reference as a criteria.

 

[Micron]

As a general rule you should not store calculations so maybe you ought to be rethinking your approach. Just about anything is possible but that doesn't mean one should do it. Maybe this will help explain it:

Allen Browne - Inventory Control: Quantity on Hand

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.

allenbrowne.com

 

[dkelly]

I am not thinking of storing a calculation more a piece of code that would copy the value in to a corresponding column in the table, for example:

I input an hour into the [hour] text box, a calculated box pulls in the relevant hourly rate, and then my total cost text box calculates the total cost (rate * hours). When my hours are updated the code would look at the ID reference of that line in the continuous form and copy the Me.TC.Value into the relevant column for the correct ID.

I've tested it on AfterUpdate but if I have more than one line of data it leaves out the last line. On change or before update does nothing. So it seems like it can work, but may just need to be tweaked in a way that is beyond my skills.

My code is as follows:

Option Explicit
Option Compare Database

Private Sub Hours_AfterUpdate()
Dim dbsQuoteForm As DAO.Database
Dim rs As DAO.Recordset
Dim Answer As Integer
Dim record As String

Set dbsQuoteForm = CurrentDb
Set rs = dbsQuoteForm.OpenRecordset("tblQteLabour")

rs.MoveLast
rs.Edit
rs("TotalCost").Value = Me.TC.Value
rs("TotalSell").Value = Me.SP.Value
rs("TotalOH").Value = Me.OHC.Value
rs.Update
End Sub

 

[Micron]

... a calculated box pulls in the relevant hourly rate - I guess that's done by DLookup or similar
... , and then my total cost text box calculates the total cost (rate * hours). - I guess that's what TC is
... copy the Me.TC.Value into the relevant column for the correct ID. - If that's a bound "column" (field) then how is that not storing a calculation??

I'm not going to beat you into conforming to something (especially if you're not and I'm just not understanding). Perhaps if you could grab some data that shows both the inputs and desired output I could tell you what you need to do. You're opening a recordset and jumping to the last record yet say the last record in the records gets missed. That isn't making sense to me. So seeing some records (inputs) and what you want out of it should help. Your code needs some tweaking as well, but I think it makes more sense for me to get a better understanding of the situation first.

 

[jackd]

Post a copy of the database with instructions for what you want and what you're currently getting. Difficult to give focused/meaningful response when we're guessing at structure, needs and details.

 

[Micron]

You can attach files to a post here? In spite of trying to figure out how a few times I never figured it out & assumed you just can't.

 

[jackd]

Post a copy of the database with instructions for what you want and what you're currently getting. Difficult to give focused/meaningful response when we're guessing at structure, needs and details.

@Micron,
Guess I haven't participated in this forum very much over the years. I found this which isn't very helpful especially for Access (non Excel specific). Seems a missing piece to me. bBut the OP can post it anywhere and provide links and instructions.