Displaying a list of reports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a user who is being *forced* to "upgrade" from filemaker to access.
Filemaker allows him to filter all his records and then choose to run a long
list of various reports on the selection - eg labels, letters, simple lists
etc.

Thanks to this forum I seem to have solved the running the report based on
the filtered records, but now I would like to be able to display a "dynamic"
list of available reports eg some sort of a drop down list or some such -
which gets the information from the reports window. I think I have once seen
this in an Access database - any suggestions?

Many thanks
 
I have a user who is being *forced* to "upgrade" from filemaker to access.
Filemaker allows him to filter all his records and then choose to run a long
list of various reports on the selection - eg labels, letters, simple lists
etc.

Thanks to this forum I seem to have solved the running the report based on
the filtered records, but now I would like to be able to display a "dynamic"
list of available reports eg some sort of a drop down list or some such -
which gets the information from the reports window. I think I have once seen
this in an Access database - any suggestions?

Many thanks
Click to expand...

As Rowsource of a list box or combo box:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Name;

Code the Combo or List box AfterUpdate event:
DoCmd.OpenReport Me!ComboName, acViewPreview
 
Thanks Fred, but I must be doing something wrong because none of my reports
appear in the combo box - I just get an empty box.

Could you explain a little more about this so I can try and find the reason
- (I tried looking up MSysObjects, but it doen't appear in any of the
searches) eg should I really just be using your code as is - or should I be
replacing eg .Name with anything

Thanks


fredg said:
I have a user who is being *forced* to "upgrade" from filemaker to access.
Filemaker allows him to filter all his records and then choose to run a long
list of various reports on the selection - eg labels, letters, simple lists
etc.

Thanks to this forum I seem to have solved the running the report based on
the filtered records, but now I would like to be able to display a "dynamic"
list of available reports eg some sort of a drop down list or some such -
which gets the information from the reports window. I think I have once seen
this in an Access database - any suggestions?

Many thanks
Click to expand...

As Rowsource of a list box or combo box:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Name;

Code the Combo or List box AfterUpdate event:
DoCmd.OpenReport Me!ComboName, acViewPreview
Click to expand...
 
Thanks Fred, but I must be doing something wrong because none of my reports
appear in the combo box - I just get an empty box.

Could you explain a little more about this so I can try and find the reason
- (I tried looking up MSysObjects, but it doen't appear in any of the
searches) eg should I really just be using your code as is - or should I be
replacing eg .Name with anything

Thanks

fredg said:
I have a user who is being *forced* to "upgrade" from filemaker to access.
Filemaker allows him to filter all his records and then choose to run a long
list of various reports on the selection - eg labels, letters, simple lists
etc.

Thanks to this forum I seem to have solved the running the report based on
the filtered records, but now I would like to be able to display a "dynamic"
list of available reports eg some sort of a drop down list or some such -
which gets the information from the reports window. I think I have once seen
this in an Access database - any suggestions?

Many thanks
Click to expand...

As Rowsource of a list box or combo box:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Name;

Code the Combo or List box AfterUpdate event:
DoCmd.OpenReport Me!ComboName, acViewPreview
Click to expand...
Click to expand...

Just use the code exactly as I've written it.
The MySysObjects is a hidden system table that Access uses.

Copy the code to the clipboard.
(Make sure you do not copy any of the >'s that precedes each line, if
there are any.)

Set the Combo Box RowSourceType property to Table/Query.
Then click on the RowSource property line, and click on the button
with the 3 dots that appears on that line.
When the Query grid opens click on the down arrow of the View tool
button.
Select SQL View.
Paste the code into the SQL window.

Click on the Bang tool button (!) to run the query.
You should see all of the reports there.
If all is well, save the query.
Then set the Combo Box Bound Column to 1.
Set the Combo Count to 1.
Set the Column Widths to 1.5"

Save the changes
The combo box should display all the reports.
 
Brilliant!!

one last question - is there anywhere that I can find out more about
MsysObjects - clearly you need to know the type of system object (-32764 or
whatever) in order to use it.

Again thanks

fredg said:
Thanks Fred, but I must be doing something wrong because none of my reports
appear in the combo box - I just get an empty box.

Could you explain a little more about this so I can try and find the reason
- (I tried looking up MSysObjects, but it doen't appear in any of the
searches) eg should I really just be using your code as is - or should I be
replacing eg .Name with anything

Thanks

fredg said:
On Thu, 4 Nov 2004 08:26:02 -0800, HelenJ wrote:

I have a user who is being *forced* to "upgrade" from filemaker to access.
Filemaker allows him to filter all his records and then choose to run a long
list of various reports on the selection - eg labels, letters, simple lists
etc.

Thanks to this forum I seem to have solved the running the report based on
the filtered records, but now I would like to be able to display a "dynamic"
list of available reports eg some sort of a drop down list or some such -
which gets the information from the reports window. I think I have once seen
this in an Access database - any suggestions?

Many thanks

As Rowsource of a list box or combo box:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=-32764))
ORDER BY MSysObjects.Name;

Code the Combo or List box AfterUpdate event:
DoCmd.OpenReport Me!ComboName, acViewPreview
Click to expand...
Click to expand...

Just use the code exactly as I've written it.
The MySysObjects is a hidden system table that Access uses.

Copy the code to the clipboard.
(Make sure you do not copy any of the >'s that precedes each line, if
there are any.)

Set the Combo Box RowSourceType property to Table/Query.
Then click on the RowSource property line, and click on the button
with the 3 dots that appears on that line.
When the Query grid opens click on the down arrow of the View tool
button.
Select SQL View.
Paste the code into the SQL window.

Click on the Bang tool button (!) to run the query.
You should see all of the reports there.
If all is well, save the query.
Then set the Combo Box Bound Column to 1.
Set the Combo Count to 1.
Set the Column Widths to 1.5"

Save the changes
The combo box should display all the reports.
Click to expand...
 
Brilliant!!

one last question - is there anywhere that I can find out more about
MsysObjects - clearly you need to know the type of system object (-32764 or
whatever) in order to use it.

Again thanks
Click to expand...
Click on Tools + Options + View tab.
Place a check mark in the Show System Objects box.

Display the database tables, and you will see the tables Access uses
for itself. Look.... but don't touch!!!!
When done, go back to options and remove the check mark.
 
Back
Top