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

report listed in combo box

P: 68
Hi,
I have an Access 2007 database with a form called 'Report Menu'. I would like to create a combo box that shows all the reports in the report as a dropdown. I would also like to include on that form, a button that, will preview the selected report from the combo box, when clicked.
I will appreciate if anyone can help me out on this.
Thanks.
Oscar
Jun 25 '12 #1
Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,597
The following Code, placed in the Open() Event of your Form, will populate a Combo Box named cboReports with all the Reports in your Database. The rest is relatively easy to accomplish.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim obj As AccessObject
  3. Dim objApp As Object
  4. Dim strRpts As String
  5.  
  6. Me![cboReports].RowSourceType = "Value List"
  7.  
  8. Set dbs = Application.CurrentProject
  9.  
  10. If dbs.AllReports.Count = o Then Exit Sub
  11.  
  12. 'Search for ALL AccessObject objects in AllReports collection.
  13. For Each obj In dbs.AllReports
  14.   'Me![cboReports].AddItem obj.Name   'Alternative approach
  15.    strRpts = strRpts & obj.Name & ";"
  16. Next obj
  17.  
  18. 'Populate Combo Box with List of all Reports in the Database. 
  19. 'Remove Trailing ";", (Not  necessary if using AddItem)
  20.  Me![cboReports].RowSource = Left$(strRpts, Len(strRpts) - 1)
  21. End Sub
Jun 25 '12 #2

P: 68
@ADezii
Hi ADezii,
Thanks again. This is working perfectly well, but I still need your help on the button the previews the report.
Thanks
Jun 25 '12 #3

100+
P: 759
Under _Click event place one of the following line:

Expand|Select|Wrap|Line Numbers
  1. 'To preview:
  2. DoCmd.OpenReport "ReportName", acViewReport
  3.  
  4. 'To print:
  5. DoCmd.OpenReport ("ReportName")
  6.  
  7. 'To save as .rtf :
  8. DoCmd.OutputTo acOutputReport, "ReportName", "RichTextFormat(*.rtf)", , True
Jun 26 '12 #4

ADezii
Expert 5K+
P: 8,597
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me![cboReports]) Then Exit Sub
  2.  
  3. 'To Open the selected report in Preview Mode
  4. DoCmd.OpenReport Me![cboReports], acViewPreview
Jun 26 '12 #5

P: 68
@ADezii
Thanks again ADezii, I am very grateful.
Jun 27 '12 #6

P: 68
@Mihail
Thanks Mihail, but this is not what I wanted.
Cheers!
Jun 27 '12 #7

P: 68
@ADezii
Hi ADezii,
I just want to thank you for your brilliant answer to my question on populating my reports to a combo box on my form. It works very well.

I also have a problem with my dlookup. I have developed an entry form with a combo box it containing a list of school codes. I would like to populate other details from the school table to the fields on the form for data entry purpose. This is what I have so far but is not working:

Me.SName = DLookup("[SchoolName]", "tblSchools", "[cboSchoolCode]='" & Me.[cboSchoolCode] & "'")

Me.txtCounty = DLookup("[County]", "tblSchools", "[cboSchoolCode]='" & Me.[cboSchoolCode] & "'")

But this is not just working.
Thanks for your assistance.
Oscar
Jun 27 '12 #8

ADezii
Expert 5K+
P: 8,597
Assuming School Codes are String Values, your problem lies in the WHERE Clause of DLookup(). You need to reference the 'Name' of the [School Code] Field in tblSchools, and not the Name of the Combo Box. Some examples 'may' be:

Expand|Select|Wrap|Line Numbers
  1. Me.SName = DLookup("[SchoolName]", "tblSchools", "[SchoolCode]='" & Me.[cboSchoolCode] & "'")
  2.  
  3. Me.SName = DLookup("[SchoolName]", "tblSchools", "[School Code]='" & Me.[cboSchoolCode] & "'")
Jun 27 '12 #9

Post your reply

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