Store Reference to Access Objects in a Field

[kd2017]

My database has a Jobs table. I have several reports to run on any one job, and some of these reports are actually slight variations on other reports. (I just copy and paste and make my tweaks)



I would like to add a field to my Jobs table to store a reference to a preferred report variation, sort of a foreign key that references an access object instead of a another table. How should I approach this?

Id like it to be dynamic such that in the future the user can copy/paste/tweak their own custom report. They could simply then find their custom report in a drop down field and select it as default for that particular job.

Finally, could this same concept be done with variations of forms?

[RuralGuy]

It really depends on how you plan to use these objects but storing Report and Form names is certainly possible.

[kd2017]

So I was poking around the hidden System Object tables, it looks like the information I'm after is stored in the MSysObjects table, with reports having a type -32764. Is there any reason I shouldn't utilize these tables? My Job table could have a foreign key to the MSysObjects table, couldn't it?

Of course I won't do any updating/appending in the system tables. (edit) Looks like I'm not able to manually edit the tables anyway, so no worries there I suppose.

[Micron]

Those tables will allow you to enumerated Access objects, but they won't identify which report is Joe's and which is Mary's.

They could simply then find their custom report in a drop down field and select it as default for that particular job.

I don't think they're suitable for what you're trying to do unless all you want is a combo list of reports, which won't be specific to their name. They will see everyone's reports.

[kd2017]

I'd store the report object's pk as a fk in the jobs table, right? Can I open a report or form with it's pk in vba? Of course I can just look up its name now..

[davegri]

Need more information on how you want this to work.

How many reports are involved? How do the custom reports differ? Do they have different display fields? Different data sources? Different grouping and sorts? Is each and every custom report a distinct report in the Navigation Pane? What is the lifetime of a custom report - one time use or permanent?
How would a user identify "his" custom reports in a list of reports? Do you have a report naming convention?

[kd2017]

So I'm thinking I'd just query the msysobjects table for object's with type -32764, assuming that's the type for all reports, and id further filter those results by searching for a prefix in the report's name, in this case any report name that starts with "SOV_". I'd have to find away to make sure the user knows to leave this identifying prefix in the report's name, maybe there's a better way to identify them?

Custom reports would usually differ in grouping and fields displayed. They could differ much more dramatically, I'm leaving that up to them. For my use right now I'm thinking I need about 5 different variations on this particular report. Lifetime is permanent. As for navigation pane, before it occurred to me to make this dynamic it was just going to be a button for each report variation, now I'm thinking I'd use a combobox to show them. The combo would be populated as described in the first paragraph.

[davegri]

Something like this? The reports are in a list box with a row source based on a query of MSysObjects table. You could filter this any way needed on a report name prefix.
I use this technique often and users like it.

[kd2017]

All right! That's what I needed to know, you identify reports by the type field? A report is always -32764?

[Micron]

here's how to identify all/most of the object types
http://access.mvps.org/access/queries/qry0002.htm

[davegri]

Here's the query (Named qReports):

SELECT MsysObjects.Name AS rptName
FROM MsysObjects
WHERE (((Left$([Name],1))<>'~') AND ((MsysObjects.Type)=-32764))
ORDER BY MsysObjects.Name;

And here's the rowsource for the listbox:

SELECT rptName from qReports ORDER BY rptName;