Passing values to On Format Event of Detail Section for Reports

[tman24m]

Hi,

I'm trying to automate report printing. I've got everything working up to the point of printing reports. I've got a recordset that returns approximately 80-90 records every day and for each recordset, I'd like to print a report.

Here's what I've got so far :



Sub InProcessPacketPrint()

Dim strSQL As String, rst As Recordset
Dim HIN As String, INVOICE As String, Stage As String, ModelID As String
Dim stDocName As String, stDocName1 As String, stDocName2 As String

strSQL = "SELECT qryGridDatesWithOrderNumbers.chrCompanyID, qryGridDatesWithOrderNumbers.chrModelID, qryGridDatesWithOrderNumbers.chrHullID, qryGridDatesWithOrderNumbers.chrManufactureMonth, qryGridDatesWithOrderNumbers.chrManufactureYear, qryGridDatesWithOrderNumbers.chrModelYear, qryGridDatesWithOrderNumbers.chrStage, qryGridDatesWithOrderNumbers.datReleaseDate, qryGridDatesWithOrderNumbers.bitPacketPrinted, qryGridDatesWithOrderNumbers.Order "
strSQL = strSQL & "FROM qryGridDatesWithOrderNumbers "
strSQL = strSQL & "WHERE (((qryGridDatesWithOrderNumbers.datReleaseDate)=Date()+1) AND ((qryGridDatesWithOrderNumbers.bitPacketPrinted)=No)) AND ((qryGridDatesWithOrderNumbers.chrModelID)=""DE"&quot "
strSQL = strSQL & "ORDER BY qryGridDatesWithOrderNumbers.chrStage;"

Set rst = CurrentDb.OpenRecordset(strSQL)

While Not rst.EOF
HIN = rst!chrCompanyID & rst!chrModelID & rst!chrHullID & rst!chrManufactureMonth & rst!chrManufactureYear & rst!chrModelYear
INVOICE = rst!Order
Stage = rst!chrStage
ModelID = rst!chrModelID

Select Case Stage
Case Is = "LMH"
stDocName = "rptHullGel"
stDocName2 = "rptHullLamQualityAudit"
DoCmd.OpenReport stDocName, acNormal
DoCmd.OpenReport stDocName2, acNormal
Case Is = "LMD"
stDocName = "rptDeckGel"
stDocName2 = "rptDeckLamQualityAudit"
DoCmd.OpenReport stDocName, acNormal
DoCmd.OpenReport stDocName2, acNormal
Case Is = "ASY"
stDocName = "rptLostTime"
DoCmd.OpenReport stDocName, acNormal
stDocName2 = "rptFunctionTest"
DoCmd.OpenReport stDocName2, acNormal
End Select
stDocName1 = "rptJobDutySheet"
DoCmd.OpenReport stDocName1, acNormal
'MsgBox "the hin number is " & HIN & " and the inoice number is " & INVOICE
rst.MoveNext
Wend

End Sub


So now what I'd like to do is pass the values of HIN, INVOICE, Stage, and ModelID to some reports and their subreports. Please help and thanks.

 

[thekon]

You dont say what is the source of your reports, but you can put this (or something like this) as recordsource

SELECT [chrCompanyID] & [chrModelID] & [chrHullID] & [chrManufactureMonth] & [chrManufactureYear] & [chrModelYear] AS HIN, chrStage AS stage, chrModelID AS ModelID, Order AS INVOICE FROM qryGridDatesWithOrderNumbers WHERE (((datReleaseDate)=Date()+1) AND ((bitPacketPrinted)=No)) AND ((chrModelID)='DE') ORDER BY chrStage;