Access closes unexpectedly when setting Form.Recordset

[Simon Lampen]

Whenever I set the Recordset object of a Form that has a subform, or the
recordset of a SubForm access closes unexpectedly.

We are in the final stages (or may be not ) of porting our access
solution to make use of the Access 2007 runtime but we have many forms that
use this coding pattern.

Could this relate to the bug outlined in
http://support.microsoft.com/kb/927536 but not in layout view?? One imagines
that when you set the recordset object that all the controls on the form must
be rebound and this may cause the Control Source bug to come into play. In
any case I would appreciate any feedback on if this is a known issue or not,
and if so, is there a hot fix available or what might be a timeline.

 

[Allen Browne]

IME, Access has always been a bit flakey when you set the Recordset of a
form, particularly where the Recordset could be created/destroyed during the
operation (which can be an issue with subforms, where Access tries to handle
the LinkMasterFields/LinkChildFields.)

Is there any chance of changing the RecordSource instead of assigning a
Recordset? As I say, that's the only way I have been able to get Access
stable. Consequently it's not something I've tested in A2007. I doubt the
issue is as discussed in kb 927356. That's a major flaw in A2007, but I've
observed it in reports only, not forms.

 

[Wei Lu [MSFT]]

Hello Simon,

I understand that you set the record set property of a Form and you could not change it and get the
error message.

I would like to get the VBA code you use to set the record set and load the report to reproduce this
issue.

If you get the same synptom of the KB article you get, my suggestion is that you need to load the
report in Report View instead of Print Preview.

Hope this helps.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Simon Lampen]

Thanks Allen and Wei,

Yes, recordset handling and binding has never been great, but we are trying
to ( slowly ) evolve our solutions to have more detail in code and less
detail in form and report properties for maintenance and future portability,
also we are trying to maintain one code base for ADPs and MDBs.

The real issue here is that these forms and Data Binding work in Access 2003
but crash in Access 2007

Anyway code snippet for you Wei:

Private Sub Form_Current() 'Master Form
'Filter FormRecordset
If IsDBNull(Me.txtOrdDspNum.value) Then Exit Sub

Dim rs As ADODB.recordset
Set rs = Me.SubOrdDspEnt.Form.recordset
rs.Filter = "OrdDspNum=" & Me.OrdDspNum
'in earlier versions of Access it seemed sufficient to deallocate the
recordset first
'to avoid a crash...but not in this case
Set Me.SubOrdDspEnt.Form.recordset = Nothing
'CRASH when we reassign the sub report recordset
Set Me.SubOrdDspEnt.Form.recordset = rs

Set rs = Nothing
End Sub

Allen, you got me thinking that I may have left the Child Master Properties
set and that might be causing the issues you mentioned so I removed those
settings but to no avail. You can see that the Master_Form_Current event
above was meant to replace this internal master\child filtering anyway.

Cheers
Simon

 

[Allen Browne]

Okay, here's a couple more suggestions:

It's always worth making sure Name AutoCorrect is off:
http://allenbrowne.com/bug-03.html

You may like to programmatically best LinkMasterFields/LinkChildFields again
after assigning the Recordset (if you can actually get that far.)

Realistically, though, I don't really expect them to work. I don't have any
way to guarantee Access won't crash when you assign the Recordset to the
form in the way you are doing. I suspect you would have to take a different
approach to get a stable application.

Wei Lu's suggestions seem to apply to a report rather than a form. From your
content, I understood you to be talking about a form, though the kb you
referenced was about reports, and you posted to a Reports group, so perhaps
I was wrong.

 

[Simon Lampen]

Thanks Allen,
I will try setting the Name AutoCorrect off, unfortunately I don't get far
enough before crashing to programmatically set the
LinkMasterFields/LinkChildFields propterties.
You are right, doh!, I am posting in the wrong group , and lastly the KB
article refers to both report and form controls admittedly in layout view but
I am grasping at straws at the moment.
KB title: "Access 2007 may close unexpectedly when you try to change the
Control Source property for a control that is on a report or on a form in
Layout view"

The dream: It would be great if recordset use could became more stable, the
feature is there and begging to be used but is somewhat redundant if it
doesn't work, disconnect recordset also have never quite worked either . I
have whole libraries of helper function for Recordset execution, updating and
manipulation etc which can't be used as efficiently without being able to
assign them to objects recordset properties.
The pipe(?) dream: to be able to bind to ADO.NET DataSets, just think
(seamless) Web Service data with no conversion... . Access is a great UI
design environment and I want to be able to keep using it productively.

 

[Simon Lampen]

To anyone who is interested I have cracked it with a small workaround. Still
it would be good to have someone tidy this up internally.
SYMPTOMS:
When setting the recordset property of a SubForm, you may find that Access
2007 quits unexpectedly. This seems only to occur during the loading of the
Parent form.

WORKAROUND:
Add a boolean flag to indicate that the form is loading as a private field
to the ParentForm and set this to true while you are binding and loading the
Parent and Child recordsets, then set it to false once finished and then do
any filtering or recordset work with the child recordset object at that point.
Any code that tries to set the recordset of the child subform during this
load process you should exit or step over this code at runtime until the
loading is complete then you can refresh or recall this code afterwards
without access crashing.

EXAMPLE:
Private isLoading As Boolean

Private Sub Form_Current()
If (isLoading) Then _
Exit Sub

'Filter SubForm Recordset to mimic LinkChildFields, LinkMasterFields
'Only if there data in this record
If IsDBNull(Me.txtOrdDspNum) Then _
Exit Sub

Dim rs As ADODB.recordset
Set rs = Me.SubReportOrdDspEnt.Form.Recordset
rs.Filter = "OrdDspNum=" & Me.txtOrdDspNum
'Deallocate the property first or access quits unexpected
' (another area of recordset usage instability in access)
Set Me.SubReportOrdDspEnt.Form.recordset = Nothing
'reassign the filtered recordset
Set Me.SubReportOrdDspEnt.Form.recordset = rs
Set rs = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
'flag that we are loading
isLoading = True

'call our Data Binding method that will do all our binding, this method
causes the
'Form_Current event to fire, which normally would have crashed access
Databind

'switch off the flag
isLoading = False

' Everything is now stable after loading so we can safely
' filter the child recordset by invoking the Form_Current event
' without fear of crashing access and with the isLoading flag set to false
Form_Current

'Use form as normal
End Sub

Sub Databind()
Dim cn As ADODB.connection
Set cn = CurrentProject.AccessConnection

'Call my helper function to return a connection object
Set Me.Recordset =
AdoDbHelper.ExecuteRecordset3(cn,Adodb.CommandTypeEnum.adCmdText, "SELECT *
FROM vwfOrdDsp")

Set Me.Crr.recordset =
AdoDbHelper.ExecuteRecordset3(cn,Adodb.CommandTypeEnum.adCmdText, "SELECT *
FROM vwlCrr")

Set Me.StkLoc.recordset =
AdoDbHelper.ExecuteRecordset3(cn,Adodb.CommandTypeEnum.adCmdText, "SELECT *
FROM vwlStkLoc")
Set cn = Nothing

End Sub

 

[Wei Lu [MSFT]]

Hi Simon,

I would like to get the Access file from you to reproduce the issue on my side.

I did not get information about the plan to enable ADO.NET in access 2007. I will performing further
research on this.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Wei Lu [MSFT]]

Hello Simon,

The product team told me that currently Access did not support ADO.NET connection. You still need
to use the ADO to connect to the datasource.


Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Wei Lu [MSFT]]

Hi ,

How is everything going? Please feel free to let me know if you need any
assistance.

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.