Sum in Unbound Textbox

[JPaulo]

Hi all;

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

how can I solve this problem ?

See the attc please

 

[JPaulo]

Help me please

 

[vbaInet]

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?

 

[JPaulo]

Thanks for responding friend, but see the mdb that will better understand.

 

[vbaInet]

Oh i got you now. Just ran a quick test:

    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

 

[missinglinq]

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.

 

[JPaulo]

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

 

[Rich]

The Form_Current event will only fire as you Scroll through the records

 

[vbaInet]

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

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.

 

[missinglinq]

I believe the On Current event fires immediately after the form loads, tip.

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.

 

[vbaInet]

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

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

    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

 

[JPaulo]

Work a charm, very very thanks to two.

 

[JPaulo]

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 ?

 

[vbaInet]

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 ?

What is the problem?

 

[JPaulo]

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.

 

[vbaInet]

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.

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.

 

[JPaulo]

Now is perfect.

Very very thanks

Regards

 

[JPaulo]

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.

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

 

[vbaInet]

Untested 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

 

[vbaInet]

Untested 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