Need to delete hidden ~TMPCLPMacro that causes my form to no longer input data into my database.

[G3K0]

Hi,

I am having an issue with an access created macro (~TMPCLPMacro) that causes my form to no longer input data into my database. I know it is this macro because it is being blocked when I first open my database. Without selecting Enable Content, I can go about my day using the form to input data. As soon as I Enable Content, the link between my form and my database is lost.

I have been trying to find where this macro is to delete it, but it seems to be hidden. In the picture below, I am showing that the macro shows up in the Contents tab of the Database Properties dialog box. I have also tried importing everything but the macro into a new blank database, but the macro does not even show up as an importable object. This macro just appears when I import my tables, queries, and forms.

Is there a way to delete this macro that creates itself?



Thanks for the help!

[NTC]

You are in a very strange place. When this file is closed, using File Explorer - right click on it and go to Properties - and see if the window that opens displays an 'Unblock' option to the lower right - if so, check that.

Then look up the method online or in your favorite textbook to correctly establish the Trusted location needed for an Access application - and create a trusted location for the folder that holds this file, the folder that holds the back end table file (if it is split), and any folders holding files that this database might link to such as excel files if that is applicable.

After you've done that - close and reopen your file and check behavior.

[John_G]

Did you try making hidden object visible?

File - Options - Current Database

Click Navigation Options, then select both Show Hidden Objects and Show system Objects

That should make the macro show in the Navigation Pane Macros group, and you could try deleting it there.

[G3K0]

Thanks for the feedback NTC! Admittedly, I so far have only been working in access for a couple weeks and everything I know I used youtube for, so I have no formal training.

When you say "very strange place" the location of this dialog box is under any access database under File, and to the far right you see an image of the database you are working on with a link below it saying 'view and edit database properties'. This is where I get the Properties dialog box. Although after typing all of this, I realize you may be referring to my situation. In which case yes, I would agree, probably standard noob mistakes that got me into this mess.

After following the first instructions I was not able to locate an 'Unblock' option, as for a method to establish the Trusted location, is this a "rule of thumb" that should have been established upon first creating the database? I think I will have to research the importance of creating a trusted location to fully understand the reasoning.

[G3K0]

John_G

I followed your instructions to show the hidden objects and system objects. Once that was applied, I see there are several MSys tables that have appeared. I still see no macros. I don't believe I have any filters on in the pane considering All Access Objects is selected in the drop down. Any other places this macro could be hiding?

[aytee111]

Try recreating the form - copy and paste all the controls on to a blank form, copy and paste all the code. Remove all embedded macros, use VBA instead. If you know which control it is that is causing the problem then don't copy that one over. If all else fails, recreate the form from scratch. And don't use embedded macros, they are notorious for causing issues.

[Bulzie]

Try this(have good backup of DB first), found from other posts:


Public Sub DelTempTable()
Dim t As Dao.Tabledef
Dim db As Dao.Database
Set db=CurrentDb

For Each t In db.TableDefs
If Instr(t.Name,"~") > 0 Then db.TableDefs.Delete t.Name
Next
set db=nothing
End Sub

[G3K0]

aytee111

Thanks for that info on embedded macros. I will stick to VBA. I have been working on importing tables, forms, and queries to find the culprit.

Bulzie

Thanks for the code, I have already resorted to restarting a particular form.

Everyone,

At this time I think I have found my problem child. I had a form with a combo box that was using a query to create a cascading combo box. I then created a navigation from that included said form and I had to modify the query at one point to make the cascading combo boxes work. After leaving the project for a couple weeks and coming back to it, I found I could not use the Navigation form, or the standalone form to insert data in the database. I broke some connection somehow, somewhere which causes that devious macro to exist in my database. My resolution to keep me going is to recreate my problem form and recreate my query in a new database and not screw up the connection by creating a separate query for my navigation form.

Is my problem solved? No I don't think so, because I have to redo a form and query. I do think I understand how NOT to create the problem again.

Thanks to everyone involved!

[MylesM]

You can find reference to that macro in system table MSysObjects

[isladogs]

After a database object is deleted, Access keeps a copy on memory until the database is closed.
However, if the database crashes for some reason before that has been done, the code for the object remains in the VBE as something like 'Form_~TMPCLP29151 or in your case ~TMPCLPMacro

The object itself is of course no longer available or listed in the nav pane.
However, the objects are left in the system table MSysObjects but (for safety reasons) this table cannot be directly edited

However there are 2 work rounds to delete it which I described in this thread at another forum:
https://www.access-programmers.co.uk...d.php?t=293579

Surprisingly the easiest way to get rid of it is to create a new object with the same name
It doesn't actually get created and even better it removes the MSysObjects item!!!!
Not tested with macros (as I never use them) but it works with forms/reports etc left over in the same way!!!

Hopefully this will fix your issue