Drop-down list that only shows relevant reports

[BrookieOU]

In my database I have different forms that are set up in lists such as
employees, projects, billing, etc. Currently, I have a drop-down box that
queries the MySysObject table and shows all the reports I have in the
database.

My question is...is there a way to limit the report box so that it will only
show relevant reports (i.e. project reports in the project list, employee
reports in the employee list)?

Thanks!

 

[John W. Vinson]

In my database I have different forms that are set up in lists such as
employees, projects, billing, etc. Currently, I have a drop-down box that
queries the MySysObject table and shows all the reports I have in the
database.

My question is...is there a way to limit the report box so that it will only
show relevant reports (i.e. project reports in the project list, employee
reports in the employee list)?

Thanks!

Probably not by using MSysObjects (to give its correct name), but you can
certainly create your own table. I'll often use a Listbox based on a table
containing the actual report name (rptOfficerActivity say) and a "human
meaningful" name like "Monthly Officer Activity Report"; the listbox will
display the latter but have the actual report name as its bound column. In the
listbox's AfterUpdate event you can have code to launch the selected report.

The listbox's rowsource can be changed to be more specific, say by having a
query which references the currently active form.

 

[golfinray]

Google using the where argument of the Docmd.openreport, something like this:
Private Sub combo2_Click()
Dim strWhere As String
Dim stDocument As String
stDocument = "06-07 active projects"
strWhere = "1=1 "
If Not IsNull(Me.Combo2) Then
strWhere = strWhere & " And [Area]=""" & _
Me.Combo2 & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere
DoCmd.Close acForm, "area form 1", acSaveNo

 

[BrookieOU]

John,

Thank you for the advice! I am going to try and see if I can figure it out
your way! I've never done a listbox before and I may have to get some help
on the AfterUpdate coding.

Thanks!
Brooke