Looping through Combo Box to Print Reports

[apank]

I have a report that opens based on the where the office code on the form matches the office code on the report. that works fine.

I have written code that sets the combo box value opens the report (acnormal), then closes report, then sets the combo box value again, opens the report and then close the report.....on and on.

The combo box grows monthly and instead of having to keep updated the code with new office codes, I would like the database to loop through each value of the combo box, print the repot, and then go on to the next one.

Here is the code I am using.

Dim val As Variant
For Each val In Me.ctrlBranchCode
 
DoCmd.OpenReport "rptBranchProductionWeekMtdYtd", acNormal, , "[Branch Code] = Forms!frmWeeklyMonthlyReporting!ctrlBranchCode"
DoCmd.Close acReport, "rptBranchProductionWeekMtdYtd"

I am gettting an error that says "Object does not support this property or method".

Any help would be greatly appreciated.

A

[ADezii]

@apank
Here is code that will allow you to return the data in the 'Bound' Column of a Combo Box for each specified Row:

Dim intCounter As Integer
Dim cboCode As ComboBox
 
Set cboCode = Me![ctrlBranchCode]
 
For intCounter = 0 To cboCode.ListCount - 1
  Debug.Print cboCode.ItemData(intCounter)
Next

[apank]

@ADezii

The procedure you described worked but I cannot get it to update the combo box, then print the report based on the combo, then close the report and print the next one. I am sure the answer is close but I can't seem to find it.

As always your help is much appreciated.

A

[ADezii]

@apank

1. How are you Updating the Combo Box?
2. At what specific point are you Updating the Combo Box?

[apank]

@ADezii
The combo box has a list. I would like the procedure to go through the list, choose each office code listed and print the report(The report prints based WHERE office code = the combo box). I can do it if I hard code the office codes in the procedure and do a set value, but would like it to loop through the combo box instead. That way if new offices are added the report will print automatically.

Thanks

[ADezii]

@apank
If the Row Source for the Combo Box is created correctly, what you request 'will' be done automatically.

[apank]

The series of events should go as follows.
1) Have the report print based on the first branch code in the combo box
2) Close the report
3) Change the branch code in the combo box to the next branch code
4) Print the Report
5) Repeat until the report has been printed for all the branch codes listed.

[ADezii]

@apank
Try:

Dim intCounter As Integer
Dim cboCode As ComboBox
 
Set cboCode = Me![ctrlBranchCode]
 
'If [Branch Code] is Numeric
For intCounter = 0 To cboCode.ListCount - 1
  DoCmd.OpenReport "rptBranchProductionWeekMtdYtd", acNormal, , _
                   "[Branch Code] = " & cboCode.ItemData(intCounter)
  DoCmd.Close acReport, "rptBranchProductionWeekMtdYtd"
Next
 
'If [Branch Code] is a String
For intCounter = 0 To cboCode.ListCount - 1
  DoCmd.OpenReport "rptBranchProductionWeekMtdYtd", acNormal, , _
                   "[Branch Code] = '" & cboCode.ItemData(intCounter) & "'"
  DoCmd.Close acReport, "rptBranchProductionWeekMtdYtd"
Next

[apank]

Thank you very much for this Adezzi. Worked great.

[ADezii]

@apank
You are quite welcome, apank.