Last record in header of monthly report

[chiuchimuN]

I have monthly reports that need to be ordered by date in descending order showing each members purchase and payment activities from the first to the last of the month, printing one page per member. I've been able to do this. The boss wants the last record( mainly the balance ) in the top part of each page( which would be the header section of the report). Its really easy to get the last record in a textbox in the footer of the report since access has ran through all the records and knows the last record in each group, but I can't figure out a way to do this in the header.

Currently I've tried to keep a second table updated with the last records of each member and querying that. It works but its next to impossible to keep the two tables synced. There must be a simple way to know the values of a last record of a group before access starts to print out the whole page.

 

[dhookom]

I think the answer would depend on the table structure which we don't have a clue. I expect you could create a small subreport that displays the last record. Then add the subreport to the group header.

Duane
Hook'D on Access
MS Access MVP

 

[chiuchimuN]

The table(TB) is:
index
ID
Date
Oldbalance
Transaction
Amount
NewBalance


I just tried creating creating a function in the reports module that opens a Query and gets the last record for each ID but I get an error.

'In Reports Declare
dim DB as database
dim RS as recordset


'In Reports module
private function GetBal( ID, DateStart, DateEnd )
dim Str as string

str="select * from TB where ID=" & ID & " and date >=#"
str=str & DateStart & "# and date <=#" & DateEnd & "# "
str=str & "order by Date dsec, index desc;"

set DB=currentDB()
set RS=db.openrecordset(str)

getBal=rs("NewBalance")
end function


'In report group Header

Text1=GetBal([ID])


This seems like it should work but I get an error in the textbox when I run the report.

Am I not allowed to open a DOA recordset in a report?

 

[dhookom]

I'm not sure why you are creating a recordset. A subreport is basically a recordset with no coding.

Your function looks like it requires 3 arguments while your "=GetBal([ID])" has only 1. Your code should be in a standard module and explicitly declare the db as DAO.Database and rs as DAO.Recordset.



Duane
Hook'D on Access
MS Access MVP

 

[chiuchimuN]

Typo getbal([ID]) should read getbal([ID],8/15/09, 9/15/09)

DOA recordset calls can't be made in a report module? They have to be in a standard module?

I want a DOA solution over a subreport since I just need one textbox at the top with the balance. I've done everything else, that's the last piece.

 

[dhookom]

You can run functions in reports. I would try change the Private to Public.

Are you using hard-coded date values or fields or parameters? If you actually used
=getbal([ID],8/15/09, 9/15/09)
you should delimit the dates like:
=getbal([ID],#8/15/09#, #9/15/09#)

Your code is also less than ideal.

str="select NewBalance from TB where ID=" & ID & " and [date] >=#"
str=str & DateStart & "# and [date] <=#" & DateEnd & "# "
str=str & "ORDER BY [Date] desc, [index] desc"

Date is a reserved word and shouldn't be used as a field name. I expect the same is true of index. Your code has "dsec" rather than "Desc".



Duane
Hook'D on Access
MS Access MVP