Populate Combo Box with DB Report Names

[mj]

Hi. I'm trying to create a combo box on my form that is
populated with the names of the reports in my database.
The database is named "Administration". Any suggestions
would be fantastic. Thanks!

 

[Bas Cost Budde]

Hi. I'm trying to create a combo box on my form that is
populated with the names of the reports in my database.
The database is named "Administration". Any suggestions
would be fantastic. Thanks!

select name from msysobjects where type=-32764

 

[Jeff Conrad]

Hi,

A couple of options for you.

1. Set the Row Source of a new query to this:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=-32764))
ORDER BY MsysObjects.Name;

That will list all the report names in the database.
However, your names may be a little hard to understand depending upon how
you name them. For example, rptReportGroups, rptActiveProductList, etc. Some
users may get confused. Also, you may not want to list ALL reports in the
combo box.

2. Create a table that lists all your report names with "meaningful" names
to the users. Base your combo box on that table. I think MVP Sandra Daigle
has a sample file demonstrating that here:

http://www.daiglenet.com/MSAccess.htm

Look for ReportDialog download.

Hope that helps,
Jeff Conrad
Bend, Oregon

 

[mj]

Very helpful. Thanks!!

-----Original Message-----
Hi,

A couple of options for you.

1. Set the Row Source of a new query to this:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=- 32764))
ORDER BY MsysObjects.Name;

That will list all the report names in the database.
However, your names may be a little hard to understand depending upon how
you name them. For example, rptReportGroups,

rptActiveProductList, etc. Some

 

[Jeff Conrad]

You're welcome.
Glad to help.

Jeff Conrad
Bend, Oregon

Very helpful. Thanks!!

-----Original Message-----
Hi,

A couple of options for you.

1. Set the Row Source of a new query to this:
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((MsysObjects.Type)=- 32764))
ORDER BY MsysObjects.Name;

That will list all the report names in the database.
However, your names may be a little hard to understand depending upon how
you name them. For example, rptReportGroups,

rptActiveProductList, etc. Some

users may get confused. Also, you may not want to list ALL reports in the
combo box.

2. Create a table that lists all your report names with "meaningful" names
to the users. Base your combo box on that table. I think MVP Sandra Daigle
has a sample file demonstrating that here:

http://www.daiglenet.com/MSAccess.htm

Look for ReportDialog download.

Hope that helps,
Jeff Conrad
Bend, Oregon




.

 

[John Vinson]

Hi. I'm trying to create a combo box on my form that is
populated with the names of the reports in my database.
The database is named "Administration". Any suggestions
would be fantastic. Thanks!

The safest way would be to create your own table of report names,
perhaps with two fields - the first being the systematic naming
convention (rptMonthlySummary) and the second being a human-friendly
name (Monthly Summary, enter a date in the Criteria textbox).

If you want to use the existing report names, you can use the
undocumented (it's not guaranteed to work across versions) hidden
MSysObjects table:

SELECT [Name] FROM [MSysObjects] WHERE [Type] = -32764 ORDER BY Name;

 

[Bas Cost Budde]

Hi. I'm trying to create a combo box on my form that is
populated with the names of the reports in my database.
The database is named "Administration". Any suggestions
would be fantastic. Thanks!

The safest way would be to create your own table of report names,
perhaps with two fields - the first being the systematic naming
convention (rptMonthlySummary) and the second being a human-friendly
name (Monthly Summary, enter a date in the Criteria textbox).

If you want to use the existing report names, you can use the
undocumented (it's not guaranteed to work across versions) hidden
MSysObjects table:

SELECT [Name] FROM [MSysObjects] WHERE [Type] = -32764 ORDER BY Name;

This is documented:

Sub b()
Dim co As Container
Dim dc As Document
Set co = DBEngine(0)(0).Containers("Reports")
For Each dc In co.Documents
'do something
Next
End Sub

but very procedurally. You have to fiddle quite a bit to make a combobox
out of it (concatenate names, set combobox.rowsourcetype to "value list")

 

[John Vinson]

This is documented:

Sub b()
Dim co As Container
Dim dc As Document
Set co = DBEngine(0)(0).Containers("Reports")
For Each dc In co.Documents
'do something
Next
End Sub

but very procedurally. You have to fiddle quite a bit to make a combobox
out of it (concatenate names, set combobox.rowsourcetype to "value list")

Eeep. Yes, that would work (with, as you say, a fair bit of hassle). I
suppose you could even use a "callback function" rowsourcetype...!

 

[Bas Cost Budde]

Eeep. Yes, that would work (with, as you say, a fair bit of hassle). I
suppose you could even use a "callback function" rowsourcetype...!

Indeed. Now when was that useful again? I tried once, but can't remember.

 

[John Vinson]

Indeed. Now when was that useful again? I tried once, but can't remember.

Other than working through the ADH example once just to figure out how
it works, I've never had occasion to actually use it.