Is Access 2007 badly broken or is it just me ?

A

Aceware

Client has just gone to latest patched office 2007 (without discussing with
me).
I have a quoting application that was working well in 2003.

I have a parent / child pair of forms where after changing any field in the
detailed
task list in the subform, calls a recalc function
eg

sub Technician_AfterUpdate()
docmd.runcommand accmdSaveRecord
RecalcLine me.parent.recordsetclone,
me.recordsetclone,me.parent.bookmark, me.bookmark
end sub

I now get :

"Object invalid or no longer set "

In the called subroutine when referencing the second parameter.

I am now working my way through the code replacing the recordsets with
explicit code such as

set rsHeader = currentdb().openrecordset("select * from [tbl Trials] where
TrialId = " & lngTrialId,dbopendynaset,dbseechanges)
set rsDetail = currentdb().openrecordset("select * from [tbl Trial Tasks]
where TrialId = " & lngTrialId, dbopendynaset, dbseechanges)

But this is very tedious and is producing other side effects, as well as
running slower.

I am contemplating telling the client to roll back to office 2003 or else
find another programmer.

Please, I am desperate for any help.

Many thanks in advance
Tony Epton
 
A

Albert D. Kallal

I don't understand or see why your example code is breaking in 2007...

Does your code correctly compile in 2007?

Remember, if some error occurs, and then all your variables do loose their
value unless you have error code handling.


Also, go tools->references and REMOVE the ADO reference

(Microsoft ActiveX data object 2.5) (or something similar....

the above assumes that you not using ado...

When you do the above, you should then do a compact and repair..and exit
your application..and then re-enter....

You can also "force" the data type parameter in:

Public Sub RecalcLine (rstParent as Dao.ReocrdSet, rstChild as
Dao.RecordSet....


So, "force" the reocrdset type if you having problems with the

me.Parent.ReocrdSetClone

The above works just fine for me..but, it DOES fail if you have a ADO
reference in the tools->references in the code window..

So, nothing has changed in terms of VBA..and I converted access 97, and
access 2003 applications with 30,000+ lines of code into 2007 and it works
fine....

So, it not the coding that needs to be changed here...it is your
references.

You can also try moving up the Microsoft Office 12 Access Database engine
object up to the top as high as possible (in the #3 position).
 
T

Tom van Stiphout

On Mon, 13 Apr 2009 06:53:52 +0800, "Aceware" <[email protected]>
wrote:

I cannot reproduce your issue. This is what I tried:
Access 2007 Northwind sample application
Form: "Order Subform for Order Details"
Private Sub Product_ID_AfterUpdate()
At the bottom of this proc I added:
DoCmd.RunCommand acCmdSaveRecord
RecalcLine Me.Parent.RecordsetClone, Me.RecordsetClone,
Me.Parent.Bookmark, Me.Bookmark

And in the same module I added:
Private Sub RecalcLine(ByRef rsParent As DAO.Recordset, ByRef rsChild
As DAO.Recordset, ByVal varParentBookmark As Variant, ByVal
varChildBookmark As Variant)
Debug.Print rsParent.Name
Debug.Print rsChild.Name
End Sub

It correctly printed some information about the two recordsets passed
in.

What's different with your situation?

-Tom.
Microsoft Access MVP
 
A

Aceware

Many thanks for helping :)

Does your code correctly compile in 2007?
Yes


Remember, if some error occurs, and then all your variables do loose their
value unless you have error code handling.
Click to expand...

it's the first crash
Also, go tools->references and REMOVE the ADO reference

(Microsoft ActiveX data object 2.5) (or something similar....

the above assumes that you not using ado...

When you do the above, you should then do a compact and repair..and exit
your application..and then re-enter....
Click to expand...

Thanks - tried it but no luck
You can also "force" the data type parameter in:

Public Sub RecalcLine (rstParent as Dao.ReocrdSet, rstChild as
Dao.RecordSet....
Thanks - tried it but no luck

So, "force" the reocrdset type if you having problems with the

me.Parent.ReocrdSetClone

The above works just fine for me..but, it DOES fail if you have a ADO
reference in the tools->references in the code window..

So, nothing has changed in terms of VBA..and I converted access 97, and
access 2003 applications with 30,000+ lines of code into 2007 and it works
fine....

So, it not the coding that needs to be changed here...it is your
references.

You can also try moving up the Microsoft Office 12 Access Database engine
object up to the top as high as possible (in the #3 position).
Click to expand...

Tried that (had to remove the dao reference first) - no luck
 
A

Aceware

Thanks for helping Tom

The only other issue I can think of is that I am using the Bennet-Tec
tlist8.ocx - I need to check if there is a different version for 2007.
It is the last entry in my references though.

Tony Epton

Tom van Stiphout said:
I cannot reproduce your issue. This is what I tried:
Access 2007 Northwind sample application
Form: "Order Subform for Order Details"
Private Sub Product_ID_AfterUpdate()
At the bottom of this proc I added:
DoCmd.RunCommand acCmdSaveRecord
RecalcLine Me.Parent.RecordsetClone, Me.RecordsetClone,
Me.Parent.Bookmark, Me.Bookmark

And in the same module I added:
Private Sub RecalcLine(ByRef rsParent As DAO.Recordset, ByRef rsChild
As DAO.Recordset, ByVal varParentBookmark As Variant, ByVal
varChildBookmark As Variant)
Debug.Print rsParent.Name
Debug.Print rsChild.Name
End Sub

It correctly printed some information about the two recordsets passed
in.

What's different with your situation?

-Tom.
Microsoft Access MVP

Client has just gone to latest patched office 2007 (without discussing with
me).
I have a quoting application that was working well in 2003.

I have a parent / child pair of forms where after changing any field in the
detailed
task list in the subform, calls a recalc function
eg

sub Technician_AfterUpdate()
docmd.runcommand accmdSaveRecord
RecalcLine me.parent.recordsetclone,
me.recordsetclone,me.parent.bookmark, me.bookmark
end sub

I now get :

"Object invalid or no longer set "

In the called subroutine when referencing the second parameter.

I am now working my way through the code replacing the recordsets with
explicit code such as

set rsHeader = currentdb().openrecordset("select * from [tbl Trials] where
TrialId = " & lngTrialId,dbopendynaset,dbseechanges)
set rsDetail = currentdb().openrecordset("select * from [tbl Trial Tasks]
where TrialId = " & lngTrialId, dbopendynaset, dbseechanges)

But this is very tedious and is producing other side effects, as well as
running slower.

I am contemplating telling the client to roll back to office 2003 or else
find another programmer.

Please, I am desperate for any help.

Many thanks in advance
Tony Epton
Click to expand...
Click to expand...
 
A

Albert D. Kallal

Aceware said:
Thanks for helping Tom

The only other issue I can think of is that I am using the Bennet-Tec
tlist8.ocx - I need to check if there is a different version for 2007.
It is the last entry in my references though.
Click to expand...

try removing the

Office 12 Access Database engine

remove the check mark...then close the reference screen...

Now, add the reference back by re-opening the reference screen. move the
reference up to the 3rd spot...

Please do post back if this fixed it....
 
A

agcook

I think this thread may have the answer I need, as well. My reports
from ACCESS 2003 had a textbox in the page footer with the following:
=[CurrentDB].[Name] & " :: " & [CurrentObjectName]. This kept track
of which report was run when people called to ask about reports.

Our office just upgraded to Office 2007 and ACCESS 2007 has been acting
very strangely. At first simple text functions (TRIM, LEFT, etc.) were
not recognized. This was fixed by unchecking the reference to a utility
library that, apparently, hasn't been used since ACCESS 95. But, I
can't seem to get the function to return the database name. I can get
the report name from [CurrentObjectName] but not the other.

Does anybody know what I have to tweak to get everything that was
working in ACCESS 2003 to work in ACCESS 2007?

Thank you,
Alex Cook
 
D

Douglas J. Steele

It's just fluke that =[CurrentDB].[Name] used to work: it should actually be
=CurrentDB().Name
 
A

agcook

Chris O'C via AccessMonster.com;361611]
Use this formula:
=[Currentproject].[Name] & " :: " & [CurrentObjectName]
Chris


Chris, and by extension CodeCage:

In the immortal words of Gomer Pyle, "Thank You! Thank You! Thank
You!!!" This did the trick! I posted my question, met my wife for
lunch, came back to the office and the answer was waiting for me. You
have no idea how frustrating this has been. Chris, if you were here in
Tucson, I'd take you out for a pint of the best our local Nimbus brewery
has to offer.

Thanks, too, to CodeCage.

Alex Cook
 

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

Treeview error 35602 in Access 2007 1

Top