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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
peter57rCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.