By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,967 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

Looping through Combo Box to Print Reports

P: 31
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.
Expand|Select|Wrap|Line Numbers
  1. Dim val As Variant
  2. For Each val In Me.ctrlBranchCode
  3.  
  4. DoCmd.OpenReport "rptBranchProductionWeekMtdYtd", acNormal, , "[Branch Code] = Forms!frmWeeklyMonthlyReporting!ctrlBranchCode"
  5. 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
May 20 '09 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,679
@apank
Here is code that will allow you to return the data in the 'Bound' Column of a Combo Box for each specified Row:
Expand|Select|Wrap|Line Numbers
  1. Dim intCounter As Integer
  2. Dim cboCode As ComboBox
  3.  
  4. Set cboCode = Me![ctrlBranchCode]
  5.  
  6. For intCounter = 0 To cboCode.ListCount - 1
  7.   Debug.Print cboCode.ItemData(intCounter)
  8. Next
May 21 '09 #2

P: 31
@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
May 21 '09 #3

ADezii
Expert 5K+
P: 8,679
@apank
  1. How are you Updating the Combo Box?
  2. At what specific point are you Updating the Combo Box?
May 21 '09 #4

P: 31
@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
May 22 '09 #5

ADezii
Expert 5K+
P: 8,679
@apank
If the Row Source for the Combo Box is created correctly, what you request 'will' be done automatically.
May 22 '09 #6

P: 31
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.
May 22 '09 #7

ADezii
Expert 5K+
P: 8,679
@apank
Try:
Expand|Select|Wrap|Line Numbers
  1. Dim intCounter As Integer
  2. Dim cboCode As ComboBox
  3.  
  4. Set cboCode = Me![ctrlBranchCode]
  5.  
  6. 'If [Branch Code] is Numeric
  7. For intCounter = 0 To cboCode.ListCount - 1
  8.   DoCmd.OpenReport "rptBranchProductionWeekMtdYtd", acNormal, , _
  9.                    "[Branch Code] = " & cboCode.ItemData(intCounter)
  10.   DoCmd.Close acReport, "rptBranchProductionWeekMtdYtd"
  11. Next
  12.  
  13. 'If [Branch Code] is a String
  14. For intCounter = 0 To cboCode.ListCount - 1
  15.   DoCmd.OpenReport "rptBranchProductionWeekMtdYtd", acNormal, , _
  16.                    "[Branch Code] = '" & cboCode.ItemData(intCounter) & "'"
  17.   DoCmd.Close acReport, "rptBranchProductionWeekMtdYtd"
  18. Next
May 22 '09 #8

P: 31
Thank you very much for this Adezzi. Worked great.
Jun 2 '09 #9

ADezii
Expert 5K+
P: 8,679
@apank
You are quite welcome, apank.
Jun 2 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.