Passing the contents of a table to a module

David Mack

Registered User.
Local time
Today, 13:20
Joined
Jan 4, 2000
Messages
53
I have a module which produces staistical data which is then fed to a detailed report. I currently use a form filter on which a user selects custom criteria for their reports. Our upper management likes to have a report which shows the statistics for every unit, which is a total of as many as 15 reports, without having to enter each unit's criteria one at a time on the form filter.
What I would like to do is have the the contents of the Unit table passed into my SQL statement in the module one record at a time until EOF with a statistical report produced for each unit.

Basically, how do I pass the contents of the Unit table to a loop in my module one record at a time?

Thank you,

Dave
 
Hi Dave,

a good solution is to use a recordset to loop through all your records in the unit table. Please find attached an example.

Hope this helps,
Judith


Sub PrintCriteria()

'Define all variables
Dim dbs As Database
Dim rst As Recordset
Dim strUnitID As String
Dim stDocName As String
Dim stLinkCriteria As String

Set dbs = CurrentDb()

'replace tblUnits with name of your Unit table
Set rst = dbs.OpenRecordset("tblUnits")

With rst
.MoveFirst

Do While Not .EOF

'replace UnitID by name of field in your table
strUnitID = !UnitID

stLinkCriteria = "[rptUnitID]=" & strUnitID

'replace frmUnitStatistic with name of your report
stDocName = "frmUnitStatistic"

DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria

.MoveNext
Loop

End With

'Close all Objects
rst.Close
dbs.Close

End Sub
 
Judith,

Just what I was looking for! Thank you very much for your response.

Dave
 
Judith,

Just what I was looking for! Thank you very much for your response.

Dave
 

Users who are viewing this thread

Back
Top Bottom