Link to home
Start Free TrialLog in
Avatar of MacRena
MacRenaFlag for United States of America

asked on

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.

details:
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?

thanks,
mac
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MacRena

ASKER

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,

mac
Avatar of MacRena

ASKER

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
    Screen.PreviousControl.SetFocus
    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.
Avatar of MacRena

ASKER

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.

yea!
Try saving the form before you move:

If Me.Dirty Then Me.Dirty = False
Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdRecordsGoToPrevious
Avatar of MacRena

ASKER

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.

mac
Avatar of MacRena

ASKER

increasing to 150 points to split