array or table? for a loop

[mmohon]

I'm a newb at VB code so go easy on me...

I have a table, of [dept] numbers and [directory paths].
I created a report that pulls those variables and outputs a snapshot
file.

How best, do I Loop through all the departments, and it spit out all
of my reports?

 

[Stefan Hoffmann]

hi,

I have a table, of [dept] numbers and [directory paths].
I created a report that pulls those variables and outputs a snapshot
file.

Do you like to generate a snapshot for each department?

How best, do I Loop through all the departments, and it spit out all
of my reports?

Try this (untested):

Dim rs As DAO.Recordset
Dim ReportName As String
Dim WhereCondition As String

Set rs = CurrentDb.OpenRecordset("yourTable")

If Not rs.Bof And Not rs.Eof Then
ReportName = "yourReport"
Do While Not rs.Eof
WhereCondition = "[dept] = '" & rs![dept] & "'"
DoCmd.OpenReport ReportName, , , WhereCondition
DoCmd.OutputTo acOutputReport, ReportName, _
acFormatSNP, rs![directorypath]
DoCmd.Close acReport, ReportName
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing


Your report needs the field [dept] in its datasource and must show all
records.

mfG
--> stefan <--

 

[mmohon]

hi,

I have a table, of [dept] numbers and [directory paths].
I created a report that pulls those variables and outputs a snapshot
file.

Do you like to generate a snapshot for each department?

How best, do I Loop through all the departments, and it spit out all
of my reports?

Try this (untested):

Dim rs As DAO.Recordset
Dim ReportName As String
Dim WhereCondition As String

Set rs = CurrentDb.OpenRecordset("yourTable")

If Not rs.Bof And Not rs.Eof Then
ReportName = "yourReport"
Do While Not rs.Eof
WhereCondition = "[dept] = '" & rs![dept] & "'"
DoCmd.OpenReport ReportName, , , WhereCondition
DoCmd.OutputTo acOutputReport, ReportName, _
acFormatSNP, rs![directorypath]
DoCmd.Close acReport, ReportName
rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing

Your report needs the field [dept] in its datasource and must show all
records.

mfG
--> stefan <--

Actually My table looks like this:

Dept groups: Paths:
'4124,4325,7150' "C:\ThisDeptGroupfolder"
'5632,2314,9052' "C:\ThisDeptGroupfolder2
so on...

My current script, changes the WHERE statement in my Query, then spits
out the report.

I have it setup so that every line of my table is a report I need
ouput, I think this will work though. I will give it a try once I
get in the office

Thanks,

 

[Stefan Hoffmann]

hi,

Dept groups: Paths:
'4124,4325,7150' "C:\ThisDeptGroupfolder"
'5632,2314,9052' "C:\ThisDeptGroupfolder2
so on...

You should normalize your table, e.g.:

Dept: Id, Dept
DeptGroup: Id, Path
Depth_DepthGroup: Id, Dept_ID, DepthGroup_ID

I have it setup so that every line of my table is a report I need
ouput, I think this will work though. I will give it a try once I
get in the office

Ok.

mfG
--> stefan <--