If you can figure this out then you are a genius..
This code work well in 97 but not in 2000.. This code is used to create a report.. It groups together customers by name (To) field.. Then it groups receipt# for each customer.. Then it groups by Description.. After this is done then it totals they quantity by description.. It does all of the above except the quantity.. For the quantity on the report it just puts the total next to each other.. Like if you had 2 records 1 record was for 4 boxes and the other for 8 boxes it would show the quantity as 84 not 12.. Like I said if you can figure this out your are trully an access genius.. I have a small sample dbase that I can send out if you want to take a look at it.. Thanks and Good luck..
Private Sub Report_Open(Cancel As Integer)
Dim dbs As Dao.Database
Dim rstTmp, rstRep As Dao.Recordset
Dim tmptblTo, tmpDesc As String
' Return reference to current database.
Set dbs = CurrentDb
Set rstRep = dbs.OpenRecordset("IDCReportTBL")
Set rstTmp = dbs.OpenRecordset("SELECT * FROM [tblIDCReceipt] ORDER BY tblTo,tblDescription")
' Populate Recordset object.
rstTmp.MoveLast
' Return to first record.
rstTmp.MoveFirst
' Do until end of file.
Do Until rstTmp.EOF
' Check and see if this record is for a different person and/or description than the last one
'if so just save it to the IDCReportTBL
If tmptblTo <> rstTmp!tblTo Or tmpDesc <> rstTmp!tblDescription Then
rstRep.AddNew
rstRep("tblReceipt#") = rstTmp("tblReceipt#")
rstRep!tblTo = rstTmp!tblTo
tmptblTo = rstTmp!tblTo
rstRep("tblToRoom#") = rstTmp("tblToRoom#")
rstRep!tblQuantity = rstTmp!tblQuantity
rstRep!tblDescription = rstTmp!tblDescription
tmpDesc = rstTmp!tblDescription
Else ' if it is the same than append the additional receipt#s and update the qty in the previous record
rstRep.Edit
rstRep("tblReceipt#") = rstRep("tblReceipt#") & " " & rstTmp("tblReceipt#")
rstRep!tblQuantity = rstRep!tblQuantity + rstTmp!tblQuantity
End If
rstRep.Update 'save the update
rstRep.MoveLast ' make sure current record in report table is the last one
rstTmp.MoveNext ' move to the next record in the temp table
Loop ' go back and do it again
' all done so close everything up and let the report run
rstRep.Close
rstTmp.Close
Set dbs = Nothing
End Sub
This code work well in 97 but not in 2000.. This code is used to create a report.. It groups together customers by name (To) field.. Then it groups receipt# for each customer.. Then it groups by Description.. After this is done then it totals they quantity by description.. It does all of the above except the quantity.. For the quantity on the report it just puts the total next to each other.. Like if you had 2 records 1 record was for 4 boxes and the other for 8 boxes it would show the quantity as 84 not 12.. Like I said if you can figure this out your are trully an access genius.. I have a small sample dbase that I can send out if you want to take a look at it.. Thanks and Good luck..
Private Sub Report_Open(Cancel As Integer)
Dim dbs As Dao.Database
Dim rstTmp, rstRep As Dao.Recordset
Dim tmptblTo, tmpDesc As String
' Return reference to current database.
Set dbs = CurrentDb
Set rstRep = dbs.OpenRecordset("IDCReportTBL")
Set rstTmp = dbs.OpenRecordset("SELECT * FROM [tblIDCReceipt] ORDER BY tblTo,tblDescription")
' Populate Recordset object.
rstTmp.MoveLast
' Return to first record.
rstTmp.MoveFirst
' Do until end of file.
Do Until rstTmp.EOF
' Check and see if this record is for a different person and/or description than the last one
'if so just save it to the IDCReportTBL
If tmptblTo <> rstTmp!tblTo Or tmpDesc <> rstTmp!tblDescription Then
rstRep.AddNew
rstRep("tblReceipt#") = rstTmp("tblReceipt#")
rstRep!tblTo = rstTmp!tblTo
tmptblTo = rstTmp!tblTo
rstRep("tblToRoom#") = rstTmp("tblToRoom#")
rstRep!tblQuantity = rstTmp!tblQuantity
rstRep!tblDescription = rstTmp!tblDescription
tmpDesc = rstTmp!tblDescription
Else ' if it is the same than append the additional receipt#s and update the qty in the previous record
rstRep.Edit
rstRep("tblReceipt#") = rstRep("tblReceipt#") & " " & rstTmp("tblReceipt#")
rstRep!tblQuantity = rstRep!tblQuantity + rstTmp!tblQuantity
End If
rstRep.Update 'save the update
rstRep.MoveLast ' make sure current record in report table is the last one
rstTmp.MoveNext ' move to the next record in the temp table
Loop ' go back and do it again
' all done so close everything up and let the report run
rstRep.Close
rstTmp.Close
Set dbs = Nothing
End Sub
