I've created a routine that prints one or more reports as determined by whether or not a checkbox by the same name of the report has been checked.

It works great except for one thing. The order that the reports are printed is not the order in which the checkboxes appear in the form. Nor are they in tab index order. They appear to be in some random order likely the order in which they were added on the form.

Does anyone know of a way to loop through form controls in order? Tab Index would be best, cause they I could manage the order during form design.

Here is my routine which works as is, but prints the reports in random checkbox order. Thanks.


Private Sub btn_print_Click()

Dim strName As String
Dim rpt As Report
Dim ctl As Control
Dim chk As CheckBox

For Each ctl In Me.Controls

If TypeOf ctl Is CheckBox Then

Set chk = ctl

If chk.Value = True Then

strName = chk.Name

DoCmd.OpenReport strName, AcView.acViewPreview, , "Plan_District between " & Me.Begin_District & " and " & Me.End_District, acHidden
Set rptReport = Reports(strName)

If rptReport.Printer.Orientation = acPRORLandscape Then
rptReport.Printer.Duplex = acPRDPVertical
Else
rptReport.Printer.Duplex = acPRDPHorizontal
End If

DoCmd.SelectObject acReport, strName, False
DoCmd.PrintOut acPrintAll
DoCmd.Close acReport, strName, acSaveNo

End If
End If
Next

End Sub

I suspect what you would want to do is loop through your controls as per your example and add them to a sortable collection along with their TabIndex (to use as the sorting value).

You then sort the collection and loop through again to generate the reports.

Will try and whip up an example but I don't do much VB so it may take a little time.

I was hoping for a single for loop with an ordered by clause, but my local developers say that I need to upgrade to C for that kind of feature. But I did come up with a solution almost exactly as Rokslide suggested. Basically, I built an array placing each report name (same as the checkbox name) in the position of the TabIndex. Then I can walk through the array and print the reports in order, much to my client's delight. Here is my solution: (p.s. my formatting is better than shown )


Dim ctl As Control
Dim chk As CheckBox
Dim iControlIndex As Integer
Dim strTag(100) As String
Dim strReportName(100) As String
Dim rptReport As Report

'Build an array of checkbox/report names in the order of the checkbox's tab index
iControlIndex = 0
For Each ctl In Me.Controls
iControlIndex = iControlIndex + 1
If TypeOf ctl Is CheckBox Then
Set chk = ctl
If chk.Value = True Then
strReportName(chk.TabIndex) = ctl.Name
strTag(chk.TabIndex) = chk.Tag
End If
End If
Next

'Print reports in check box tab index order using the array set above
For idx = 0 To iControlIndex

If strReportName(idx) > "" Then

DoCmd.OpenReport strReportName(idx), AcView.acViewPreview, , strTag(idx) & " between " & Me.Begin_District & " and " & Me.End_District, acHidden

Set rptReport = Reports(strReportName(idx))
If rptReport.Printer.Orientation = acPRORLandscape Then
rptReport.Printer.Duplex = acPRDPVertical
Else
rptReport.Printer.Duplex = acPRDPHorizontal
End If

DoCmd.SelectObject acReport, strReportName(idx), False
DoCmd.PrintOut acPrintAll
DoCmd.Close acReport, strReportName(idx), acSaveNo
End If
Next

Nice solution but....

How many controls do you actually have on the form? Is it the 100 that you have set your array limit to? Will it ever grow beyond this or have you built in a lot of fat. If there is a lot of fat you will be looping for no reason (admittedly you are not actually doing a lot when there is no entry there).

I'd write a common event handler for the checkboxes that would register or remove themselves from a common collection/container (I'd lean towards a string array). When the time comes, you can then loop through the collection and print your reports. How you would allow an arbitrary ordinal position for each checkbox depends on what kind of object you use to store the "enabled" reports. I'm spoiled with .NET so I'm not sure of the least painful type of object that would allow you to sort arbitrarily in VBA. You might have to go old school linked list.

Nice idea, very simple,.... but how would you handle the ordering....

You would still need to register something like the TabIndex against the report entry so you could generate them in order.

I haven't written any "serious" vba in a long time, I don't remember what all is available as far as collections go. Shouldn't be too bad to bubble sort a plain old vanilla two dimensional array in any language, I'm guessing this isn't an ultra high performance environment so if it takes a second it's not a big deal.

If it is REALLY horrendous to do any other way, you could even keep a recordset laying around.

Lots of ways to skin that particular cat...