Prinitng Invoices

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Hi

I have a report that prints 6 invoices for each student in my table. One
field in the table has the first payment date. I want the other 5 payment
dates to be increased by one month each.

E.g. the payment date on the first invoice is January, on the second invoice
it's February, on the third it's March... etc. Each student may have a
different payment date, so the date routine has to be automatic and not
specified.
How can I do this automatically when the report is compiled?

For more details see another post further down the list with the same
subject title.

Thanks for any advice

Jeff
 
If you are using Access 2002 or greater, the open report method has a
parameter called "OpenArgs" which can be used to pass a value into a report.

If you are using Access 2000 or less, I think the best way to pass a
parameter to a report is to use a 1 record table, called something like ,
"tlbReportArgs" . Create a field in the table called "PaymentDate" and
create a single record and set its value to a random date.

Next, you'll need two functions to read and write to the table. Put these
two functions in one of your modules. Make sure the ADO Objects Library
reference is checked in your Tools/References.

'*******************************************************
Public Function GetPaymentDate() As Date ' returns the payment date
Dim dbs As ADODB.Connection, rst As ADODB.Recordset, dtePaymentDate As Date

dtePaymentDate = Date
Set dbs = CurrentProject.Connection
Set rst = New Recordset
rst.Open "tlbReportArgs", dbs, adOpenStatic, adLockOptimistic
If IsDate(rst!PaymentDate) Then dtePaymentDate = rst!PaymentDate
rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing
GetPaymentDate = Format$(dtePaymentDate, "mm/dd/yyyy")
End Function

Public Function SetPaymentDate(dtePaymentDate As Date)' sets the payment
date
Dim dbs As ADODB.Connection, rst As ADODB.Recordset

Set dbs = CurrentProject.Connection
Set rst = New Recordset
rst.Open LastValueTableName, dbs, adOpenDynamic, adLockOptimistic
rst!PaymentDate = dtePaymentDate
rst.Update
rst.Close
dbs.Close
Set dbs = Nothing
Set rst = Nothing

End Function

Function NextMonth(Optional dteDate As Date) As Date
Dim lngMonth As Long, lngDay As Long, lngYear As Long
' This function calculates the day of the next month, given a date.
' If no date is passed in, the function uses the current date.

If CLng(dteDate) = 0 Then
dteDate = Date
End If

lngMonth = Month(dteDate)
lngYear = Year(dteDate)
lngDay = Day(dteDate)

If lngMonth = 12 Then
lngMonth = 1
lngYear = lngYear + 1
Else
lngMonth = lngMonth + 1
End If

NextMonth = DateSerial(lngYear, lngMonth, lngDay)
End Function
'*******************************************

In your report, set the Data Source for the Payment Date field to
"=GetPaymentDate()" without the quotes.

Now all you have to do it call the function SetPaymentDate before printing
the report. Declare a variable called something like "dteFirstPaymentDate"

dteFirstPaymentDate = Me.PaymentDate' this is the field on the form , if
you have one
FOR I = 1 TO 5
DoCmd.OpenReport "ReportName", acViewNormal
dteFirstPaymentDate = NextMonth(dteFirstPaymentDate )
NEXT I
 
Back
Top