2003 code raises error in 2007

[Kate]

I originally posted this on the Access conversion
newsgroup, but the response was so minimal, I
thought this would perhaps be a better place to post.


I have a fully-qualified reference in VBA to a
subform of a subform of a form, which works fine
in Access 2003.

In Access 2007, however, it results in "you have
entered an invalid
reference to the property Form/Report."

This is the snippet of code:


Private Sub SetFormProperties()
'called from form_open, makes all forms editable
if edit mode was selected

Dim ctrl As Control, ctrlSub As Control
Dim frm As Form

If Me.OpenArgs = "Edit" Then
Set frm = Me

'set main form edit options
EditProperties frm

'set subform edit options
For Each ctrl In frm
If ctrl.ControlType = acSubform Then
EditProperties
Forms(frm.Name).Controls(ctrl.Name).Form
For Each ctrlSub In ctrl.Form

'subforms can have subforms!
If ctrlSub.ControlType =
acSubform Then
EditProperties
Forms(frm.Name).Controls(ctrl.Name).Form.Controls(ctrlSub.Name).Form
'
THIS IS THE LINE THAT CAUSES THE ERROR!!
End If
Next ctrlSub
End If
Next ctrl
End If
End Sub



Sub EditProperties(frm As Form)
'this only gets called when Edit mode is true, as
forms are opened
read-only otherwise.

With frm
.AllowAdditions = True
.AllowEdits = True
.AllowDeletions = True
End With
End Sub


Thanks if you can help me,
Kate

 

[Gina Whipp]

Kate,

The response may have been minimal but the suggestions were valid. Did you
try any or all of those suggestions?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kate]

Gina, I did everything that was suggested, with no success. FWIW, the
MS object library 12.0 has of course replaced the 11.0 which is
what this database was created under.

I also opened a different database application, and immediately
encountered an error under Access 2007.

I am finding that Access 2003 code does NOT work under Office 2007!

Thanks,
Kate

 

[Gina Whipp]

Kate,

They do because not only do I do it but my Clients do it. Are your
databases in a Trusted Location?
http://www.regina-whipp.com/index_files/TrustedLocation.htm (Yes, I know
they are 2003 databses but s007 still needs them in a Trusted Location.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kate]

Gina, I enabled all macros, putting security at the lowest (not
recommended) level. That has the same effect, I believe.

 

[Gina Whipp]

That is not a Trusted Location, did you do that?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kate]

But, I just now did add the location to trusted locations, and still the
problem.
I don't believe it has to do with security, but with a different version
of the object library.

 

[Gina Whipp]

Kate,

That really shouldn't do that because of the library difference. Have you
commented out the code to see if anything else shows.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

I meant if any other code bombs out...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Paul Shapiro]

I have several applications with 10's of thousands of lines of code, and
found no issues running in Access 2007. Some of these apps go back to Access
2.

Your form references seem a little more convoluted than necessary to me.
Instead of:
Forms(frm.Name).Controls(ctrl.Name).Form.Controls(ctrlSub.Name).Form
why not just use: ctrlSub.Form?

If you want to keep the full expression, maybe it needs the .Form reference
on the initial Forms():
Forms(frm.Name).FORM.Controls(ctrl.Name).Form.Controls(ctrlSub.Name).Form

When declaring variables, it might not matter but I prefer to disambiguate
references by using Access.Form instead of just Form. If more than one
library has a Form object, the particular object being referenced would
depend on the library listing order when it's declared as Form.

 

[Kate]

Paul, thanks for the suggestion. The reason why I need the full
reference is that I'm referring to the subform's subform FROM the main form.
I added in the .form reference as you suggested, to the main form's
reference, and still get the same error.

 

[Gina Whipp]

Kate,

After reading your reply I am a bit confused, why not use...

Me!Subform1.Form!Subform2.Form!ControlName

OR

Me!Subform1.Form!Subform2.SourcObject

....from http://www.mvps.org/access/forms/frm0031.htm

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kate]

Gina, because I loop through all controls on a form testing whether they
are subforms,
I need to refer to them by the control property, rather than as a subform.

 

[Gina Whipp]

Kate,

I am at a loss as to why this is not working in Access 2007. Perhaps it is
related to 2007 but upon reading
http://msdn.microsoft.com/en-us/library/bb214299.aspx I saw no difference.

Hopefully, someone else can step in with a suggestion.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kate]

Thanks, Gina. I'm going to have to set up a test machine and work
through these problems there, with both versions of Access installed.
Thanks for you help!
-Kate

 

[Kate]

Oh, gee, you had me all excited to try this! So I did, and it of course
worked with no error in Access 2003,
but RAISED THE SAME INVALID REFERENCE ERROR in Access 2007!!! SOOOO
disappointing.

Thanks, please keep trying...

 

[Gina Whipp]

Kate,

Please list all the References you have set in Access 2007 and the order in
which they are listed. Also can you check..

....Is this at the top of every module that has code, including forms...

Option Compare Database
Option Explicit

OR

Is the database in a state where you can send it to me?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Gina Whipp]

Kate,

I actually got the image, uunusual becuase they are normally stripped... It
appears that you opened this in Access 2003, what References are set in
Access 2007...

Also... MAKE A BACK-UP and do this on the BACK-UP

Uncheck the Reference to Microsoft Visual Back for Applications
Extensibility and reopen in Access 2007, does the same error happen?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Kate]

Gina, yes, I did create that ref list in 2003; as I said, in 2007 the
only difference is that the library is ver 12.0.

I can tell you what happens if the VBA extensibility ref isn't checked
in 2007, because I inadvertently did that when I was
trying out another person's suggestion, which was to delete all
references in 2007, then add them back in:

I got other object errors because some objects were no longer
recognized. Didn't even get to the point of the current error.

 

[Gina Whipp]

Kate,

The Reference should not be needed. Was this database upgraded from Access
97?

This is a list of References you should see in Access 2003..

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.5 Library

You say when you unchecked that Reference you got a list of object errors.
Which objects? Also, are you using Common Controls in your database?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm