code not working

[anjujp]

Hi,
My problem is that I have 2 databases,one from which I take the records and work on it and update the second one with its results.
I opened both of them using adodc(ado data control)

ADODC1-database whose records are used for calculations.
adodc2-database whose records are updated with the results.
Now the problem (I dont know ) is that the part where it is supposed to sum up the subtotals ,those are just reading the last record and are not responding to the loops.

Please ,please somebody read thru and check it up,where I have gone wrong.
I am pasting the entire code::


Dim shipped As Double
Dim eprice As Double
Dim j As Integer

Dim WithEvents rstship As ADODB.Recordset
Dim sdate, edate As Variant
Dim subtotalqty, subtotaleprice As Double
Dim sqlstate As String
Private Sub cmdPrint_Click()
CrystalReport1.Action = 1


End Sub

Public Sub reportX(sdate, edate, sqlstate As String)

subtotalshpqty = 0
subtotaleprice = 0

'rstship.Source = Adodc1.RecordSource
Set rstship = New ADODB.Recordset
Set rstship.DataSource = Adodc1

If Not rstship.BOF Then
rstship.MoveFirst
Do Until rstship.EOF = True
i = i + 1
rstship.MoveNext
Loop
Else
Do Until rstship.EOF = True
i = i + 1
rstship.MoveNext
Loop
End If
'to get the subtotal of the quantity and extended price
'Do While (rstship.Fields("CURDUE_28") >= sdate) And (rstship.Fields("CURDUE_28") <= edate)
If rstship.RecordCount = 0 Then
subtotalshpqty = 0
subtotaleprice = 0
Else
rstship.MoveFirst
'checking the date to add up the subtotals.
shipped = rstship.Fields("SHPQTY_28")
eprice = rstship.Fields("PRICE_28") * shipped
subtotalshpqty = subtotalshpqty + shipped
subtotaleprice = subtotaleprice + eprice
Text1.Text = subtotalshpqty
Text2.Text = subtotaleprice
For j = 1 To i - 1
rstship.MoveNext
shipped = rstship.Fields("SHPQTY_28")
eprice = rstship.Fields("PRICE_28") * shipped
subtotalshpqty = subtotalshpqty + shipped
subtotaleprice = subtotaleprice + eprice
Next j
End If
'Loop
End Sub

Private Sub cmdStart_Click()
lblStatus.Caption = "retrieving data......"
mainX
lblStatus.Caption = "process complete...."
End Sub

Private Sub Command3_Click()
End

End Sub

Public Sub mainX()
Dim n As Integer
Dim p As Long
Dim firstsunday As Variant
Dim totalqty, totaleprice As Double
Dim rstmax As ADODB.Recordset

weeknumber = Format(Date, "ww", vbSunday, vbFirstFullWeek)
dayofweek = Format(Date, "w", vbSunday)
firstsunday = Date - dayofweek + 1 - ((weeknumber - 1) * 7)
Set rstmax = New ADODB.Recordset
Set rstmax.DataSource = Adodc2
'rstmax.Open
p = 0 'populating the recordset
If (rstmax.BOF = True) And (rstmax.EOF = True) Then
p = 0
ElseIf rstmax.BOF = False Then
rstmax.MoveFirst
Do Until rstmax.EOF = True
p = p + 1
rstmax.MoveNext
Loop
End If
sqlstate = "SELECT SO^Detail.CURDUE_28,SO^Detail.ORDNUM_28,SO^Detail.PRTNUM_28,"
sqlstate = sqlstate & "SO^Detail.CURQTY_28,SO^Detail.SHPQTY_28,"
sqlstate = sqlstate & "SO^Detail.PRICE_28,SO^Master.REASON_27 FROM SO^Detail,"
sqlstate = sqlstate & "SO^Master WHERE SO^Detail.ORDNUM_28=SO^Master.ORDNUM_27 AND"
sqlstate = sqlstate & "SO^Master.REASON_27<>'4' AND SO^Master.REASON_27<>'10' AND"
sqlstate = sqlstate & "SO^Master.REASON_27<>'16'"
If p <> 0 Then 'deleting all the existing data in the recordset
rstmax.MoveFirst
Do Until rstmax.EOF = True
rstmax.Delete
rstmax.MoveNext
Loop
End If
lblaccess.Caption = "adding data in access tables"
frmshpHistory.Refresh

For n = 0 To 51
sdate = Format(firstsunday + (7 * n), "mm/dd/yyyy")
edate = Format(firstsunday + 6 + (7 * n), "mm/dd/yyyy")
sqlstate = sqlstate & "and SO^Detail.CURDUE_28 >= '" & sdate & "' AND SO^Detail.CURDUE_28 >= '" & edate & "'"
Adodc1.RecordSource = sqlstate
frmshpHistory.Refresh
Call reportX(sdate, edate, sqlstate)
'adding the total and date values in the access record
rstmax.AddNew
rstmax.Fields("shpqty") = subtotalqty
rstmax.Fields("eprice") = subtotaleprice
rstmax.Fields("Sdate") = sdate
rstmax.Fields("Edate") = edate
Next n
rstmax.UpdateBatch

lblaccess.Caption = "please check the access data"
'connaccess.Close
CrystalReport1.ReportFileName = "c:\Anju\rptAccess.rpt"
CrystalReport1.ReportSource = crptReport
CrystalReport1.PrintReport
End Sub

please check it up and thanks

anjujp
email:[email protected]

[Danny Layne]

I think you may find it considerably easier to use the SUM aggregate function in SQL to return a single-row recordset containing your sum, rather than looping through each record in your return r-set. See any SQL book or Access help for further guidance.....