Populating List Box with Containers???

[mishbaker]

I need to populate a list box with documents in my Reports Container. Unfortunately I'm new to VBA and there is no option for this in the ListBox wizard. How would I go about doing this?

I have done some reading and I believe it would require VBA to call the names of the Reports containers then somehow filter out only the ones that pertain to that specific set of reports I want to list. Like, just the ones that are based on my Employee's table or just the ones that are based on my Equipment tables?

 

[shannonp]

You're right that you'd have to use VB to call names of report contained with your database, although I'm not sure how you would filter the list based on what each reports underlying table is but you could filter on the names of certain reports.

The following code has been put in a forms OPEN event but could easily be assigned to a button CLICK event. I've also created a List Box called "ReportList".

Private Sub Form_Open(Cancel As Integer)
Dim db As Database, rpt As Document, cnt As Container
Dim strRowSrc As String, RepName As String

Set db = CurrentDb
Set cnt = db.Containers!Reports

For Each rpt In cnt.Documents
        RepName = rpt.Name
        strRowSrc = strRowSrc & RepName & ";"
Next rpt

[ReportList].RowSource = strRowSrc
Set cnt = Nothing
Set db = Nothing

End Sub

Hope this helps...


Shann

 

[RoyVidar]

He he - no you don't;-)

to get all reports, use the rowsource of:

Select Name FROM msysObjects WHERE Type = -32764 order by Name

for your listbox control.

To select only those partaining to specific tables/forms or whatever, I'd append all those reports to another table, where you add another field for "report category" (add manually), so that you can either filter the reports, or show the "categories" in the listbox.

(but isn't this a forms question (forum702)?)

For some more "systable" thingies, have a look here thread702-839833

Roy-Vidar

 

[shannonp]

Humbled by the Roy-vinator...

Up until now thats how I've done it...guess whose changing their method!!!


Shann

 

[dhookom]

Shann,
Don't feel bad. Roy's method works very well but it is not a documented method like your coding solution.

I however, would never show a user my report names. They are generally something like "rptFacBudYr" or "srptEmpWrkHrs". Either solution mentioned would also display subreports which isn't good. Roy-Vidar mentions creating a table of reports. This is my preferred method. I have a table with fields for rptName, rptTitle, rptDescription, rptStatus, rptCategory,... This table is the row source for my lboReports.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[RoyVidar]

I thought that by putting in a smiley, my message wouldn't come accross as rude, if it did, I appologize! That was never the intention, It was just showing another method, that works on the versions of Access I work with currently (2000-2003), which also provides the possibility of sorting the list, if needed.

What dhookom presents, is my preference too, for the same reasons. I also sometimes include a child table with some extra information, for instance fields of the reports recordsource the users can play with (include or not in the report, filter...), different header/footer information, and other settings.

As for documentation, I'm not sure I agree 100%. It is documented as means of retreiving system information on Access 1.0 (see for instance ACC: How to Find If an Object Exists in a Database).

It's also included in at least one KB for later versions too Programmatically Populated Combo Box or List Box Is Not Sorted in the Order Expected - though with the following warning: "Although this method will work with Microsoft Access 2000, it is not a highly recommended method, as the structure of system tables may change within future versions of Access.". (The KB applies to 2000 and 2002)

Roy-Vidar

 

[dhookom]

I think Roy-Vidar and are on the same page. I keep a related tables with distinct control names and report/control junction.

My typical report selection form has
- a list box with report titles
- text, list, or combo boxes for criteria
- options for detail/summary & preview/print
- text box with report description
- command button to run the report
- user instructions

As a user selects a report, some controls will be enabled or disabled based on my Report/Control junction table.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[mishbaker]

Thanks for all your help guys. I ended up creating a table with the Report Names Desc, etc etc and making the listbox show the Descriptions to the user. On dblclick it print previews the report. Thanks again for the great ideas!