Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Looping through controls in order?

    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

  2. #2
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  3. #3
    Join Date
    Oct 2011
    Posts
    2

    Solution: two for loops

    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

  4. #4
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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).

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    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.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •