Sum in Unbound Textbox (1 Viewer)

JPaulo

Developer
Local time
Today, 18:55
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

vbaInet

AWF VIP
Local time
Today, 18:55
Joined
Jan 22, 2010
Messages
26,374
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

Developer
Local time
Today, 18:55
Joined
Dec 21, 2009
Messages
185
Thanks for responding friend, but see the mdb that will better understand.
 

vbaInet

AWF VIP
Local time
Today, 18:55
Joined
Jan 22, 2010
Messages
26,374
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
 

missinglinq

AWF VIP
Local time
Today, 13:55
Joined
Jun 20, 2003
Messages
6,385
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

Developer
Local time
Today, 18:55
Joined
Dec 21, 2009
Messages
185
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

Registered User.
Local time
Today, 18:55
Joined
Aug 26, 2008
Messages
2,898
The Form_Current event will only fire as you Scroll through the records
 

vbaInet

AWF VIP
Local time
Today, 18:55
Joined
Jan 22, 2010
Messages
26,374
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

AWF VIP
Local time
Today, 13:55
Joined
Jun 20, 2003
Messages
6,385
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

AWF VIP
Local time
Today, 18:55
Joined
Jan 22, 2010
Messages
26,374
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:

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:

JPaulo

Developer
Local time
Today, 18:55
Joined
Dec 21, 2009
Messages
185
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

AWF VIP
Local time
Today, 18:55
Joined
Jan 22, 2010
Messages
26,374
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

Developer
Local time
Today, 18:55
Joined
Dec 21, 2009
Messages
185
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

AWF VIP
Local time
Today, 18:55
Joined
Jan 22, 2010
Messages
26,374
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

Developer
Local time
Today, 18:55
Joined
Dec 21, 2009
Messages
185
Now is perfect.

Very very thanks

Regards
 

JPaulo

Developer
Local time
Today, 18:55
Joined
Dec 21, 2009
Messages
185
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
 

vbaInet

AWF VIP
Local time
Today, 18:55
Joined
Jan 22, 2010
Messages
26,374
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
 

vbaInet

AWF VIP
Local time
Today, 18:55
Joined
Jan 22, 2010
Messages
26,374
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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom