Solved Cannot find referenced form... error?

[HalloweenWeed]

Hello again,
I have a split database on an institutional network, a very complicated front-end with dozens of forms that interact, many interact with each other via vba. I am using a method that implements 'live search' technique in the affected form. The problem is, as soon as I click on the (only) subform, I get:

Microsoft Access cannot find the referenced form 'Security_Current_User_Name'.
* The from you reverenced may be closed or may not exist in this database.* Microsoft Access may have encountered a compile error in a Visual Basic module for the form.

This is really odd to me, as this was the name of a hidden form that I renamed nearly a year ago, and since then I have rebuilt the db FE 'from scratch' so-to-speak, importing only the tables and 6 reports. All the rest was recreated by:
1. copying the SQL from the original FE in queries, and pasting the SQL into each of the fresh new queries.
2. Rebuilding each form from the 'create new form', setting all the form properties, and then copying all the objects from each panel (header, detail, etc.) and pasting them into the fresh new forms.
3. Copying and pasting each module vba from the original to the new FE modules.

I have done a vba search of the (only) entire vba project for:
Security_Current_User_Name
Security_
_Current_
and it finds nothing.

And then I subsequently get "Error #2465: Application-defined or object-defined error."

I have searched the SQL in each of the queries including source queries related to the form and subform throwing the error, and they don't contain "Security_Current_User_Name". The SQL that I originally pasted didn't either.

Said form Security_Current_User_Name was only in the FE, never in the BE. db is only split into two, FE & BE.

The parent form is unbound. The subform is bound to "qry_RspndrSuffixSelect". The parent form does a requery of the subform when the only visible field on said parent form is changed, both "on key up" (it transfers the "Text" value to a hidden textbox that is used in the query for the subform), and "After Update".


The form Security_Current_User_Name was originally referenced in some macros within form events, to check for permission to view the data within. All these have been converted to vba, and since then the forms rebuilt from scratch, so I don't see how any old db FE reference data to form Security_Current_User_Name could have gotten into the new db. No form controls, or even forms themselves, were ever bound to that form either.

I have checked the db FE relationships, it is not there either.

I compose vba with auto-compile off, then compile, then save. All compiles without error.

More clues: the error only comes up when I click on the subform, on a different record; i.e. if I click a second time on the same record it does not do it. This makes me refer to the subform "current" event, which was empty, so I tried putting in a macro to "requery," saved the subform, removed the macro, saved it again, no joy. Each record contains only two non-editable controls, textboxes, and one "Choose" button. The "Choose" button is only tied to one event: OnClick - vba. When I try to click on the button, with a breakpoint on the first line of executable code in the procedure, the error comes up before the break, so it must be something tied to form events such as "current" or a focus event. The parent form only has events on Load and Open. The subform uses no events whatsoever, and it's textboxes are bound and locked. It doesn't matter where on the subform record line I click, behaves the same way. The parent form control textbox that is losing the focus has an after update and on key up events, both vba. I've checked the vba for anything that could throw said error and am stumped.

Any ideas are desperately wanted and appreciated.

 

[theDBguy]

Hi. Is there any way you can post a demo copy of your db to show the problem?

 

[HalloweenWeed]

Hi. Is there any way you can post a demo copy of your db to show the problem?

Hmm (thinking here), this is a db of protected personal info, but this particular form only changes the suffixes/titles of the list of available names of staff (and only for subsequent records), so I think that could be done. It will take quite a while to do though, I will just need to delete and replace the names of the people with some dummy names, I think that could be done. I will work on it, that seems preferable to the alternative. Who knows, when doing so maybe I will get another clue about where it is coming from for instance when the problem disappears if it disappears. I will be converting some linked tables to local in order to do so.

First I need to debug some other facets that I just upgraded. But that 'shouldn't' take very long.

 

[theDBguy]

Hmm (thinking here), this is a db of protected personal info, but this particular form only changes the suffixes/titles of the list of available names of staff (and only for subsequent records), so I think that could be done. It will take quite a while to do though, I will just need to delete and replace the names of the people with some dummy names, I think that could be done. I will work on it, that seems preferable to the alternative. Who knows, when doing so maybe I will get another clue about where it is coming from for instance when the problem disappears if it disappears. I will be converting some linked tables to local in order to do so.

First I need to debug some other facets that I just upgraded. But that 'shouldn't' take very long.

Hi. Anything you can post would probably be better than just us guessing at things in your db. We don't need everything, just the ones related to the issue. And yes, using dummy data would also help. Thanks!

 

[Isaac]

I would do a deep dive into the properties pane of any 'containers' - especially subform type containers. Just wondering if there is some old container control somewhere that is referencing a Source Object that no longer exists.

Just a feeling, it may involve a Property like that somewhere. Now is where I might think of writing some code that searches all forms for all properties' names, and all forms for all of their controls' properties' names. More than likely, there's a tool out there somewhere for that, but I find it more rewarding and educational to write my own. I've never written code that does exactly that, so broadly, but it probably isn't too bad.
Anyway, those're my thoughts.

 

[HalloweenWeed]

I have just too many issues with putting this in a public download link.
PLEASE msg me here (click on my icon and "start conversation"), and I will happily send you a Dropbox download link (until the issue is resolved).

 

[isladogs]

Before you do that, try ticking hidden objects in the nav pane. Does the form appear?
If not, search all forms in the MSysObjects system table. The forms all have Type = -32768

 

[HalloweenWeed]

Before you do that, try ticking hidden objects in the nav pane. Does the form appear?

I only have a choice for "hide," not "unhide" or "show."

If not, search all forms in the MSysObjects system table. The forms all have Type = -32768

I have done that, it is not there. I even delved into all the other (Msys) tables that I could figure out how to, and the aforementioned form name is not there.

 

[isladogs]

I only have a choice for "hide," not "unhide" or "show."

Right click an empty space in the nav pane & click Navigation Options then tick the checkbox as below

I have done that, it is not there. I even delved into all the other (Msys) tables that I could figure out how to, and the aforementioned form name is not there.

If its not in MSysObjects, then you have deleted it at some point. Do you have a backup?

 

[HalloweenWeed]

This is all I get:

Perhaps you are using an older version of Access?

EDIT: Oh, it was under "navigation options":

If its not in MSysObjects, then you have deleted it at some point. Do you have a backup?

No, it sounds like you don't understand, I don't want it, I want to stop the error from popping up, so I want to find where it is coming from and delete/correct it. Unfortunately, it doesnt seem to be coming from anywhere that I can access, and I can't even understand how it got there in the first place.

 

[Minty]

Download VTools and perform a Deep search of all objects.

V-Tools

 

[isladogs]

I did say to look in Navigation Options

Sorry. I skim read post #1 earlier & forgot what you wrote originally when I answered last time.

V-Tools is excellent & FREE! It should find all references to the obsolete form in queries/recordsets/combos/listboxes etc.
If somehow that isn't enough, Phillip Stiefel has an even more powerful equivalent...but its a commercial app.

For future reference, before renaming something that may be used elsewhere, it is always worthwhile checking the Object Dependencies in Database Tools. If anything is listed as depending on the object to be renamed, then switch on Name Autocorrect then do the rename. Many experienced developers would then advise you to switch that off again afterwards (though I use it with caution and never have issues with it)

 

[Isaac]

Simply coding to loop through all QueryDefs (and evaluating their .Sql property) will, indeed, not only include saved visible Query objects, but also the queries that start with ~ that you can't see....Recordsources, Cbo/Lb Rowsources, etc. If this finds it - great. If not, I think you will need to dig deeper into all of the Properties of all forms & controls. If Vtools does that, then I'm impressed - that's a deep dive!

 

[HalloweenWeed]

Unfortunately, I cannot install Vtools, am locked down by the institution and my home computer does not have Access.
I would love to try though.
Will try the QueryDefs loop, will Google it. Seems pretty easy to do an Instr search.

 

[Isaac]

Will try the QueryDefs loop, will Google it. Seems pretty easy to do an Instr search.

It is. And quite revealing, all of the ~ "querydefs" that the system is storing for you. Include in your code for output/results, the parent object(s), else it it will be hard to know where the heck ~sql_12-def or some obscure name like that, goes to.

 

[theDBguy]

Unfortunately, I cannot install Vtools, am locked down by the institution and my home computer does not have Access.
I would love to try though.
Will try the QueryDefs loop, will Google it. Seems pretty easy to do an Instr search.

Maybe you can use this one.

UtterAccess.com

www.utteraccess.com

 

[isladogs]

Simply coding to loop through all QueryDefs (and evaluating their .Sql property) will, indeed, not only include saved visible Query objects, but also the queries that start with ~ that you can't see....Recordsources, Cbo/Lb Rowsources, etc. If this finds it - great. If not, I think you will need to dig deeper into all of the Properties of all forms & controls. If Vtools does that, then I'm impressed - that's a deep dive!

V-Tools looks at al database objects including combos/listboxes, module code & macros.
Phillip Stiefel's app for even further and looks into all form & control properties

 

[wvmitchell]

You could run the built-in Access documenter, turn on every possible option, and then search the output.

 

[Isaac]

V-Tools looks at al database objects including combos/listboxes, module code & macros.
Phillip Stiefel's app for even further and looks into all form & control properties

It's amazing what's out there. Designing in a given platform is one thing - but designing "developer tools" is something extra special, I always think.
Also those who design add-ins for SSMS or Visual Studio. Impressive dev area.
Much respect!

 

[HalloweenWeed]

You could run the built-in Access documenter, turn on every possible option, and then search the output.

Success!

db debug ver.accdb

Form: RspndrSuffixSlctSubform Page: 942

Properties

...

Objects

...

Command Button: Choose_But

...

OldBorderStyle: 1 OnClick: [Event Procedure]

OnGotFocus: [Embedded Macro] OnGotFocusEmMac Version =196611

ro: ColumnsShown =0

Begin

Condition ="(Not [Forms]![Security_Current_User_Name]![RW])"
Action ="Close"
Argument ="-1"
Argument =""
Argument ="2"
End

Begin
Condition ="..."
Action ="MsgBox"
Argument ="\"You do not have permission to open this form!\""
Argument ="-1"
Argument ="4"
End

Begin
Condition ="..."
Action ="StopMacro"
End

Begin
Comment ="_AXL:<?xml version=\"1.0\" encoding=\"UTF-16\" standalone=\"no\"?>\015\012<UserInterfaceMacro
For=\"Choose_But\"
Event=\"OnGotFocus\"
xmlns=\"http://schemas.microsoft.com/office/accessservices/2009/11/application\"><Statements><Co

...

The button had an embedded macro in "OnGotFocus"! (Duh). I didn't even notice that.
Enigma solved! Thank you all for helping.
Thank you very much for your ideas, and I have learned a lot.