MS Access - The object doesn't contain the automation object... Run time error 2766

I have an Access 2000 format database that contains the VBA code

OtherContract = DLookup("[Contract Code]", "TimeSheets", "[Contract Code] <> [frmqryTimeSheets]![Contract Code] AND [Payroll Number] = [frmqryTimeSheets]![Payroll Number] AND [Worked Week No] = [frmqryTimeSheets]![Worked Week No] AND [Sun] > 0 ")

When the database is accessed with Access 2007 I get a run time error 2766 and the message "The object doesn't contain the automation object frmqryTimesheets".  The same code run from a machine running Office XP works perfectly, and it has previously worked perfectly from the Office 2007 machines.  I am not aware of any changes that have been made to the PCs involved.

Can anyone explain why this is happening?   Many thanks in advance.
dedwardsaAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
Hey ho...

How about..(open the form first)
?forms!frmqrytimesheets.name

If there's still a problem, open another form and try its name in the command.

I think we're heading for creating a new blank database and importing everything into it...but just try the above first.
0
 
peter57rCommented:
I don't really see how the code could have ever worked. The syntax is incorrect.
You need...

OtherContract = DLookup("[Contract Code]", "TimeSheets", "[Contract Code] <>" &  [frmqryTimeSheets]![Contract Code] & " AND [Payroll Number] =" &  [frmqryTimeSheets]![Payroll Number] & " AND [Worked Week No] = " & [frmqryTimeSheets]![Worked Week No] & " AND [Sun] > 0 ")
0
 
dedwardsaAuthor Commented:
Thanks, but there must be something else at work..  

I have copied and pasted your corrected query and now get an error 2465, access can't find the field "|" in your query.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
peter57rCommented:
Correction:

othercontract = DLookup("[Contract Code]", "TimeSheets", "[Contract Code] <>" & Forms!frmqryTimeSheets![Contract Code] & " AND [Payroll Number] =" & Forms!frmqryTimeSheets![Payroll Number] & " AND [Worked Week No] = " & Forms!frmqryTimeSheets![Worked Week No] & " AND [Sun] > 0 ")
0
 
dedwardsaAuthor Commented:
I know this is going to sound really ungrateful, but  - that generates error 2450, access can't find the form frmqryTimeSheets  refered to in the macro or VBA...

Sorry, and thanks!
0
 
peter57rCommented:
Is the form open?
0
 
dedwardsaAuthor Commented:
Yes...
0
 
peter57rCommented:
Then it can't be called frmqrytimesheets.

What name appears in the objects list?

If you compile the application does it report errors?
0
 
dedwardsaAuthor Commented:
But it is.  frmqryTimeSheets in the object list, bold as brass.  

As I said, the syntactically incorrect code works fine under XP, it's just 2007 that's giving me grief (which is sad as we've just u/graded all round)...

Thanks again.
0
 
peter57rCommented:
'code works fine under XP'

I'm sorry to have to be so blunt but that is utterly impossible.
The code is fundamentally wrong and would be absolutely certain to produce an error message.
(Probably one that said ' You cancelled the previous operation').

The only explanations for you not getting such an error is that this bit of code was never used or you have error messages suppressed..

There are lots of issues being seen with conversion of docs/apps into Office 2007 and I am at a loss to understand why companies are doing such upgrades without testing first.
But we need to see what can be done about your specific problem.

Open frmqrytimesheets to an existing record.

Go into the Immediate window in VBA and try:
?DLookup("[Contract Code]", "TimeSheets", "[Contract Code] =" & Forms!frmqryTimeSheets![Contract Code] )

(This assumes that Contract code is a number)
It should return the same value to you as is shown in the form.

0
 
dedwardsaAuthor Commented:
Fair comments.  

Anyway, you're not going to like this...

2450, can't find frmqrytimesheets...
0
 
dedwardsaAuthor Commented:
Thanks for the help, I will get back to this in a day or two.   I've gone back to XP to get things rolling for the time being.
0
 
dedwardsaAuthor Commented:
Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.