2003 code raises error in 2007

K

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
 
K

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
 
K

Kate

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

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.
 
P

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.
 
K

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.
 
K

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.
 
K

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
 
A

AccessVandal via AccessMonster.com

Kate,

Access 2007 seems to be very very picky on how you code it. What I can
suggest is to take Paul’s advice of not using the full qualifying name
references but instead use what he suggested ctrlSub.Form and ctrl.Form?

There no need for that as you are already looping through the properties of
the SubForm and the Sub – SubForm.

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 ctrl.Form
For Each ctrlSub In ctrl.Form

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

I wanted to tell you that first in the beginning but I don’t why it works in
Access 2003, so I had to put that on hold.
 
K

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...
 
G

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
 
G

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
 
K

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

2003 code raises error in 2007 12
Create a SubForm on a Tab Control 0
Keep form open until loop completes 2
Display contents of Tag property for all (closed) forms in the db 2
VBA works in orignal but fails in copy of database 1
You must use the dbSeeChanges option with Open Recordset whenaccessing a SQL Server table that has a 1
Syntax Error WHY? 16
I can't get this code to work 1

Top