works in Access 2002, but not in Access 2007

hello experts,
i wrote a program for a client several years ago in Access 2002 which has worked wonderfully for several years.

now they try to run it in Access 2007, and the form/subform only show ONE record. clicking "next" goes to a new record.

the underlying query shows 1,600 records, and if i run the exact same copy of the program in Access 2002, the forms show 1,600 records.

there's something about Access 2007 that won't allow the forms to show all the records, but it does allow them to show the first record.

1) we're all running XP

2) this "front-end" was built very normally with regular linked tables to a "back-end" mdb across a mapped drive to a server (and on the same drive when on my dev. machine).

3) it uses only ADO code.  there is no DAO anything.
i tried adding the DAO object library to the References, but that didn't solve it.

4) the main form has an embedded subform which is bound to the same query as the main form (i don't remember exactly why i did it that way but it probably has to do with the filtering options i built in to the main form).  
i tried making a twin query and binding the subform to it (thereby removing that design as a possible cause) but the same phenomenon occurs.

5) no other forms, functions, or reports fail.  only this embedded form/subform

i don't know what else to try.

any thoughts?

Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Obviously you should check references to insure none are marked as MISSING. This would have to be done on the target machine of course.

Insure the target machines are fully up to date re: Office and Windows.

If you open the query in 2007, does the query correctly show the records?

Can you show the SQL of that query?

Many people have reported issues with 07 that were resolved by simply doing this:

Build a new, blank db in 2007 (you can use the .mdb format) and import everything to it. Compact, Compile and Compact again.

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
Helen FeddemaCommented:
I agree with LSM (especially the last item).  I wrote a book on Access 2007 and had to create the sample databases during the beta, or very early in the release version, and I have had to recreate some of them since, because of various problems.

If you haven't actually upgraded to Access 2007, you might want to try that too.  Here is a document with some advice about upgrading.
MacRenaAuthor Commented:
hi guys,
thanks for replying.

the underlying query shows 1,600 records, in Access 2002, 2003, and 2007,
        SELECT *
        FROM AKA_CaseHeader
        ORDER BY [AKA_SurgeryDate], [AKA_Time], [AKA_Physician], [AKA_Hospital], [AKA_PatientName];
the query works fine; it's the form that's not working.

no MISSING references
        Visual Basic for Applications
        Microsoft Access 12.0 Object Library
        OLE Automation
        Microsoft Office 12.0 Access database engine Object Library
        Microsoft ActiveX Data Objects 2.8 Library
        Microsoft ADO Ext. 2.8 for DDL and Security
        Microsoft Outlook 12.0 Object Library
        (Microsoft DAO 3.6 Object Library didn't help here so it's not there after the test)

the one thing i realized after i posted this is that there's a subform embedded on the first subform (so 3-deep)
sorry i didn't mention it.  it's a few years old and i forgot it was there.
since no other forms are experiencing this problem, i'm going to look harder there.

i was hoping someone had experienced opening a form with a subform and only seeing the first record.  it's a real goofy problem.  i'm really good at this Access stuff and i can't even identify what the heck is happening, let alone how to solve it.
the form and subform don't see the rest of the records that i KNOW are returned by the underlying query... sheesh!!!

Thanks again,

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

MacRenaAuthor Commented:
ok, it has nothing to do with the subform, and the forms do see all records.
the problem is in the DoCmd command of my Navigation buttons (that are on the subform).

Private Sub cmdPreviousRecord_Click()
    On Error Resume Next
    DoCmd.RunCommand acCmdRecordsGoToPrevious
End Sub

when i take out the "On Error Resume Next", Access throws the error

Runtime Error '2046':
The command or action 'RecordsGoToPrevious" isn't available now.

and when i click the 'Next Record' button, it successfully goes to a NewRecord, but when i click it again, it throws the error.

Runtime Error '2046':
The command or action 'RecordsGoToNext" isn't available now.

so the behavior with the "On Error Resume Next" in there was that it simply ignored my command and LOOKED like the form could only see one record.

i'll have to figure out why Access 2000, 2002, and 2003 have no problem navigating with this command but Access 2007 won't do it.
MacRenaAuthor Commented:
interesting.  if i set focus to the Main form, the error doesn't occur.
clicking the "Previous" button sets focus to the Main form, moves back one record, and the subform follows along very happy.

so all i have to do to solve this is to manipulate focus so it ends up in the field where it was right before the user clicked the "Previus" button.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try saving the form before you move:

If Me.Dirty Then Me.Dirty = False
DoCmd.RunCommand acCmdRecordsGoToPrevious
MacRenaAuthor Commented:
thanks LSMC, but no good.

i resolved it be jumping from the subform to the main form before the DoCmd.
the funny thing is that i can't successfully set the focus back to the subform.
it just ignores the command.  i'm not sure where focus goes, but it doesn't go to the control i declared.

thanks to all 3 for your help.

MacRenaAuthor Commented:
increasing to 150 points to split
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
Visual Basic Classic

From novice to tech pro — start learning today.