Reload of ADO Recordsets - Access Crashing

[gray]

Hi

WinXPPro - SP3
Access 2007
.mbd file
Native Access Db on Local Machine

Sorry to post again so soon but I've an issue with ADO RS's and I'm completely out of ideas. I started to implement these some weeks ago and wish I'd not bothered!
I have main and 6 subforms. All are built as I've seen in various examples:

Option Explicit
Private Frm_RstADO1 As ADODB.Recordset
Private Frm_CnnADO1 As ADODB.Connection
 
OPEN EVENT
----------
Set Frm_CnnADO = CurrentProject.AccessConnection
Set Frm_RstADO = New ADODB.Recordset
 
SQLLine = "SELECT TBL1.* FROM Addrs AS TBL1 "<--- may get JOINED later
With Frm_RstADO
    Set .ActiveConnection = Frm_CnnADO
    .Source = SQLLine
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .Open
End With
 
Set Me.Form.Recordset = Frm_RstADO
 
Set Frm_RstADO = Nothing
Set Frm_CnnADO = Nothing

Each SubForm SELECTs from different tables.
My main form current event co-ordinates the main and subforms as here:-

With My_SubForm.Form
   .Recordset.Filter = adFilterNone       
   .Recordset.Filter = "[Addrs_Unique_No]=" & Me!TBL1_Unique_No <--- main form key
   Set .Recordset = .Recordset
End With

All works perfectly.... until I add a new record into the main form RS. When I have finished doing so I "requery" it with :-

Set me.form.recordset = me.form.recordset (it's not DAO).

This works around all the subforms calling their unload events and then open events.... so they are all re-loaded as they were originally loaded above...
Now, however, the filtering of the subforms or indeed any mention of them e.g.

With My_SubForm.Form
.Recordset.Filter

causes Access to immediately crash?!? Any ideas anyone please?

 

[gray]

Hi

After many hours investigation I think I've fixed this.

For anyone else out there who doesn't want to waste 3 weeks of their lives on trial and error :-

When my main form "source-of-records" is re-built (I use that term to distinguish it from "RecordSource") I read that it was first necessary to clear the form's recordset...

 Set me.form.recordset = nothing.

Then apply the new source e.g.

 Set Frm_CnnADO = CurrentProject.AccessConnection
Set Frm_RstADO = New ADODB.Recordset
 
SQLLine = "SELECT TBL1.* FROM Addrs AS TBL1 WHERE Name='Fred'"  <--- may get JOINED later
With Frm_RstADO
    Set .ActiveConnection = Frm_CnnADO
    .Source = SQLLine
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .Open
End With
 
Set Me.Form.Recordset = Frm_RstADO
 
Set Frm_RstADO = Nothing
Set Frm_CnnADO = Nothing

All the subforms are re-loaded by the "recordset=Nothing", that is to say, the unload events are fired followed by the open events, and then re-loaded again by the "Recordset = Frm_RstADO
" .... Access is still not convinced they are open thus any references to them fails and crashes the form.

For the time being I have made my subform open events public and I call them all after the "Recordset = Frm_RstADO"... so...

call me.my_subform.form.form_open(0)

This has done the trick... no more crashes....

I'm going to see if I can streamline this a little cos' it seems way over the top to me.....

If i get enough monkeys with enough keyboards and enough copies of Access, do you think they would eventually get my project working?

 

[Galaxiom]

There is an easier way.

The filter should be applied to the recordset. Then the form's recordset "requeried" (Me.Recordset = Me.Recordset).

 

[gray]

Hi

Thanks for the reply.

I use a mix of SQL SELECTs and ADO filters... Some of my main_form requirements are quite complex so I use an initial SELECT to get them into the RS... but you are correct and, under the circumstances described above, I'm just going to carry out a set me.recordset=me.recordset instead of building the whole RS again. I've gone through so many iterations of this trying to fix the problem that I'm reviewing the whole thing.

I guess there are two real questions form all this... why Access does not have a current object for the subforms it's just re-loaded? and why it crashes without even a hint of a reason?. Just taken so much time in trial and error.

Thnx again.


that I can just do a me.recordset=me.recordset and not have to build the RS's again from I don't think I could replicate them with just filters. I guess the question really is why Access loads these subforms but doesn't have an object for them without a further load.