|
|
I am currently testing an older Access project for compatibility with Access
2007, SP1 prior to a roll-out.
I'm experiencing a problem with a complex form that fails to open and
crashes Access 2007. It runs fine on Access 2003. The database is SQL Server
2000. Using references to ADO 2.5, 2.7 or 2.8 makes no difference.
The form has 6 tabbed pages. On one of the tabbed pages there is a sub-form.
That sub-form has 2 tabbed pages and one of those tabbed pages has 5
sub-forms. The form has about 23 sub-forms in all.
A routine in a loop binds an ADO Recordset to each of the sub-forms using
the expression:
Set frm.Form.Recordset = rs
The problem begins when I get to the 5 sub-forms on the nested tabbed page.
I know the recordsets are created. For the first two, the expression appears
to execute but the value of frm.Form.Recordset is Nothing and there is no
crash. On the third, Access 2007 crashes (outside the debugger) or hangs
(inside the debugger). Ending task on the latter occasionally reboots the
machine (XP Pro, SP2).
This project was created (upsizing an earlier .MDB file) under Office XP and
migrated to Office 2003 with no problem, although I've had an issue recently
with a couple of controls (textbox and combobox) suddenly getting "lost" on
the form (i.e. they couldn't be located by Access even though they were
recognized in the debugger). In this case, I recreated these controls from
scratch and the project worked fine afterwards.
Recreating the crashing sub-form from scratch has not solved the problem.
Nor has re-creating the project from scratch and importing all the objects.
I'm open to any suggestions.
Thanks in advance,
John
|
|
I don't see the purpose of using ADP instead of MDB if you manually set the
frm.form.Recordset to a recordset. While the best option would be that
Acces 2007 doesn't crash, in your case, you should try with regular forms'
and subforms' recordsets; with the forms and subforms data sources pointing
toward a stored procedure or a Select statement.
Also, it's possible that the problem comes from the code/method used to
create these external recordsets but you give no detail on this matter.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"John" <CharmNewton[ at ]msn.com> wrote in message
news:63880978-F14C-4E82-899A-270AE49F6CD1[ at ]microsoft.com...
[Quoted Text] >I am currently testing an older Access project for compatibility with
>Access 2007, SP1 prior to a roll-out.
>
> I'm experiencing a problem with a complex form that fails to open and
> crashes Access 2007. It runs fine on Access 2003. The database is SQL
> Server 2000. Using references to ADO 2.5, 2.7 or 2.8 makes no difference.
>
> The form has 6 tabbed pages. On one of the tabbed pages there is a
> sub-form. That sub-form has 2 tabbed pages and one of those tabbed pages
> has 5 sub-forms. The form has about 23 sub-forms in all.
>
> A routine in a loop binds an ADO Recordset to each of the sub-forms using
> the expression:
>
> Set frm.Form.Recordset = rs
>
> The problem begins when I get to the 5 sub-forms on the nested tabbed
> page.
>
> I know the recordsets are created. For the first two, the expression
> appears to execute but the value of frm.Form.Recordset is Nothing and
> there is no crash. On the third, Access 2007 crashes (outside the
> debugger) or hangs (inside the debugger). Ending task on the latter
> occasionally reboots the machine (XP Pro, SP2).
>
> This project was created (upsizing an earlier .MDB file) under Office XP
> and migrated to Office 2003 with no problem, although I've had an issue
> recently with a couple of controls (textbox and combobox) suddenly getting
> "lost" on the form (i.e. they couldn't be located by Access even though
> they were recognized in the debugger). In this case, I recreated these
> controls from scratch and the project worked fine afterwards.
>
> Recreating the crashing sub-form from scratch has not solved the problem.
> Nor has re-creating the project from scratch and importing all the
> objects.
>
> I'm open to any suggestions.
>
> Thanks in advance,
>
> John
|
|
The ADO code that creates the recordsets is pretty standard--it could have
been copied and pasted out of the Help example. The SQL statements that
populate these recordsets are stored in an array which is initialized at
startup.
The aplication was upsized due to poor performance over a wide-area network.
As I mentioned it runs fine under Access 2003.
Is there any behavior differences with sub-forms and tabbed pages between
Access 2003 and Access 2007? It looks like the sub-forms are lost on the
form or perhaps the tabbed page.
John
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OZFRlNXMJHA.3496[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] >I don't see the purpose of using ADP instead of MDB if you manually set the
>frm.form.Recordset to a recordset. While the best option would be that
>Acces 2007 doesn't crash, in your case, you should try with regular forms'
>and subforms' recordsets; with the forms and subforms data sources pointing
>toward a stored procedure or a Select statement.
>
> Also, it's possible that the problem comes from the code/method used to
> create these external recordsets but you give no detail on this matter.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "John" <CharmNewton[ at ]msn.com> wrote in message
> news:63880978-F14C-4E82-899A-270AE49F6CD1[ at ]microsoft.com...
>>I am currently testing an older Access project for compatibility with
>>Access 2007, SP1 prior to a roll-out.
>>
>> I'm experiencing a problem with a complex form that fails to open and
>> crashes Access 2007. It runs fine on Access 2003. The database is SQL
>> Server 2000. Using references to ADO 2.5, 2.7 or 2.8 makes no difference.
>>
>> The form has 6 tabbed pages. On one of the tabbed pages there is a
>> sub-form. That sub-form has 2 tabbed pages and one of those tabbed pages
>> has 5 sub-forms. The form has about 23 sub-forms in all.
>>
>> A routine in a loop binds an ADO Recordset to each of the sub-forms using
>> the expression:
>>
>> Set frm.Form.Recordset = rs
>>
>> The problem begins when I get to the 5 sub-forms on the nested tabbed
>> page.
>>
>> I know the recordsets are created. For the first two, the expression
>> appears to execute but the value of frm.Form.Recordset is Nothing and
>> there is no crash. On the third, Access 2007 crashes (outside the
>> debugger) or hangs (inside the debugger). Ending task on the latter
>> occasionally reboots the machine (XP Pro, SP2).
>>
>> This project was created (upsizing an earlier .MDB file) under Office XP
>> and migrated to Office 2003 with no problem, although I've had an issue
>> recently with a couple of controls (textbox and combobox) suddenly
>> getting "lost" on the form (i.e. they couldn't be located by Access even
>> though they were recognized in the debugger). In this case, I recreated
>> these controls from scratch and the project worked fine afterwards.
>>
>> Recreating the crashing sub-form from scratch has not solved the problem.
>> Nor has re-creating the project from scratch and importing all the
>> objects.
>>
>> I'm open to any suggestions.
>>
>> Thanks in advance,
>>
>> John
>
>
|
|
|
[Quoted Text] > The aplication was upsized due to poor performance over a wide-area
> network. As I mentioned it runs fine under Access 2003.
If you are directly binding your recordsets to your forms/subforms, I don't
see why using an ADP project should make these going faster. In my opinion,
you should see the exact same speed under this case scenario with or without
an ADP; so you should either keep your MDB file or, if you want to go the
ADP road, go for it for good; without stopping half-way.
If you can't solve your speed problem; another solution would be to remote
desktop; using Terminal Server or some dedicated workstations (these
workstations could be virtualized). This is usually the easiest solution
for accessing a database over the WAN.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"John" <CharmNewton[ at ]msn.com> wrote in message
news:OF$LjArMJHA.468[ at ]TK2MSFTNGP06.phx.gbl...
> The ADO code that creates the recordsets is pretty standard--it could have
> been copied and pasted out of the Help example. The SQL statements that
> populate these recordsets are stored in an array which is initialized at
> startup.
>
> The aplication was upsized due to poor performance over a wide-area
> network. As I mentioned it runs fine under Access 2003.
>
> Is there any behavior differences with sub-forms and tabbed pages between
> Access 2003 and Access 2007? It looks like the sub-forms are lost on the
> form or perhaps the tabbed page.
>
> John
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:OZFRlNXMJHA.3496[ at ]TK2MSFTNGP04.phx.gbl...
>>I don't see the purpose of using ADP instead of MDB if you manually set
>>the frm.form.Recordset to a recordset. While the best option would be
>>that Acces 2007 doesn't crash, in your case, you should try with regular
>>forms' and subforms' recordsets; with the forms and subforms data sources
>>pointing toward a stored procedure or a Select statement.
>>
>> Also, it's possible that the problem comes from the code/method used to
>> create these external recordsets but you give no detail on this matter.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> "John" <CharmNewton[ at ]msn.com> wrote in message
>> news:63880978-F14C-4E82-899A-270AE49F6CD1[ at ]microsoft.com...
>>>I am currently testing an older Access project for compatibility with
>>>Access 2007, SP1 prior to a roll-out.
>>>
>>> I'm experiencing a problem with a complex form that fails to open and
>>> crashes Access 2007. It runs fine on Access 2003. The database is SQL
>>> Server 2000. Using references to ADO 2.5, 2.7 or 2.8 makes no
>>> difference.
>>>
>>> The form has 6 tabbed pages. On one of the tabbed pages there is a
>>> sub-form. That sub-form has 2 tabbed pages and one of those tabbed pages
>>> has 5 sub-forms. The form has about 23 sub-forms in all.
>>>
>>> A routine in a loop binds an ADO Recordset to each of the sub-forms
>>> using the expression:
>>>
>>> Set frm.Form.Recordset = rs
>>>
>>> The problem begins when I get to the 5 sub-forms on the nested tabbed
>>> page.
>>>
>>> I know the recordsets are created. For the first two, the expression
>>> appears to execute but the value of frm.Form.Recordset is Nothing and
>>> there is no crash. On the third, Access 2007 crashes (outside the
>>> debugger) or hangs (inside the debugger). Ending task on the latter
>>> occasionally reboots the machine (XP Pro, SP2).
>>>
>>> This project was created (upsizing an earlier .MDB file) under Office XP
>>> and migrated to Office 2003 with no problem, although I've had an issue
>>> recently with a couple of controls (textbox and combobox) suddenly
>>> getting "lost" on the form (i.e. they couldn't be located by Access even
>>> though they were recognized in the debugger). In this case, I recreated
>>> these controls from scratch and the project worked fine afterwards.
>>>
>>> Recreating the crashing sub-form from scratch has not solved the
>>> problem. Nor has re-creating the project from scratch and importing all
>>> the objects.
>>>
>>> I'm open to any suggestions.
>>>
>>> Thanks in advance,
>>>
>>> John
>>
>>
>
>
|
|
My apologies for not getting back sooner, but I cannot access this newsgroup
from work.
I did manage to find a solution to the problem after spending much time with
the debugger, although I'm not sure why three sub-forms failed to bind their
recordsets without crashing Access while the other three failed
spectacularly. I saw that the initialization of a major sub-form (which had
a tab control with two pages) fires that sub-form's Current event which was
used to retrieve the data for the 6 additional sub-forms on those two pages.
In Access 2003 this didn't create a problem, but something about the object
creation sequence or object location context in Access 2007 is different,
Postponing the databinding until further in the initialization process of
the main form solved the problem. The line
frm.Form.Recordset
would seem to mean something different when executing from within the
context of a sub-form as opposed to the main form (even though the debugger
had no difficulty identifying the sub-form). From the latter, the Recordset
property is visible and the recordset binds. From the former, it doesn't.
Perhaps in Access 2003 the behavior was the same, only it was handled more
gracefully. I haven't spent time debugging the code in Access 2003 as it
wasn't failing, but may take a look just for my own satisfaction.
I'm confused by the rest of your response. This application has a long
heritage. Originally it was a combined Access/Jet database which was later
split into an Access front-end and a separate Jet back-end using linked
tables. The back-end was upsized SQL Server 2000 about 4 years ago and a
decision was made to go with an Access project and ADO rather than use ODBC.
I was not involved in the upsizing, but Microsoft and most authorities were
recommending ADO and Access data projects at that time. Those who have used
the application over the years stated the upsizing was a vast improvement. I
find it strange that Microsoft is now recommending .ACCDB front-ends using
ODBC to connect to SQL Server, since ODBC is a deprecated technology.
John
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:edJO825MJHA.5648[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] >> The aplication was upsized due to poor performance over a wide-area
>> network. As I mentioned it runs fine under Access 2003.
>
> If you are directly binding your recordsets to your forms/subforms, I
> don't see why using an ADP project should make these going faster. In my
> opinion, you should see the exact same speed under this case scenario with
> or without an ADP; so you should either keep your MDB file or, if you want
> to go the ADP road, go for it for good; without stopping half-way.
>
> If you can't solve your speed problem; another solution would be to remote
> desktop; using Terminal Server or some dedicated workstations (these
> workstations could be virtualized). This is usually the easiest solution
> for accessing a database over the WAN.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
|
|
"John" <CharmNewton[ at ]msn.com> wrote in message
news:B8DEE38E-25AE-4771-BFDF-9F1F164F80DF[ at ]microsoft.com...
[Quoted Text] > My apologies for not getting back sooner, but I cannot access this
> newsgroup from work.
>
> I did manage to find a solution to the problem after spending much time
> with the debugger, although I'm not sure why three sub-forms failed to
> bind their recordsets without crashing Access while the other three failed
> spectacularly. I saw that the initialization of a major sub-form (which
> had a tab control with two pages) fires that sub-form's Current event
> which was used to retrieve the data for the 6 additional sub-forms on
> those two pages. In Access 2003 this didn't create a problem, but
> something about the object creation sequence or object location context in
> Access 2007 is different, Postponing the databinding until further in the
> initialization process of the main form solved the problem. The line
>
> frm.Form.Recordset
>
> would seem to mean something different when executing from within the
> context of a sub-form as opposed to the main form (even though the
> debugger had no difficulty identifying the sub-form). From the latter, the
> Recordset property is visible and the recordset binds. From the former, it
> doesn't.
I don't really understand how you are using this piece of code into your VBA
code but obviously, the name "frm" is the name of a control (form control)
who does exist on the main form but don't exist on the subform. I don't see
how you could make a reference to this control (located only on the parent
form) directly from the subform; without making first a reference to the
parent form. The fact that this work in Access 2003 is probably a bug in my
opinion that has been corrected in A2007.
The debugger knows it probably because it keep the contextual (environment)
information of the variable.
> Perhaps in Access 2003 the behavior was the same, only it was handled more
> gracefully. I haven't spent time debugging the code in Access 2003 as it
> wasn't failing, but may take a look just for my own satisfaction.
>
> I'm confused by the rest of your response. This application has a long
> heritage. Originally it was a combined Access/Jet database which was later
> split into an Access front-end and a separate Jet back-end using linked
> tables. The back-end was upsized SQL Server 2000 about 4 years ago and a
> decision was made to go with an Access project and ADO rather than use
> ODBC. I was not involved in the upsizing, but Microsoft and most
> authorities were recommending ADO and Access data projects at that time.
> Those who have used the application over the years stated the upsizing was
> a vast improvement. I find it strange that Microsoft is now recommending
> .ACCDB front-ends using ODBC to connect to SQL Server, since ODBC is a
> deprecated technology.
>
> John
>
What I don't understand is why you are making a direct assignation of the
recordset rs to the property Me.Recordset - using the code « Set
frm.Form.Recordset = rs » instead of using standard record sources such as a
Select query or calling a stored procdure.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
|
|
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uMhSxWFOJHA.588[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] >
> What I don't understand is why you are making a direct assignation of the
> recordset rs to the property Me.Recordset - using the code « Set
> frm.Form.Recordset = rs » instead of using standard record sources such as
> a Select query or calling a stored procdure.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
A sub-form itself doesn't expose a Recordset property, but as it is a form
the Recordset property can be accessed through the Form property of the
sub-form. So frm refers to the sub-form, Form to the sub-from's Form
property. The data in the recordset is then bound to the datasheet for
display and manipulation. The data stored in the recordset is retrieved by
an SQL statement which happens to be stored in an array which is assigned to
the CommandText property and executed. Writing this code as a stored
procedure is another possibility, but the pressures of getting the upsizing
done as quickly as possible led to the developer falling back on his VB6
experience to initialize the 20+ sub-forms from within a loop looking up the
SQL statements in the array and using standard boilerplate code (as I
mentioned, you can find this ADO code in the Help system) to retrieve the
data.
Although I maintain this project, my background isn't Access. I've talked
with a veteran Access developer who mentioned that the syntax for
referencing sub-forms is convoluted, possibly because Access uses a form to
contain other forms. It seems odd for an object at the same level of the
hierarchy to contain like objects, but the designers of Access saw this as
desirable and have made it work.
This application will be ticketed fro a re-write, possibly within the next
year or two and will have the opportunity to modernize the architecture.
John
|
|
"John" <CharmNewton[ at ]msn.com> wrote in message
news:eCOI$TEPJHA.4312[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:uMhSxWFOJHA.588[ at ]TK2MSFTNGP06.phx.gbl...
>>
>> What I don't understand is why you are making a direct assignation of the
>> recordset rs to the property Me.Recordset - using the code « Set
>> frm.Form.Recordset = rs » instead of using standard record sources such
>> as a Select query or calling a stored procdure.
>>
>
> A sub-form itself doesn't expose a Recordset property, but as it is a form
> the Recordset property can be accessed through the Form property of the
> sub-form. So frm refers to the sub-form, Form to the sub-from's Form
> property. The data in the recordset is then bound to the datasheet for
> display and manipulation. The data stored in the recordset is retrieved by
> an SQL statement which happens to be stored in an array which is assigned
> to the CommandText property and executed. Writing this code as a stored
> procedure is another possibility, but the pressures of getting the
> upsizing done as quickly as possible led to the developer falling back on
> his VB6 experience to initialize the 20+ sub-forms from within a loop
> looking up the SQL statements in the array and using standard boilerplate
> code (as I mentioned, you can find this ADO code in the Help system) to
> retrieve the data.
This might be the heart of your problem: you are coding an ADP project like
if it was a VB6 application. If your programmer want to keep coding in VB6,
he should have kept the VB6 IDE to develop the application instead of
switching to ADP.
> Although I maintain this project, my background isn't Access. I've talked
> with a veteran Access developer who mentioned that the syntax for
> referencing sub-forms is convoluted, possibly because Access uses a form
> to contain other forms. It seems odd for an object at the same level of
> the hierarchy to contain like objects, but the designers of Access saw
> this as desirable and have made it work.
Nothing strange in that. A lot of modern frameworks give you the
possibility of having a collection of objects of the save level inside
another object. Probably that almost anyone doing object orienting
programming see that on a regular basis; especially for some high level
objects like GUI objects. I'm surprised to see that you find this
surprising.
> This application will be ticketed fro a re-write, possibly within the next
> year or two and will have the opportunity to modernize the architecture.
Very good idea indeed but don't forget to tell the next programmer to leave
behind VB6 for good.
> John
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
|
|
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23Dp2aZFPJHA.3448[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > "John" <CharmNewton[ at ]msn.com> wrote in message
> news:eCOI$TEPJHA.4312[ at ]TK2MSFTNGP06.phx.gbl...
>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
>> wrote in message news:uMhSxWFOJHA.588[ at ]TK2MSFTNGP06.phx.gbl...
>>>
>>> What I don't understand is why you are making a direct assignation of
>>> the recordset rs to the property Me.Recordset - using the code « Set
>>> frm.Form.Recordset = rs » instead of using standard record sources such
>>> as a Select query or calling a stored procdure.
>>>
>>
>> A sub-form itself doesn't expose a Recordset property, but as it is a
>> form the Recordset property can be accessed through the Form property of
>> the sub-form. So frm refers to the sub-form, Form to the sub-from's Form
>> property. The data in the recordset is then bound to the datasheet for
>> display and manipulation. The data stored in the recordset is retrieved
>> by an SQL statement which happens to be stored in an array which is
>> assigned to the CommandText property and executed. Writing this code as a
>> stored procedure is another possibility, but the pressures of getting the
>> upsizing done as quickly as possible led to the developer falling back on
>> his VB6 experience to initialize the 20+ sub-forms from within a loop
>> looking up the SQL statements in the array and using standard boilerplate
>> code (as I mentioned, you can find this ADO code in the Help system) to
>> retrieve the data.
>
> This might be the heart of your problem: you are coding an ADP project
> like if it was a VB6 application. If your programmer want to keep coding
> in VB6, he should have kept the VB6 IDE to develop the application instead
> of switching to ADP.
>
No, the heart of the problem was the changed behavior when initializing a
group of sub-froms from another sub-form's Current() event in Access 2007.
The earlier developer needed a way to retrieve data for more than 20
sub-forms and wrote a generic routine to do it. That routine looks up and
executes SQL statemnents stored in an array. It works correctly and performs
well. How would you do it differently? The programmer didn't have unlimited
funds to get the job done.
>> Although I maintain this project, my background isn't Access. I've talked
>> with a veteran Access developer who mentioned that the syntax for
>> referencing sub-forms is convoluted, possibly because Access uses a form
>> to contain other forms. It seems odd for an object at the same level of
>> the hierarchy to contain like objects, but the designers of Access saw
>> this as desirable and have made it work.
>
> Nothing strange in that. A lot of modern frameworks give you the
> possibility of having a collection of objects of the save level inside
> another object. Probably that almost anyone doing object orienting
> programming see that on a regular basis; especially for some high level
> objects like GUI objects. I'm surprised to see that you find this
> surprising.
>
I just want to be sure you understand my point. Are you aware of frameworks
where a combo box is a collection for other combo boxes or a text box a
collection for other text boxes? That is the relationship of a form to a
sub-form, as the latter is a form in its own right. I imagine the Access
team worked very hard to get this to work as they wanted a very transparent
way of rendering one-to-many data relationships.
John
|
|
|
[Quoted Text] > I just want to be sure you understand my point. Are you aware
> where a combo box is a collection for other combo boxes or a
That is not a very good example, because a form is a container
object. A combo box is not a container object: a text box is not
a container object.
I'm sure that the original developers worked very hard to make
forms container objects. Also, it was very demanding on the
hardware and Operating System at the time: most frameworks
avoided full blown container objects for that reason. Even
Access cheats in one place: a form data sheet is not a collection
of objects, it is a single window with data painted on it to
make it look like a collections of objects.
On the other hand, a form object is already a child object,
sometimes a child of the desktop but normally a child of
the Application, so all of the 'child object' stuff already existed.
And this is a child object where you can have multiple siblings.
Most Windows applications can not run the main application
window as a child, and cannot have siblings, but Access forms
are already subforms of the Application window. That is
not just an analogy, in the 1st and second version, the Access
interface was more-or-less built on the same technology using
form/subform windows inside the main application window.
(The property window is still a subform window, but AFAIK
the technology is no longer shared).
As an aside, look at kb/956054. 4 Files are updated in the hotfix:
Accwiz.dll
Msaccess.exe
Msaexp30.dll
Soa.dll
I don't recognise MsaExp30, but SOA and AccWiz are both
libraries which were originally written in Access Basic and
ran in Access.
However, the interesting thing is that the Access 2.0 developers
did so well, and had such a coherent vision and implementation.
Every version since, including A97 (which is often considered
the best version) has weakened that vision and that implementation.
By the way, the FORM property you are using is a hangover
from Access 2.0, that is, it is part of an object model which
predates COM. All forms had a FORM property, because
the forms weren't proper objects themselves, and both VBA
and the macro language needed something they could reference
as the form object. In the DAO COM object model, the
sub-form DOES have a recordset property. The fact that the
recordset property is missing from the ADP subform, and you
have to use a backward compatible kludge, indicates that the
ADP developers, while doubtless smarter than me, implemented
a lightweight solution, and probably thought they were much
cleverer than their predecessors.
Regarding the fact that your application worked correctly
in Access 2003, and fails in 2007, note that you were just
lucky to start with: most developers abandoned the idea
of assigning recordsets to adp forms, because although it is
a very good idea which we all wanted for a long time, the
implementation, like much of what was new in Access 2000,
was broken and flaky, with problems like you now have.
You may also be lucky that it works at all: several of these
backward-compatibility features not used by many people
have quietly disappeared over the years.
Having said that, the failure in 2007 has all the hallmarks of
another 'optimisation' such as we have been inflicted with
over the last 10 years. Someone has decided to optimise
delay loading of the subforms, and the form objects haven't
been created before they are used. This is a worthwhile
optimisation, because the subform loading sequence is
very sub-optimal and data intensive, doubtless because
fixing it was harder than it looked. The form loading
sequence takes place outside the VBA exception handler
(separation of the VBA and Access components), and
the whole application is closed when there is a memory
fault.
(david)
"John" <CharmNewton[ at ]msn.com> wrote in message
news:%231OWKKkPJHA.3748[ at ]TK2MSFTNGP04.phx.gbl...
>
>> I just want to be sure you understand my point. Are you aware of
frameworks
> where a combo box is a collection for other combo boxes or a text box a
> collection for other text boxes? That is the relationship of a form to a
> sub-form, as the latter is a form in its own right. I imagine the Access
> team worked very hard to get this to work as they wanted a very
transparent
> way of rendering one-to-many data relationships.
>
> John
>
>
>
|
|
The big problem with directlysetting the recordset property of a subform is
what happens or what should happen when you have a continuous form as your
main form; because in this case scenario, the same subform can be repeated
multiple times but each time with a different set of data.
People using ODBC link and passthrough queries have this exact same problem
when dealing with reports because reports often have sub-reports and a
passthrough query cannot be directly used with a subreport.
If we get back to the original problem, my first suggestion was - and
remains - to use the Record Source instead of the Recordset property.
However, some people have the exact same problem for MDB or ACCDB data
file - and not just with ADP - when they are using the Recordset property
and are switching to A2007. A workaround that seems to work is to make sure
that there is not attempt to set the recordset of a subform when setting the
recordset of the main form is not finished:
Dim MainInPopulate as Boolean
Private Sub Form_Open(Cancel As Integer)
MainInPopulate= True
Call PopulateMainForm
MainInPopulate = False
Call PopulateSubForm(Clng(Nz(Me.Text1, 0)))
End Sub
Private Sub Form_Current()
If MainInPopulate = True Then
Exit Sub
End If
Dim lngId As Long
lngId = Nz(Me.Text1, 0)
Call PopulateSubForm(lngId)
End Sub
I would tell that there are other potential problems lurking around; like
the infamous error 3420 when you try to use the RecordsetClone property for
navigating around after a full requery of the form/subforms.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
<david[ at ]epsomdotcomdotau> wrote in message
news:%23WyeOfnPJHA.576[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] >> I just want to be sure you understand my point. Are you aware
>> where a combo box is a collection for other combo boxes or a
>
> That is not a very good example, because a form is a container
> object. A combo box is not a container object: a text box is not
> a container object.
>
> I'm sure that the original developers worked very hard to make
> forms container objects. Also, it was very demanding on the
> hardware and Operating System at the time: most frameworks
> avoided full blown container objects for that reason. Even
> Access cheats in one place: a form data sheet is not a collection
> of objects, it is a single window with data painted on it to
> make it look like a collections of objects.
>
> On the other hand, a form object is already a child object,
> sometimes a child of the desktop but normally a child of
> the Application, so all of the 'child object' stuff already existed.
> And this is a child object where you can have multiple siblings.
> Most Windows applications can not run the main application
> window as a child, and cannot have siblings, but Access forms
> are already subforms of the Application window. That is
> not just an analogy, in the 1st and second version, the Access
> interface was more-or-less built on the same technology using
> form/subform windows inside the main application window.
> (The property window is still a subform window, but AFAIK
> the technology is no longer shared).
>
> As an aside, look at kb/956054. 4 Files are updated in the hotfix:
> Accwiz.dll
> Msaccess.exe
> Msaexp30.dll
> Soa.dll
>
> I don't recognise MsaExp30, but SOA and AccWiz are both
> libraries which were originally written in Access Basic and
> ran in Access.
>
> However, the interesting thing is that the Access 2.0 developers
> did so well, and had such a coherent vision and implementation.
> Every version since, including A97 (which is often considered
> the best version) has weakened that vision and that implementation.
>
> By the way, the FORM property you are using is a hangover
> from Access 2.0, that is, it is part of an object model which
> predates COM. All forms had a FORM property, because
> the forms weren't proper objects themselves, and both VBA
> and the macro language needed something they could reference
> as the form object. In the DAO COM object model, the
> sub-form DOES have a recordset property. The fact that the
> recordset property is missing from the ADP subform, and you
> have to use a backward compatible kludge, indicates that the
> ADP developers, while doubtless smarter than me, implemented
> a lightweight solution, and probably thought they were much
> cleverer than their predecessors.
>
> Regarding the fact that your application worked correctly
> in Access 2003, and fails in 2007, note that you were just
> lucky to start with: most developers abandoned the idea
> of assigning recordsets to adp forms, because although it is
> a very good idea which we all wanted for a long time, the
> implementation, like much of what was new in Access 2000,
> was broken and flaky, with problems like you now have.
> You may also be lucky that it works at all: several of these
> backward-compatibility features not used by many people
> have quietly disappeared over the years.
>
> Having said that, the failure in 2007 has all the hallmarks of
> another 'optimisation' such as we have been inflicted with
> over the last 10 years. Someone has decided to optimise
> delay loading of the subforms, and the form objects haven't
> been created before they are used. This is a worthwhile
> optimisation, because the subform loading sequence is
> very sub-optimal and data intensive, doubtless because
> fixing it was harder than it looked. The form loading
> sequence takes place outside the VBA exception handler
> (separation of the VBA and Access components), and
> the whole application is closed when there is a memory
> fault.
>
>
> (david)
>
>
> "John" <CharmNewton[ at ]msn.com> wrote in message
> news:%231OWKKkPJHA.3748[ at ]TK2MSFTNGP04.phx.gbl...
>>
>>> I just want to be sure you understand my point. Are you aware of
> frameworks
>> where a combo box is a collection for other combo boxes or a text box a
>> collection for other text boxes? That is the relationship of a form to a
>> sub-form, as the latter is a form in its own right. I imagine the Access
>> team worked very hard to get this to work as they wanted a very
> transparent
>> way of rendering one-to-many data relationships.
>>
>> John
>>
>>
>>
>
>
>
|
|
Sorry, I forgot to give credit for the previous workaround; it's from
Giorgio Rancati,
[Office Access MVP].
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%239XGn1JQJHA.1960[ at ]TK2MSFTNGP04.phx.gbl...
[Quoted Text] > The big problem with directlysetting the recordset property of a subform
> is what happens or what should happen when you have a continuous form as
> your main form; because in this case scenario, the same subform can be
> repeated multiple times but each time with a different set of data.
>
> People using ODBC link and passthrough queries have this exact same
> problem when dealing with reports because reports often have sub-reports
> and a passthrough query cannot be directly used with a subreport.
>
> If we get back to the original problem, my first suggestion was - and
> remains - to use the Record Source instead of the Recordset property.
>
> However, some people have the exact same problem for MDB or ACCDB data
> file - and not just with ADP - when they are using the Recordset property
> and are switching to A2007. A workaround that seems to work is to make
> sure that there is not attempt to set the recordset of a subform when
> setting the recordset of the main form is not finished:
>
>
> Dim MainInPopulate as Boolean
>
> Private Sub Form_Open(Cancel As Integer)
>
> MainInPopulate= True
> Call PopulateMainForm
> MainInPopulate = False
>
> Call PopulateSubForm(Clng(Nz(Me.Text1, 0)))
>
> End Sub
>
> Private Sub Form_Current()
>
> If MainInPopulate = True Then
> Exit Sub
> End If
>
> Dim lngId As Long
> lngId = Nz(Me.Text1, 0)
> Call PopulateSubForm(lngId)
>
> End Sub
>
> I would tell that there are other potential problems lurking around; like
> the infamous error 3420 when you try to use the RecordsetClone property
> for navigating around after a full requery of the form/subforms.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> <david[ at ]epsomdotcomdotau> wrote in message
> news:%23WyeOfnPJHA.576[ at ]TK2MSFTNGP04.phx.gbl...
>>> I just want to be sure you understand my point. Are you aware
>>> where a combo box is a collection for other combo boxes or a
>>
>> That is not a very good example, because a form is a container
>> object. A combo box is not a container object: a text box is not
>> a container object.
>>
>> I'm sure that the original developers worked very hard to make
>> forms container objects. Also, it was very demanding on the
>> hardware and Operating System at the time: most frameworks
>> avoided full blown container objects for that reason. Even
>> Access cheats in one place: a form data sheet is not a collection
>> of objects, it is a single window with data painted on it to
>> make it look like a collections of objects.
>>
>> On the other hand, a form object is already a child object,
>> sometimes a child of the desktop but normally a child of
>> the Application, so all of the 'child object' stuff already existed.
>> And this is a child object where you can have multiple siblings.
>> Most Windows applications can not run the main application
>> window as a child, and cannot have siblings, but Access forms
>> are already subforms of the Application window. That is
>> not just an analogy, in the 1st and second version, the Access
>> interface was more-or-less built on the same technology using
>> form/subform windows inside the main application window.
>> (The property window is still a subform window, but AFAIK
>> the technology is no longer shared).
>>
>> As an aside, look at kb/956054. 4 Files are updated in the hotfix:
>> Accwiz.dll
>> Msaccess.exe
>> Msaexp30.dll
>> Soa.dll
>>
>> I don't recognise MsaExp30, but SOA and AccWiz are both
>> libraries which were originally written in Access Basic and
>> ran in Access.
>>
>> However, the interesting thing is that the Access 2.0 developers
>> did so well, and had such a coherent vision and implementation.
>> Every version since, including A97 (which is often considered
>> the best version) has weakened that vision and that implementation.
>>
>> By the way, the FORM property you are using is a hangover
>> from Access 2.0, that is, it is part of an object model which
>> predates COM. All forms had a FORM property, because
>> the forms weren't proper objects themselves, and both VBA
>> and the macro language needed something they could reference
>> as the form object. In the DAO COM object model, the
>> sub-form DOES have a recordset property. The fact that the
>> recordset property is missing from the ADP subform, and you
>> have to use a backward compatible kludge, indicates that the
>> ADP developers, while doubtless smarter than me, implemented
>> a lightweight solution, and probably thought they were much
>> cleverer than their predecessors.
>>
>> Regarding the fact that your application worked correctly
>> in Access 2003, and fails in 2007, note that you were just
>> lucky to start with: most developers abandoned the idea
>> of assigning recordsets to adp forms, because although it is
>> a very good idea which we all wanted for a long time, the
>> implementation, like much of what was new in Access 2000,
>> was broken and flaky, with problems like you now have.
>> You may also be lucky that it works at all: several of these
>> backward-compatibility features not used by many people
>> have quietly disappeared over the years.
>>
>> Having said that, the failure in 2007 has all the hallmarks of
>> another 'optimisation' such as we have been inflicted with
>> over the last 10 years. Someone has decided to optimise
>> delay loading of the subforms, and the form objects haven't
>> been created before they are used. This is a worthwhile
>> optimisation, because the subform loading sequence is
>> very sub-optimal and data intensive, doubtless because
>> fixing it was harder than it looked. The form loading
>> sequence takes place outside the VBA exception handler
>> (separation of the VBA and Access components), and
>> the whole application is closed when there is a memory
>> fault.
>>
>>
>> (david)
>>
>>
>> "John" <CharmNewton[ at ]msn.com> wrote in message
>> news:%231OWKKkPJHA.3748[ at ]TK2MSFTNGP04.phx.gbl...
>>>
>>>> I just want to be sure you understand my point. Are you aware of
>> frameworks
>>> where a combo box is a collection for other combo boxes or a text box a
>>> collection for other text boxes? That is the relationship of a form to a
>>> sub-form, as the latter is a form in its own right. I imagine the Access
>>> team worked very hard to get this to work as they wanted a very
>> transparent
>>> way of rendering one-to-many data relationships.
>>>
>>> John
>>>
>>>
>>>
>>
>>
>>
>
>
|
|
Another suggestion put forward by Armen Stein for a similar problem would be
to add DoEvents calls into the code at various points in order to give
asynchronous processes in Access a chance to catch up with what's happened.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uVTemTKQJHA.576[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > Sorry, I forgot to give credit for the previous workaround; it's from
> Giorgio Rancati,
> [Office Access MVP].
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:%239XGn1JQJHA.1960[ at ]TK2MSFTNGP04.phx.gbl...
>> The big problem with directlysetting the recordset property of a subform
>> is what happens or what should happen when you have a continuous form as
>> your main form; because in this case scenario, the same subform can be
>> repeated multiple times but each time with a different set of data.
>>
>> People using ODBC link and passthrough queries have this exact same
>> problem when dealing with reports because reports often have sub-reports
>> and a passthrough query cannot be directly used with a subreport.
>>
>> If we get back to the original problem, my first suggestion was - and
>> remains - to use the Record Source instead of the Recordset property.
>>
>> However, some people have the exact same problem for MDB or ACCDB data
>> file - and not just with ADP - when they are using the Recordset property
>> and are switching to A2007. A workaround that seems to work is to make
>> sure that there is not attempt to set the recordset of a subform when
>> setting the recordset of the main form is not finished:
>>
>>
>> Dim MainInPopulate as Boolean
>>
>> Private Sub Form_Open(Cancel As Integer)
>>
>> MainInPopulate= True
>> Call PopulateMainForm
>> MainInPopulate = False
>>
>> Call PopulateSubForm(Clng(Nz(Me.Text1, 0)))
>>
>> End Sub
>>
>> Private Sub Form_Current()
>>
>> If MainInPopulate = True Then
>> Exit Sub
>> End If
>>
>> Dim lngId As Long
>> lngId = Nz(Me.Text1, 0)
>> Call PopulateSubForm(lngId)
>>
>> End Sub
>>
>> I would tell that there are other potential problems lurking around; like
>> the infamous error 3420 when you try to use the RecordsetClone property
>> for navigating around after a full requery of the form/subforms.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>>
>> <david[ at ]epsomdotcomdotau> wrote in message
>> news:%23WyeOfnPJHA.576[ at ]TK2MSFTNGP04.phx.gbl...
>>>> I just want to be sure you understand my point. Are you aware
>>>> where a combo box is a collection for other combo boxes or a
>>>
>>> That is not a very good example, because a form is a container
>>> object. A combo box is not a container object: a text box is not
>>> a container object.
>>>
>>> I'm sure that the original developers worked very hard to make
>>> forms container objects. Also, it was very demanding on the
>>> hardware and Operating System at the time: most frameworks
>>> avoided full blown container objects for that reason. Even
>>> Access cheats in one place: a form data sheet is not a collection
>>> of objects, it is a single window with data painted on it to
>>> make it look like a collections of objects.
>>>
>>> On the other hand, a form object is already a child object,
>>> sometimes a child of the desktop but normally a child of
>>> the Application, so all of the 'child object' stuff already existed.
>>> And this is a child object where you can have multiple siblings.
>>> Most Windows applications can not run the main application
>>> window as a child, and cannot have siblings, but Access forms
>>> are already subforms of the Application window. That is
>>> not just an analogy, in the 1st and second version, the Access
>>> interface was more-or-less built on the same technology using
>>> form/subform windows inside the main application window.
>>> (The property window is still a subform window, but AFAIK
>>> the technology is no longer shared).
>>>
>>> As an aside, look at kb/956054. 4 Files are updated in the hotfix:
>>> Accwiz.dll
>>> Msaccess.exe
>>> Msaexp30.dll
>>> Soa.dll
>>>
>>> I don't recognise MsaExp30, but SOA and AccWiz are both
>>> libraries which were originally written in Access Basic and
>>> ran in Access.
>>>
>>> However, the interesting thing is that the Access 2.0 developers
>>> did so well, and had such a coherent vision and implementation.
>>> Every version since, including A97 (which is often considered
>>> the best version) has weakened that vision and that implementation.
>>>
>>> By the way, the FORM property you are using is a hangover
>>> from Access 2.0, that is, it is part of an object model which
>>> predates COM. All forms had a FORM property, because
>>> the forms weren't proper objects themselves, and both VBA
>>> and the macro language needed something they could reference
>>> as the form object. In the DAO COM object model, the
>>> sub-form DOES have a recordset property. The fact that the
>>> recordset property is missing from the ADP subform, and you
>>> have to use a backward compatible kludge, indicates that the
>>> ADP developers, while doubtless smarter than me, implemented
>>> a lightweight solution, and probably thought they were much
>>> cleverer than their predecessors.
>>>
>>> Regarding the fact that your application worked correctly
>>> in Access 2003, and fails in 2007, note that you were just
>>> lucky to start with: most developers abandoned the idea
>>> of assigning recordsets to adp forms, because although it is
>>> a very good idea which we all wanted for a long time, the
>>> implementation, like much of what was new in Access 2000,
>>> was broken and flaky, with problems like you now have.
>>> You may also be lucky that it works at all: several of these
>>> backward-compatibility features not used by many people
>>> have quietly disappeared over the years.
>>>
>>> Having said that, the failure in 2007 has all the hallmarks of
>>> another 'optimisation' such as we have been inflicted with
>>> over the last 10 years. Someone has decided to optimise
>>> delay loading of the subforms, and the form objects haven't
>>> been created before they are used. This is a worthwhile
>>> optimisation, because the subform loading sequence is
>>> very sub-optimal and data intensive, doubtless because
>>> fixing it was harder than it looked. The form loading
>>> sequence takes place outside the VBA exception handler
>>> (separation of the VBA and Access components), and
>>> the whole application is closed when there is a memory
>>> fault.
>>>
>>>
>>> (david)
>>>
>>>
>>> "John" <CharmNewton[ at ]msn.com> wrote in message
>>> news:%231OWKKkPJHA.3748[ at ]TK2MSFTNGP04.phx.gbl...
>>>>
>>>>> I just want to be sure you understand my point. Are you aware of
>>> frameworks
>>>> where a combo box is a collection for other combo boxes or a text box a
>>>> collection for other text boxes? That is the relationship of a form to
>>>> a
>>>> sub-form, as the latter is a form in its own right. I imagine the
>>>> Access
>>>> team worked very hard to get this to work as they wanted a very
>>> transparent
>>>> way of rendering one-to-many data relationships.
>>>>
>>>> John
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
|
|
"John" <CharmNewton[ at ]msn.com> дÈëÏûÏ¢ÐÂÎÅ:eCOI$TEPJHA.4312[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
> wrote in message news:uMhSxWFOJHA.588[ at ]TK2MSFTNGP06.phx.gbl...
>>
>> What I don't understand is why you are making a direct assignation of the
>
>
|
|
|