MacRena
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.Set Focus
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.
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.Set
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.
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!
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.Set Focus
DoCmd.RunCommand acCmdRecordsGoToPrevious
If Me.Dirty Then Me.Dirty = False
Screen.PreviousControl.Set
DoCmd.RunCommand acCmdRecordsGoToPrevious
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
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
ASKER
increasing to 150 points to split
ASKER
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