Footer totals on Form

[GShen]

I have a continuous form. I want to be able to keep a total in the footer. It works if I create a field in the footer like =Sum([fieldname]), where fieldname is a field on the detail line. However, Lets say I have 2 fields qty and price. I have a field on the detail line (unbound) called TOTAL. (qty * price). I then go to the footer and I want a sum of the TOTAL, so I put =Sum([Total]). It doesn't like it, I keep getting an error. I looked this up everywhere, tried 1000 girgitations and I cannot get this to work for the life of me. There has to be a simple answer.

I anxiously await a simple answer, starting with, THIS IS EASY....

Thanks, hope to hear from someone.

I had previously posted in ACCESS FORMS and I was told to try here.

Thanks,

remember when everything worked and there was a reason for it?

 

[cjowsey]

Well, it's not that hard... :

You need a VBA function to return the total sum from your database and then populate a call to the function in the Current event of the form. So you would have something like this.

Public Function GetFormTotal() As Long
    
    Dim cnn As Connection
    Dim rs  As ADODB.Recordset
    Dim cmd As ADODB.Command
    Dim strSQL As String
    
    strSQL = "SELECT SUM(qty * price) " & vbCrLf & _
             "FROM myTable"
    Set cnn = Application.CurrentProject.Connection
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = cnn
    cmd.CommandType = adCmdText
    cmd.CommandText = strSQL
    
    Set rs = cmd.Execute
    GetFormTotal = Nz(rs(0), 0)
    cnn.Close

End Function

and in the form...

Private Sub Form_Current()
    'Set the running total for the form     
    Me.txtTotal = GetFormTotal()
End Sub

I use the Current event because it will update the footer total if you change qty or price inany row on the form.

You'll want to tidy the code up a bit, but this should get you going.

 

[GShen]

thanks,
In the meantime I kept trying this and I ended up putting the calc. in the stored procedure. I then put these on the detail line (invisible) and used the =sum(Total) in the footer. In the after update event for the 2 fields I am changing I do a requery to update the totals in the footer. It works. I will try your code this afternoon. Always like to know a few ways to perform operations.
Thanks again for getting back to me.

Remember when... everything worked and there was a reason for it?