DBGrid and DAO

[tbpcpa]

Constructing an invoice screen where the details of the invoice are on the top of the form, with appropriate input boxes for demographic data, and a set of input box for detail input for the invoice. Below that is a DBGrid (DAO) which contains the appropriate detail of that particular invoice.

That part works fine. The question is how, using DAO, can I accumulate columns using DBGrid in DAO. I'm at a total loss? I've looked and read everything I can and NOTHING yet.

Any assistance is appreciated. Ever felt like a fool? LOL

 

[billy71]

I have looped thru the recordset that loads the DBGrid and accummulated each field and placed the totals in textboxes to ouput when it is finished with the recordset. This works well when I load or refresh the recordset.
First you find the total records in the recordset then use a loop. This may not be what you have in mind but it is something I have used. If you would like more info I will post some code.
Billy

 

[tbpcpa]

Any code along that line would be appreciated

 

[billy71]

I hope you will find this useful. It works for me in Several different locations. I am sure there will be another way and any suggestions on how to improve it would be appreciated.

Public Sub showAllDues()
'
On Error GoTo noAdd
'
noDues = False: outBalance = 0: txtBalDue = ""
txtCurrentYear(0) = "": txtCurrentYear(1) = ""
txtCurrentYear(2) = "": txtCurrentYear(3) = ""

Dim iNumberrecords As Integer
Dim ctr As Integer
'Dim outBalance was set in the General Declarations
'This program selects from a Table containing annual dues for Active members
' The table is named tblAllDues and the index in that table is Number
'There will never be more than 2 years outstanding and ctr determines which text
'box receives the outstanding dues. This is loaded when the Member Data is
'Displayed. Another routine will be used to
'post the dues and update the balances.
'They are slected Using a number named CurrentRecord which comes from the result
'of a Selected Name from a Combo Box.
'This may not be the way most people select but it is working for me.
'
Dim strQuery As String
strQuery = "Select * From tblAllDues Where val(Number) = '" & _
Val(CurrentRecord) & "' Order By year "
'
'dtaAllDues is the Name Assigned to a Data Control
'
With dtaAllDues
.RecordSource = strQuery
.Refresh
End With
'
With dtaAllDues.Recordset
iNumberrecords = .RecordCount
If iNumberrecords = 0 Then noDues = True
'
'Calculate Balance due
'
If noDues = True Then GoTo noAdd 'if there is no record in recordset bypass.
'
For Ln = 1 To iNumberrecords 'Loop thru the Recordset.
outBalance = outBalance + !Balance '!Balance is the name of the Recordset field.
If outBalance > 0 Then ctr = ctr + 1 'This detail may appear in two different txtBoxes
If ctr = 1 Then txtCurrentYear(0) = !Year 'Year is the name of a recordset field
If ctr = 1 Then txtCurrentYear(1) = Format(!Dues, "currency") 'Dues is a field name
If ctr = 2 Then txtCurrentYear(2) = !Year
If ctr = 2 Then txtCurrentYear(3) = Format(!Dues, "currency")
.MoveNext
Next
'Loop is finished pass the total to a textbox which
'is displayed on the Side of the Grid.
txtBalDue = Str(outBalance)
FormText = Val(txtBalDue)
txtBalDue = Format(FormText, "$##.#0")

End With
'
noAdd:
'
End Sub