Populate Combo Box with DB Report Names

  • Thread starter Thread starter mj
  • Start date Start date
M

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!
 
mj said:
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!
Click to expand...

select name from msysobjects where type=-32764
 
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
 
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,
Click to expand...
rptActiveProductList, etc. Some
 
You're welcome.
Glad to help.

Jeff Conrad
Bend, Oregon

mj said:
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,
Click to expand...
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




.
Click to expand...
Click to expand...
 
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!
Click to expand...

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;
 
John said:
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!
Click to expand...


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;
Click to expand...

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")
 
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")
Click to expand...

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

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

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