delete ~tmp.... from dropdown list

Question

Hi

I have a dropdown list of Reports (unbound) - the underlying query(?) came built in with the Events template in Access 2007

It is potentially very useful but top of the list is something called "~TMPCLP445791" which when clicked asks for date parameters and then opens one of the Reports (Current Month's Events)  ... which is, in fact, also listed further down (and doesn't need parameters)! Basically, how do I delete the ~TMP... report from the list? 

I checked the Properties and it comes up with the following:

SELECT MSysObjects.Name

FROM MSysObjects

WHERE (((MSysObjects.Name) Not Like "*Subreport*") AND ((MSysObjects.Type)=-32764))

ORDER BY MSysObjects.Name;

Where do I find the MSysObjects and (safely!) delete the report?

Many thanks.

Answer 1

KPT07,

MSysObjects is a system table, which you can see by checking the 'Show System Objects' box, under the Navigation Options dialog from the Navigation Pane.

However, you will not be able to delete anything from this table.

I don't know whether there is a better way to do this.  But I handle these odd ~TMP objects, when they arise, by creating a new database file, and then import all objects from the existing one.  Followed by a Compact/Repair.  The ~TMP report will not be included.

After doing this, you would also need to check any References in your VBA, and re-do any Current Database settings under Access Options.

---

Steve Schapel, Microsoft Access MVP

Answer 2

It may not be necessary - or safe, or easy - to *delete* the ~TMP records from the MSysObjects table; try compacting as suggested, but they may reappear.

What you can do is suppress them from appearing in the dropdown by using an additional criterion:

SELECT MSysObjects.Name

FROM MSysObjects

WHERE (((MSysObjects.Name) Not Like "*Subreport*")  AND (MSysObjects.Name Not Like "~TMP*") AND ((MSysObjects.Type)=-32764))

ORDER BY MSysObjects.Name;

---

John W. Vinson/MVP

Answer 3

Thanks Steve ... maybe one day!!

Answer 4

KPT07,

The References problem almost certainly will not apply to you.  It would only apply if you had written VBA code in your application that required non-default References to be set.  If you had done so, you would know about it. :)

---

Steve Schapel, Microsoft Access MVP

Answer 5

Many thanks - this worked fine... I'm not sure what you mean by the "References in your VBA" (I'm pretty new to Access) but any problems will probably come to light as I work on it!

Thanks again