report listed in combo box

[Stoic]

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

[ADezii]

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.

Private Sub Form_Open(Cancel As Integer)
Dim obj As AccessObject
Dim objApp As Object
Dim strRpts As String
 
Me![cboReports].RowSourceType = "Value List"
 
Set dbs = Application.CurrentProject
 
If dbs.AllReports.Count = o Then Exit Sub
 
'Search for ALL AccessObject objects in AllReports collection.
For Each obj In dbs.AllReports
  'Me![cboReports].AddItem obj.Name   'Alternative approach
   strRpts = strRpts & obj.Name & ";"
Next obj
 
'Populate Combo Box with List of all Reports in the Database. 
'Remove Trailing ";", (Not  necessary if using AddItem)
 Me![cboReports].RowSource = Left$(strRpts, Len(strRpts) - 1)
End Sub

[Stoic]

@ADezii
Hi ADezii,
Thanks again. This is working perfectly well, but I still need your help on the button the previews the report.
Thanks

[Mihail]

Under _Click event place one of the following line:

'To preview:
DoCmd.OpenReport "ReportName", acViewReport
 
'To print:
DoCmd.OpenReport ("ReportName")
 
'To save as .rtf :
DoCmd.OutputTo acOutputReport, "ReportName", "RichTextFormat(*.rtf)", , True

[ADezii]

If IsNull(Me![cboReports]) Then Exit Sub
 
'To Open the selected report in Preview Mode
DoCmd.OpenReport Me![cboReports], acViewPreview

[Stoic]

@ADezii
Thanks again ADezii, I am very grateful.

[Stoic]

@Mihail
Thanks Mihail, but this is not what I wanted.
Cheers!

[Stoic]

@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

[ADezii]

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:

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