Sort AllReports output by Name

[Guest]

I'm using Access2000/VBA to create a collection of Reports for printing. I'm using a For/Next loop to step through AllReports and if the report Name begins with the right characters (i.e. "TR HP"), print it. The problem is, the reports need to be printed in Name sort order (i.e. "TR HP-4-01","TR HP-4-02", etc). Just looping through AllReports prints the collection in Index order. I'm familiar with arrays and Recordsets, but haven't actually used them much.

Any ideas on how to set this up

Current code

Dim obj As AccessObject, dbs As Objec
Set dbs = Application.CurrentProjec

For Each obj In dbs.AllReport
If Left(obj.Name, 5) = "TR HP" And Right(obj.Name, 6) <> "counts" The
DoCmd.OpenReport obj.Nam
End I
Next ob
Set dbs = Nothin

Many thanks
Carter

 

[Joe Fallon]

Set up a loop prior to running the report and dump the selected values into
a temp table.
Then sort the temp table in a query.
Loop over the query to run the reports.
--
Joe Fallon
Access MVP

I'm using Access2000/VBA to create a collection of Reports for printing.

I'm using a For/Next loop to step through AllReports and if the report Name
begins with the right characters (i.e. "TR HP"), print it. The problem is,
the reports need to be printed in Name sort order (i.e. "TR HP-4-01","TR
HP-4-02", etc). Just looping through AllReports prints the collection in
Index order. I'm familiar with arrays and Recordsets, but haven't actually
used them much..

 

[Ruskin]

You could try something like;

Dim myRecords As DAO.Recordset
Dim mySQL As String

mySQL = "SELECT * FROM MSysObjects WHERE " & _
"(([MSysObjects].[Type])=-32764) " & _
"ORDER BY [MSysObjects].[Name];"
Set myRecords = CurrentDb().OpenRecordset(mySQL)

myRecords.MoveFirst
Do Until myRecords.EOF
MsgBox myRecords![Name]
myRecords.MoveNext
Loop
myRecords.Close
Set myRecords = Nothing

I'm using Access2000/VBA to create a collection of Reports for printing.

I'm using a For/Next loop to step through AllReports and if the report Name
begins with the right characters (i.e. "TR HP"), print it. The problem is,
the reports need to be printed in Name sort order (i.e. "TR HP-4-01","TR
HP-4-02", etc). Just looping through AllReports prints the collection in
Index order. I'm familiar with arrays and Recordsets, but haven't actually
used them much..