Auto rotate through combobox

[renenger]

I have a combobox with the list of all of our installers. I have a report setup to display statistics for whichever installer is selected. I would like to have a Print All selection that will rotate through each person in the list and print the report automatically.

I know I can do this by hard-coding the box in VB but I would rather it be dynamic so I don't have to worry when we gain or lose new installers.

I haven't been able to find a solution through the knowledge base.

Any ideas???

 

[Moniker]

Sub CycleThroughCbo()

    Dim ctr As Integer

    With Me.YourComboBoxName
        For ctr = 0 To .ListCount - 1
            *** Run your report on .ItemData(ctr)
        Next
    End With

End Sub

That's pseudo-coded (may not work), but that's the gist of it. If the combobox has multiple columns and your data is stored in something besides the bound column, you'll need to use a Column() function in there as well. I don't want to write it for you, but you're doing something simple here: loop through a combobox, grabbing each value, and perform some action on it.

A simpler way, of course, would be to just loop through the table/query the combobox is based on, assuming it's structured that way.

 

[renenger]

The combobox is based off of this:

SELECT [tblInstallers].[Installer] FROM tblInstallers WHERE [tblInstallers].[Location]='NV Field' ORDER BY [tblInstallers].[Installer];


How could I loop it through the table/query?

 

[boblarson]

Instead of going through all of that hassle, why not do this - First, take off the criteria from the query underlying the report so it shows all. Then, in the code that opens the report do this:

1. Have a checkbox that says Print ALL

2. In the after update event of the checkbox put:

    Me.YourComboBoxNameHere.Enabled = Not Me.YourCheckBoxNameHere

3. Then in the code for the open report button, do this:

    If Me.YourCheckBoxNameHere Then
       DoCmd.OpenReport "YourReportNameHere", acViewPreview
    Else
       DoCmd.OpenReport "YourReportNameHere", acViewPreview, , "[Installer]=" & Me.YourComboBoxNameHere
    End If

 

[renenger]

Thanks Bob. But I think may be I was clear in what I was looking to do. I want to print a separate report for each installer in the combobox. I want the user to be able to specify one installer and print the report or just choose the Print All and have it loop through each installer and print a separate report for each one. That way the user doesn't have to select one and print, then select the next one and print.

Does that make sense? I can hard code this with the installer's names but I would rather not do that.

 

[Moniker]

Sub cmdPrintAll_Click ()

    Dim rsInstallers As ADODB.Recordset

    Set rsInstallers = New ADODB.Recordset
    rsInstallers.Open "SELECT Installer FROM tblInstallers WHERE Location='NV Field' ORDER BY Installer;", CurrentProject.Connection, adOpenKeyset, adLockReadOnly

    With rsInstallers
        .MoveFirst
        While Not(.EOF)
            DoCmd.OpenReport "YourReportNameHere", acViewPreview, , "[Installer]='" & .Fields("Installer") & "'"
            DoCmd.PrintOut acPrintAll
            DoCmd.Close acReport, "YourReportNameHere"
            .MoveNext
        Wend
    End With

    rsInstallers.Close
    Set rsInstallers = Nothing

End Sub