Loop through values in a form combo box

[Bryan]

Hi - I am new to programming in access, although I have several years
programming experience on other platforms.

I have a form with an "Agent Name" combo box and a button that allows them
to run a report by agent. If the user leaves the "agent name" field blank, I
would like to loop through each name in the combo box, execute the report,
and send it to a printer.

For example, if my drop list has ten names and none is selected, I would
like to loop through all ten and run and print each report individually.

Does anyone have code and/or instructions in terms of how to go about
getting this done?

Thanks for your feedback!!!!

 

[Sprinks]

Hi, Bryan.

On Error Resume Next ' An error is triggered if user just deleted a
record
Dim ctl As Control
Dim strDoc As String ' Report Name
Dim intIndex As Integer ' Index for combo box items
Dim varItem As Variant ' Value of the Bound Column

Set ctl = Me!YourComboBox
strDoc = "YourReportName"
For intIndex = 0 To ctl.ListCount - 1
DoCmd.OpenReport strDoc, , , "YourPrimaryKey = " & ctl.ItemData(intIndex)
Next

Hope that helps.
Sprinks

 

[Bryan]

Hi Sprinks - Thanks for the input.

The only part that has me confused is the "primary key" reference and
passing the agent name back to the query. Do I have to modify the query or
th report properties to accept the agent name that is being calculated and
returned by the module code below?

 

[Sprinks]

Hi, Bryan.

I guess I've assumed facts not in evidence. I'd thought that your agent
name combo box *displayed* a name but actually *stored* a numeric code, it
being the primary key of the table specified in the combo box' Row Source.
This may or may not be the case. Simply replace YourPrimaryKey with whatever
field matches the Bound Column of the combo box.

The RecordSource of the report, be it a table or a query, needn't have any
reference for the agent; the code changes the Report's Filter property to
print the appropriate record.

I see, however, that I covered the case when there has been no selection
made, and not when the user has selected an Agent. Modify as follows:

On Error Resume Next ' An error is triggered if user just deleted a
record
Dim ctl As Control
Dim strDoc As String ' Report Name
Dim intIndex As Integer ' Index for combo box items
Dim varItem As Variant ' Value of the Bound Column

Set ctl = Me!YourComboBox
strDoc = "YourReportName"
If IsNull(ctl) Then
' Loop through all combo box rows; print report for each
For intIndex = 0 To ctl.ListCount - 1
DoCmd.OpenReport strDoc, , , "YourMatching Field = " &
ctl.ItemData(intIndex)
Next
Else
' Print the report only for the selected agent
DoCmd.OpenReport strDoc, , , "YourMatchingField = " & ctl.Value
End If

Hope that helps.
Sprinks