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" <laurel....@wmich.edu> wrote in message
news:OEQN6QfP...@TK2MSFTNGP05.phx.gbl...

[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" <laurel....@wmich.edu> wrote in message

news:%23EJws3h...@TK2MSFTNGP06.phx.gbl...

[Kate]

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

[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]

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" <laurel....@wmich.edu> wrote in message

news:4ABD307...@wmich.edu...

[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

"Kate" <laurel....@wmich.edu> wrote in message

news:4ABD318C...@wmich.edu...

[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

"Gina Whipp" <NotInt...@InViruses.com> wrote in message
news:upmBGaiP...@TK2MSFTNGP02.phx.gbl...

[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" <laurel....@wmich.edu> wrote in message

news:OEQN6QfP...@TK2MSFTNGP05.phx.gbl...

[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" <laurel....@wmich.edu> wrote in message
news:uOAof2D...@TK2MSFTNGP02.phx.gbl...

[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" <laurel....@wmich.edu> wrote in message

news:eoCg13E...@TK2MSFTNGP02.phx.gbl...

[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

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

Kate wrote:
>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

--
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200909/1

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

AccessVandal via AccessMonster.com wrote:
> 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

[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

"Kate" <laurel....@wmich.edu> wrote in message

news:uQtRf6QQ...@TK2MSFTNGP05.phx.gbl...

> 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...
>
>
> AccessVandal via AccessMonster.com wrote:
>> 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.

>> I wanted to tell you that first in the beginning but I don�t why it works

[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" <laurel....@wmich.edu> wrote in message

news:uPj9LLRQ...@TK2MSFTNGP04.phx.gbl...
> Gina,
> These are the references as set in Access 2003: (pasted image below, it
> may not come through)
>
> <!--[if !vml]--><!--[endif]-->
>
> In 2007, the only difference is the 12.0 object library replaces the 11.0.
>
> Yes,
>
> Option Compare Database
> Option ExplicitIs at the top of EVERY module.
>
> I found that this topic of subform references was brought up in this
> newsgroup back in Oct. 2, 2008.
> The only solution the person found was to RECREATE the form under
> Access 2007!!
>
> This is what he said:
>
> Well, I recreated the form in Access 2007 and nested subforms worked.
>
> Now I'm really concerned. I took an Access 2000 base MDB and ran it in
> Access 2007. The nested subforms did not work. When I recreated the form
> with the subforms it works. NICE - NOT!! I just don't get it. Why?
>
>
>
> I don't get it, either. Everything I read about upgrading seemed to
> suggest it was seamless...
> If I have to convert all of my mdbs into accdbs for them to work, this is
> not an upgrade I wish to make.
> I cannot send you the database as it contains proprietary information,
> unfortunately.

[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

"Kate" <laurel....@wmich.edu> wrote in message

news:4AC22E5E...@wmich.edu...

[Kate]

Gina,
I think you are correct, that this was upgraded from Acc97.

I am using a common dialog control.

As to which objects caused errors, I can't recall. However, I'm in the
process of configuring an extra
computer as a test machine for Office 2007, so when I get that set up I
will perform this test and let you know!
(tomorrow sometime).

[Gina Whipp]

Kate,

Okie dokie...

--
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" <laurel....@wmich.edu> wrote in message

news:eigUF4UQ...@TK2MSFTNGP04.phx.gbl...

[AccessVandal via AccessMonster.com]

My guess it's still definitely a reference conflict in your VBA.

Do post your references here as Gina suggested. Here's how to check, copy
this code below and paste it into a standard module and in the immediate
window input ListReferences and press the Enter key. Copy and paste the
result from the immediate window to your browser or whatever you use to post
here.

Sub ListReferences()
Dim refCurr As Reference

For Each refCurr In Application.References
Debug.Print refCurr.Name & ": " & refCurr.FullPath
Next

End Sub

Here's what you can to to find out if the code will work in 2007 MDB format
(not ACCDB or if you prefer ACCDB than do that). Create a new blank DB with
main form, subform and sub-subform just like your original. Copy and paste
the into the form's module and a standard module. Activate it by a command
button if you like.

The new blank DB with VBA references should not include extra or unwanted
references, just the default basic ones. It a good way to find out if there's
any references conflict and from here, you do comparision with your original
DB.

In the meantime, here's a good site on references.
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

Kate wrote:
>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...

--

Please Rate the posting if helps you.

Message posted via http://www.accessmonster.com

[Gina Whipp]

She already did... but it was an image... She will be starting to remove
the *useless* and no loger ones tomorrow.

--
Gina Whipp

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

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

"AccessVandal via AccessMonster.com" <u18947@uwe> wrote in message
news:9cde8f082de78@uwe...

[AccessVandal via AccessMonster.com]

Thanks for the feedback. Let's hope she can resolve the problems.

Gina Whipp wrote:
>She already did... but it was an image... She will be starting to remove
>the *useless* and no loger ones tomorrow.

--

[Kate]

Yes, thank you both for getting involved. I am presently downloading a
slew of updates to
the old computer which I'm making my 'test office 2007' machine, and
after that completes,
I will jump back into this discussion. Stay tuned, and thank you!

-Kate

[Kate]

Okay, this is what I've done to address the code errors I was receiving
trying to run my Access 2003 applications under Access 2007:

I installed Office 2007 on a test machine, leaving Access 2003, Excel
2003, and Word 2003 intact. That was the first step.

My test machine, albeit an old PC with only 512 mb RAM, opened my Access
2003 mdbs without problems, in Access 2007. All functionality was there.
I unchecked the unnecessary reference to VBA extensibility, and still
not a hitch.

I then attempted to open the very same mdbs on the first machine, where
I had installed Office 2007 with the option to remove all previous
versions of Office,
and had the same problems as before.

I then removed Office 2003 from the test machine, and then tried to open
the mdbs in Office 2007. Turns out, Office 2007 has to
practically reinstall itself when you remove a previous version after
the original installation.

Anyway, the mdbs opened just fine after rebooting. We are coming to the
conclusion here that the first machine, where the errors still occur, is
having problems
caused by CLONING the hard drive to the new machine (this happened last
month), not by anything that Office 2007 is doing.

Thank you all for your diligent support!
-Kate

[Gina Whipp]

Kate,

No... THANK YOU for all YOUR hard work. You have enabled us all to know
that it is not an Access problem.

--
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" <laurel....@wmich.edu> wrote in message
news:OatHKChQ...@TK2MSFTNGP04.phx.gbl...

[AccessVandal via AccessMonster.com]

Thanks for the feedback. Glad you got it solved.

Allen Browne have something on this but Vista/Windows 7. Although some had
reported problem in Windows XP as well. MS does not recommend two version of
office installed on a single machine but you need some tweaks if you want it
work.

http://allenbrowne.com/bug-17.html

It also a well known fact that MS uninstall may not remove all Registry or
Folders or files that may lead to problems with newer MS Office.

Kate wrote:
>Okay, this is what I've done to address the code errors I was receiving
>trying to run my Access 2003 applications under Access 2007:

snip....

[Kate]

Thanks for that link. I have saved it for future reference, as I think
this will not be the end of it...

I wonder if the installation procedure for Office 2007 does an excellent
job of removing all previous versions,
if that is the installation chosen.