Invalid Reference Error

[AG]

I have two Access 2000, properly split, applications deployed at a client.
Both are quite complex (58,000 and 26,000 lines of code) and connect to
three (the same) backends.
Anywhere from 2 to 15 users each, read/write.

Occassionally, I get error 2455 (You entered an expression that has an
invalid reference to the property Form/Report) on lines like the following:

If Me!MySubform.Form.RecordsetClone.RecordCount > 0 then
Me!MySubform.Form.MyPublicFunction
If Nz(Me!MySubform.Form.!SomeControl,"") <> "" Then

This and similar code runs thousands of time each day with no problem, but I
only occassionally get the error.
Distributing and mde vs. mdb seems to make little or no difference.

Once in a while I also receive error 3020 (Update or CancelUpdate without
AddNew or Edit), in the BeforeUpdate event of a bound (main or sub) form.

I hve not been able to duplicate either error myself on my machines, or at
client via TS.

The client's network is somewhat slow and some of the routines that run when
changing main form records sometimes take a noticeable amount of time.

Can anyone offer any ideas of what might be the cause, and/or solution?

We do have a project in the works to convert to SQL Server back end and
Access 2007 front end, but it has been put on hold for a while.

 

[Mark Han[MSFT]]

Hi AG,

Welcome to Microsoft MSDN Managed Newsgroup. This is Mark. I am very glad
to work with you on this issue.

according to your description, I understand that you meet 2 issues when
running the application. one is error 2455; another is 3020. They are 2
different issues. Generally, in order to ensure that the right support
professional can work with you to analyze and solve the technical issue in
more timely and efficient manner, generally, we only focus on one issue per
service request. Therefore, please tell me what error is your main
concern and then I will focus on the error to help you resolve it.

in order to better assist you, personally, I do research on the 2 error.
1 according to the error 2455(You entered an expression that has an invalid
reference to the property Form/Report), we could know that the the property
might not exist. Also please tell me if the form is a Unbound Forms. if
so, that might be a known issue. there is an article to share with
you:http://support.microsoft.com/kb/208908

2 about the error 3020, there are 2 articles to share with you:
BUG: 3426 and 3020 - Different Error Number With 16-bit/32-
bit:http://support.microsoft.com/kb/149040
PRB: Rollback Causes DAO Recordset Editmode To Be Set Back to
:http://support.microsoft.com/kb/179616

also, based on my experience, it might be caused by "recordset do not have
.edit ". So please check the code running in the application.

Besides, about MDE Vs MDB; I would like to explain the following
An MDE file is the same as a regular Access MDB database file, with the
following changes:
1 All VBA procedures are compiled — converted from human-readable code
(more or less readable, anyway) to a format that only the computer
understands. This change prevents a database user from reading or changing
your VBA code.
2 No one can create forms or reports or modify the existing ones (you can't
even open them in Design view). You can't import any, either.

in order to help you to resolve the issue, could you send the access
database file to me? and tell you what event/fuction is running when the
issue happens?

Please feel free to let me know if you have any other questions or
concerns. I look forward to your reply. Thanks.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[AG]

Mark,

Forms and subforms are all bound.
I mentioned both errors because they both seem to occur randomly and don't
seem to apply.
My main issue would be the error 2455, since it occurs most often.

It occurs in different forms as seemly random times. The same code runs
probably thousands of times each day without a problem, then suddenly pops
up.
The subforms and controls difinitely exist and are never removed.
There are no recordsets opened in code - these are all bound forms.

I do believe that the client has some network issues.
Is it possible that if a network error occurs, Access can lose it's
reference to a bound subform?

I do understand the difference between mde and mdb. I mentioned it because
when code errors occur, mdb's often reset public variables, while mde's do
not.
Client typically uses the mdb, but I had sent him mde, just to see if it
made a difference. There was not noticable difference.

I can't send the database because it is part of a large system. Plus, I
can't duplicate the errors.

--

AG
Email: npATadhdataDOTcom

 

[Mark Han[MSFT]]

Hi AG

Thank you for the reply.

I understand that it is not convenient to you to send the access database
to me; However, to further diagnose the problem, we need to get to know the
detail code evnet which is running when the issue happens. So, if it is
convenient to you, could you please send me a sample of the access database
to me?

Besides,if the access will lose it's reference to a bound subform when
netwrok issue happens is based on how we involve the reference for the
bound subform and what reference we involve for the bound subform.

Based on the above, the sample of the access database is very inportant for
us to better understand and diagnose the problem. if you could send it to
us, it will be easier for us to understand the problem and know what
happens when the issue occurs.

if you have any questions on the above, please let me know.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[AG]

Mark,

I will need to get permission from the client to provide you with a copy.

 

[AccessVandal via AccessMonster.com]

With this kind of code you'll get errors like "(You entered an expression
that has an invalid reference to the property Form/Report)"

It should be like
If Me.RecordsetClone.RecordCount > 0 then
'do something
but with you'll also get an error as "Me!MySubform.Form.MyPublicFunction" as
well as
(if you want to run a function, change it to the function name like
"MyFunction")
"If Nz(Me!MySubform.Form.!SomeControl,"") <> "" Then".

It should be like
If Nz(Me.SomeControl,"") <> "" Then

'If Me!MySubform.Form.RecordsetClone.RecordCount > 0 then
'Me!MySubform.Form.MyPublicFunction
'If Nz(Me!MySubform.Form.!SomeControl,"") <> "" Then

 

[AG]

Thanks for the reply.
I think I will try moving the subform's public function to the main form,
even though it will probably only move the error to a different place. The
function changes the recordsource of the subform, to it still needs to be
referenced.

The big thing with all of this is that the syntax is all correct and it all
runs thousands of times each day. Only once in a while does it error out.
That is why I mentioned the network. I wonder what happens within Access
when code is running and the network hiccups while Access is trying to get a
different record from the back end.

 

[AccessVandal via AccessMonster.com]

So far, I don't think this method "Me!MySubform.Form....." is workable. I had
tried this before, it's not always working as it is. I rather use "Forms!
MainForm!Subform.Form.Control" and "Forms!Mainform!Control".

 

[AccessVandal via AccessMonster.com]

PS. You can't call a function/subroutine from the mainform if the function is
in the subform. You can put it as a public function all you want but as far
as the form's module are concern, it is private and known only to the form's
module and can only be called the the form's module no where else.

If you want it as a Public function, put into a Standard module and you can
called it from any where from your form's module.

Thanks for the reply.
I think I will try moving the subform's public function to the main form,
even though it will probably only move the error to a different place. The
function changes the recordsource of the subform, to it still needs to be
referenced.

The big thing with all of this is that the syntax is all correct and it all
runs thousands of times each day. Only once in a while does it error out.
That is why I mentioned the network. I wonder what happens within Access
when code is running and the network hiccups while Access is trying to get a
different record from the back end.

With this kind of code you'll get errors like "(You entered an expression
that has an invalid reference to the property Form/Report)"

[quoted text clipped - 19 lines]

 

[Mark Han[MSFT]]

Hi Alphonse

Thank you for the reply and coopreation.

once I receive the sample file of the access database, I will do further
research and provide you an appropriate action plan.

Have a nice day.

est regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[AG]

Thanks for the reply, but you are off base here. A public function in a
subform can be called in the manner I described. I have used it for years in
many systems. My problem is NOT that it never works. It works fine thousands
of times a day.
If it did not, then the entire system would be useless. It is only once in a
while that I get the errors.

--

AG
Email: npATadhdataDOTcom

PS. You can't call a function/subroutine from the mainform if the function
is
in the subform. You can put it as a public function all you want but as
far
as the form's module are concern, it is private and known only to the
form's
module and can only be called the the form's module no where else.

If you want it as a Public function, put into a Standard module and you
can
called it from any where from your form's module.

Thanks for the reply.
I think I will try moving the subform's public function to the main form,
even though it will probably only move the error to a different place. The
function changes the recordsource of the subform, to it still needs to be
referenced.

The big thing with all of this is that the syntax is all correct and it
all
runs thousands of times each day. Only once in a while does it error out.
That is why I mentioned the network. I wonder what happens within Access
when code is running and the network hiccups while Access is trying to get
a
different record from the back end.

With this kind of code you'll get errors like "(You entered an
expression
that has an invalid reference to the property Form/Report)"

[quoted text clipped - 19 lines]

I will need to get permission from the client to provide you with a
copy.

 

[AccessVandal via AccessMonster.com]

Well, that's good but so far it doesn't work for me.

Besides Hardware/network issues did you do a compact and repair as well as
decompile?
See Tony Teow's site on performance, may not help but will give you some
ideas about your problems.
http://www.granite.ab.ca/access/performancefaq.htm

and how to decompile
http://www.granite.ab.ca/access/decompile.htm

Lets hope Mark Han has a solution for you.

Thanks for the reply, but you are off base here. A public function in a
subform can be called in the manner I described. I have used it for years in
many systems. My problem is NOT that it never works. It works fine thousands
of times a day.
If it did not, then the entire system would be useless. It is only once in a
while that I get the errors.

PS. You can't call a function/subroutine from the mainform if the function
is

[quoted text clipped - 29 lines]

 

[AG]

Thanks,

I moved that one routine out of the subform, just to see if anything
changes.
Yes, I never deploy an application without doing a decompile,
compact/repair, compile, compact/repair.
Also do it several times during long development.

--

AG
Email: npATadhdataDOTcom

Well, that's good but so far it doesn't work for me.

Besides Hardware/network issues did you do a compact and repair as well as
decompile?
See Tony Teow's site on performance, may not help but will give you some
ideas about your problems.
http://www.granite.ab.ca/access/performancefaq.htm

and how to decompile
http://www.granite.ab.ca/access/decompile.htm

Lets hope Mark Han has a solution for you.

Thanks for the reply, but you are off base here. A public function in a
subform can be called in the manner I described. I have used it for years
in
many systems. My problem is NOT that it never works. It works fine
thousands
of times a day.
If it did not, then the entire system would be useless. It is only once in
a
while that I get the errors.

PS. You can't call a function/subroutine from the mainform if the
function
is

[quoted text clipped - 29 lines]

I will need to get permission from the client to provide you with a
copy.