Sum in Unbound Textbox

JPaulo

JPaulo

Developer
Local time
Today, 15:03
Joined
Dec 21, 2009
Messages
185
Hi all;

Gives error in the sum of a texbox in continuos form footer.

how can I solve this problem ?

See the attc please
 

Attachments

Haven't had a look at your db (yet), but you may be referencing that control incorrectly? One way to do is to count the number of records instead using DCount or count using its recordset?
 
Thanks for responding friend, but see the mdb that will better understand.
 
Oh i got you now. Just ran a quick test:

Code: 
    Dim i As Integer, countClone As Long

    DoCmd.GoToRecord , , acFirst
    countClone = 0
    For i = 1 To Me.RecordsetClone.RecordCount - 1
        countClone = countClone + Nz(Txt2, 0)
        DoCmd.GoToRecord , , acNext
    Next
    Texto20.Value = countClone
 
vbaInet's suggestion should work. You can only use aggregate functions, such as Sum and DSum, against the field of a Domain, i.e. a table or query. You cannot use them against unbound controls in a form.
 
Not yet vbaInet gives error 2105,

Private Sub Form_Current()
Dim i As Integer, countClone As Long
DoCmd.GoToRecord , , acFirst
countClone = 0
For i = 1 To Me.RecordsetClone.RecordCount - 1
countClone = countClone + Nz(Txt2, 0)
DoCmd.GoToRecord , , acNext
Next
Me.Texto20.Value = countClone
End Sub
 
The Form_Current event will only fire as you Scroll through the records
 
JPaulo said:
Not yet vbaInet gives error 2105,

Private Sub Form_Current()
Dim i As Integer, countClone As Long
DoCmd.GoToRecord , , acFirst
countClone = 0
For i = 1 To Me.RecordsetClone.RecordCount - 1
countClone = countClone + Nz(Txt2, 0)
DoCmd.GoToRecord , , acNext
Next
Me.Texto20.Value = countClone
End Sub
Click to expand...

Put that code on the click event of Texto20, you will find that it works. Try and see if you can workout how to make it work with the Current event. I believe the On Current event fires immediately after the form loads, tip.
 
vbaInet said:
I believe the On Current event fires immediately after the form loads, tip.
Click to expand...
Yes it does, which is why placing the code there is a mistake on the OP's part. When OnCurrent fires the first time, the code moves to the next record, which causes the OnCurrent to fire again, which moves it back to the first record, where OnCurrent fires again, and on and on in an endless loop.
 
JPaulo said:
Not yet vbaInet gives error 2105,

Private Sub Form_Current()
Dim i As Integer, countClone As Long
DoCmd.GoToRecord , , acFirst
countClone = 0
For i = 1 To Me.RecordsetClone.RecordCount - 1
countClone = countClone + Nz(Txt2, 0)
DoCmd.GoToRecord , , acNext
Next
Me.Texto20.Value = countClone
End Sub
Click to expand...

I hope missinglinq's comments were helpful and you digested it. I saw a slight flaw in my code and made some amendments:

Code: 
    Dim i As Integer, count012001 As Long, countTotal As Long
    
    Me.RecordsetClone.MoveFirst
    countClone = 0
    countTotal = 0
    
    For i = 1 To Me.RecordsetClone.RecordCount - 1
        count012001 = count012001 + Nz(Me.RecordsetClone.[01/2010], 0)
        countTotal = countTotal + Nz(Me.RecordsetClone.[Total], 0)
        Me.RecordsetClone.MoveNext
    Next
    Texto20.Value = count012001
    Texto18.Value = countTotal
Using DSum in your Total's text box is not the quickest way to get a total. Remove your DSum statement from its Control Source, the code above will set the value for you instead. It looks like your form is going to be a read-only form (for display purposes) so create a function and place that code in it then call the function from the On Load event of the form. I advised you place that code in a function so that if you needed to call it again it's reusable.

NB: You would need to call the function for anytime a record is inserted or deleted
 
Last edited:
Works perfect, but there is a problem, is that the form originated in a Croostab and the field name Nz(Me.RecordsetClone.[01/2010], 0)
changes.

Any idea like resolving ?
 
JPaulo said:
Works perfect, but there is a problem, is that the form originated in a Croostab and the field name Nz(Me.RecordsetClone.[01/2010], 0)
changes.

Any idea like resolving ?
Click to expand...


What is the problem?
 
The problem is in the code is the field name [01-2010] and CrossTab to change [02-2010] gives Run-time error 3265 Item not found.
I can not be changing the name of the field in cod, every time insert dates in the table.
 
JPaulo said:
The problem is in the code is the field name [01-2010] and CrossTab to change [02-2010] gives Run-time error 3265 Item not found.
I can not be changing the name of the field in cod, every time insert dates in the table.
Click to expand...


Use the Fields property of the recordset:

count012001 = count012001 + Nz(Me.RecordsetClone.Fields(2), 0)

The field is like a column. The numbering starts from 0, so (I think) number 2 in your database would be your [02/2010] column and column 1 is for Total. I haven't had the time to open your db so I'm sure you can check that.
 
Hello;
I saw another problem, it sum 1 line to 8.
In fact the continuos form has 8 columns, but has 17 lines to sum.

Code: 
Public Sub MostraSoma()
Dim i As Integer, strTotal, strSoma, strSoma1, strSoma2, strSoma3, strSoma4, strSoma5, strSoma6 As Double
Me.RecordsetClone.MoveFirst
strTotal = 0
strSoma = 0
strSoma1 = 0
strSoma2 = 0
strSoma3 = 0
strSoma4 = 0
strSoma5 = 0
strSoma6 = 0
For i = 1 To Me.RecordsetClone.RecordCount - 1
strTotal = strTotal + Nz(Me.RecordsetClone.Fields(1), 0) ' percorre a segunda coluna
strSoma = strSoma + Nz(Me.RecordsetClone.Fields(2), 0) 'percorre a terceira coluna
strSoma1 = strSoma1 + Nz(Me.RecordsetClone.Fields(3), 0)
strSoma2 = strSoma2 + Nz(Me.RecordsetClone.Fields(4), 0)
strSoma3 = strSoma3 + Nz(Me.RecordsetClone.Fields(5), 0)
strSoma4 = strSoma4 + Nz(Me.RecordsetClone.Fields(6), 0)
strSoma5 = strSoma5 + Nz(Me.RecordsetClone.Fields(7), 0)
strSoma6 = strSoma6 + Nz(Me.RecordsetClone.Fields(8), 0)
Me.RecordsetClone.MoveNext
Next i
Texto18.Value = strTotal
txtA.Value = strSoma
txtB.Value = strSoma1
txtC.Value = strSoma2
txtD.Value = strSoma3
txtE.Value = strSoma4
txtF.Value = strSoma5
txtG.Value = strSoma6
End Sub
 
Untested code:

Code: 
Public Sub MostraSoma()
    Dim yRow As Integer, xField As Long, strSoma() As Long
    
    Me.RecordsetClone.MoveFirst

    ReDim strSoma(1 To 8)
    For yRow = 1 To Me.RecordsetClone.RecordCount - 1
        For xField = 1 To 8
            strSoma(xField) = strSoma(xField) + Nz(Me.RecordsetClone.Fields(i), 0)
        Next
        Me.RecordsetClone.MoveNext
    Next

    Texto18.value = strSoma(1)
    txtA.value = strSoma(2)
    txtB.value = strSoma(3)
    txtC.value = strSoma(4)
    txtD.value = strSoma(5)
    txtE.value = strSoma(6)
    txtF.value = strSoma(7)
    txtG.value = strSoma(8)

End Sub
 
Untested code:

Code: 
Public Sub MostraSoma()
    Dim yRow As Integer, xField As Long, strSoma() As Long
    
    Me.RecordsetClone.MoveFirst

    ReDim strSoma(1 To 8)
    For yRow = 1 To Me.RecordsetClone.RecordCount - 1
        For xField = 1 To 8
            strSoma(xField) = strSoma(xField) + Nz(Me.RecordsetClone.Fields(i), 0)
        Next
        Me.RecordsetClone.MoveNext
    Next

    Texto18.value = strSoma(1)
    txtA.value = strSoma(2)
    txtB.value = strSoma(3)
    txtC.value = strSoma(4)
    txtD.value = strSoma(5)
    txtE.value = strSoma(6)
    txtF.value = strSoma(7)
    txtG.value = strSoma(8)

End Sub
 
You must log in or register to reply here.

Similar threads

J
Conditional Format
Replies
2
Views
231
arnelgp
arnelgp
M
Unbound field in form to display summed field from a query
Replies
3
Views
218
Pat Hartman
P
asteropi
Solved Connecting sums from order detail to order list
Replies
18
Views
658
asteropi
asteropi
H
Solved Sum / Dsum
Replies
6
Views
289
Harry Taylor
H
M
working with unbound field in a form
Replies
8
Views
132
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom