VBA code broke in migration from 2003 to 2007

[Clay_LMCO]

I have some very extensive VBA code using Access forms for the UI. While
migrating from Access 2003 to Access 2007 I have encountered a problem with
the "visibility" of subform controls and their properties in my VBA code.

One of two things happens when the problem occurs:
1. The code behaves as if I have put in a "On Error Resume Next" statement
and ignores any problems.
2. I get the following error - "The expression you enetered refers to an
object that is closed or doesn't exist."

The problem appears to be triggered after I change the RecordSource property
of the subform. I have written code that will search for the control objects
starting with the Application.Forms property and searching down through the
subforms to get to the controls, but this is very cumbersome and performance
is slow.

Has anyone encountered this and found a fix?

Thanks.

 

[Jeff Boyce]

Clay

I've run across a similar behavior, both in migration to Access 2007 and in
migration to previous versions from (even) earlier versions.

What worked in my situations was inspecting all the VBA for the use of the
"dot" instead of the "bang". This may no longer be applicable, but it
appeared that if I took the easy way in referring to a control:

Me.MyControlName

to gain the advantage of the Intellisense completion, in some subsequent
versions, Access complained. When I converted that to:

Me!MyControlName

Access accepted and compiled the changed syntax.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clay_LMCO]

Thanks, Jeff. It was a good try but it unfortunately did not fix my problem.

--
Clay Watson
Lockheed Martin Aeronautics Company
Ft. Worth, Texas

Clay

I've run across a similar behavior, both in migration to Access 2007 and in
migration to previous versions from (even) earlier versions.

What worked in my situations was inspecting all the VBA for the use of the
"dot" instead of the "bang". This may no longer be applicable, but it
appeared that if I took the easy way in referring to a control:

Me.MyControlName

to gain the advantage of the Intellisense completion, in some subsequent
versions, Access complained. When I converted that to:

Me!MyControlName

Access accepted and compiled the changed syntax.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mike Painter]

Is the file in a trusted location?
That fixes a lot of problems.

Try cutting and pasting the existing code into another routine, then
renaming.
I've seen that work for me with queries in subforms twice now.
Make sure that no reserved words are being used.
Extra milk and cookies for the brownies.
Are you running the code in 2007 or did you convert it?

 

[AccessVandal via AccessMonster.com]

‘2. I get the following error - "The expression you enetered refers to an
’object that is closed or doesn't exist."

This is a problem I had in the past. My guess is due to windows update. I
never had this problem when using XP with Access2007 Runtime until windows
update came. This problem persists in Vista with full version of Access 2007.
(That includes Vista with Access 2007 Runtime).

What I did was just simply save the MDB(A2K) in Access 2007. If it does not,
than I’ll upgrade the format to Accdb. If it still does not work, I had to
recode the event that is causing the problem.

As you can see, the problem does not lie in one area. It works for Jeff, and
I do not know environment condition in Jeff’s PC. What sort of windows update
was in the PC?

It appears that VBA is having problems reading a control value, the problem
is consistent but this problem varies with data like.

ItemID Like Ԡ& Me.ItemID & “*’â€

The control “ItemID†will read text like “ACCDxxx†or “ACCExxx†but will not
read “ACCSxxx†or sometimes will not read the other.

This problem goes away if I recompile, compact and repair in XP/ A2K without
any code changes. With the buggy update of course.

Another would be a default value of a control. I had to recode on the form’s
onOpen event to set a value to the control. Although, VBA debugging show the
data/default value is correct, it just throws the error as above. (nothing to
do with date setting or regional setting).

I hope the explanation will be able to help you solve some your problems.

PS. They are in Trusted Locations for full version of Access 2007.

 

[Clay_LMCO]

Thank you Jeff, Mike, and AccessVandal for the comments. I still don't have
a fix yet, but I avenues to explore. I will be going back to my original
development/testing forms and rebuild my libraries from there. At this point
I don't see any shortcuts.

Based upon what I see with other Access developers, I am doing some
unconventional things. For example, I have embedded pointers to subforms
within VB class objects to encapsulate tool libraries I use across numerous
applications. It has resulted in higher levels of reusable code, big savings
in programming time, and even has performance benefits. However, it also
seems to put my problem into uncharted territories.

I am sure that the problem I am having has to do with persistence of object
pointers within my class library and is tied specifically to subforms.
Everything still works great at the parent form level. Is anyone aware of a
change in Access 2007 that would result in loss of persistence for subforms?

 

[Clay_LMCO]

I believe I have found the problem! Apparently the new version of MS Access
is multithreaded in ways that it was not in previous versions. What is
happening is that I have "critical sections" in my code that are being called
by multiple threads and the local variables are getting clobbered.
ARRRRGH!!!!!

At least I know what is happening. Thanks again to all that tried to help.

 

[Michael J. Strickland]

I have some very extensive VBA code using Access forms for the UI.
While
migrating from Access 2003 to Access 2007 I have encountered a problem
with
the "visibility" of subform controls and their properties in my VBA
code.

One of two things happens when the problem occurs:
1. The code behaves as if I have put in a "On Error Resume Next"
statement
and ignores any problems.
2. I get the following error - "The expression you enetered refers to
an
object that is closed or doesn't exist."

The problem appears to be triggered after I change the RecordSource
property
of the subform. I have written code that will search for the control
objects
starting with the Application.Forms property and searching down
through the
subforms to get to the controls, but this is very cumbersome and
performance
is slow.

Has anyone encountered this and found a fix?

Thanks.

It sounds like it might be a reference problem.

One thing to remember is that Access 2007 went back to using DAO as the
default data access package so you might want to check your
references/declarations and make sure they are correct.



--