Recordset bombs, RecordsetClone works

[Petr Danes]

Then maybe you need to use the old-fashioned way of opening a recordset:

Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Set dbs = CurrentDb
Set rsTable = dbs.OpenRecordset("YOUR TABLE NAME OR SQL")
With rsTable
    .MoveLast
    .MoveFirst
    MsgBox .RecordCount
.Close
End With

I have never used the Form.RecordSet methodology you are trying. I doubt it will work very well or work consistently though especially if you are referencing subforms.

I can do that, but I also need to set the current record of the form. Most of this is taking place in a search function. The user specifies a record indentifier, and the DB searches for a record matching that request. When such a record(s) exists, the subform is supposed to display it. Sometimes the record is in the recordset already displayed, sometimes it is in the DB but not in the current display set, in which case the pass-through query is changed to include that record, sometimes as a singleton, sometimes as part of a larger set, depending on many things. But in all cases, the subform winds up with a recordset containing at least the record(s) that match the search criteria. After finding the record, I want to position the subform's current record pointer to that found record, or the first match if there are several. THAT is where I need to use the .Recordset property of the subform, and THAT is what is crashing. Things like getting the .RecordCount property I can do with the clone, although I don't see why using a clone should have any advantage over the original - seems to me that all it does is add confusion. But positioning the clone does not update the display, which is what I need to do.

 

[Petr Danes]

@LarryE - I frequently refer to form recordsets, including in subforms, and it's not normally troublesome.
I suspect that it's to do with either the underlying recordset changing somehow, which will cause it to become unreadable or the Form_ style references.

I do change the underlying recordset, by changing the text of the pass-through query that feeds the subform. I no longer use the Form_ syntax, after an exchange in a different thread, where it was explained to me that it is not the proper way to refer to such things.

 

[Petr Danes]

@The_Doc_Man - We discussed the use of Form_ references in the other thread, and I thought they were no longer being used, as they can silently open another hidden instance of the form.

The global variables are declared in a code module:

Public gbl_frmAkces As Form_Akces
Public gbl_frmPodrobnosti As Form_Podrobnosti

This declaration is the only place I use the Form_ syntax.

 

[Minty]

That

The global variables are declared in a code module:

Public gbl_frmAkces As Form_Akces
Public gbl_frmPodrobnosti As Form_Podrobnosti

This declaration is the only place I use the Form_ syntax.

That's where I am suggesting it might be a problem, as it is still not directly referencing the open instance of either main form or subform.

 

[Petr Danes]

That

That's where I am suggesting it might be a problem, as it is still not directly referencing the open instance of either main form or subform.

But this is only the declarations of the variables. The actual instantiation, as I have written several times, is in the load event of the main form, so:

Set gbl_frmAkces = Me
Set gbl_frmPodrobnosti = gbl_frmAkces.sfPodrobnosti.Form

If you think this is not correct, what do you suggest I try? Declaring the variables as simply objects?

 

[LarryE]

I have a form Viewform with subform VendorForm that has 57 records. If I use:

Dim Frm As Form
Dim rsFrm As DAO.Recordset
Set Frm = Forms![ViewForm]![VendorForm].Form
Set rsFrm = Frm.Recordset
With rsFrm
    .MoveLast
    .MoveFirst
    MsgBox .RecordCount
End With

The message box returns 57. I wonder if the problem is that you are not defining and setting a DAO.Recordset object even if your subforms recordset is being defined and set (or supposedly is). Try defining the DAO.Recordset object as the subform recordset.

Obviously, change my form reference to yours.

 

[Petr Danes]

I have a form Viewform with subform VendorForm that has 57 records. If I use:

Dim Frm As Form
Dim rsFrm As DAO.Recordset
Set Frm = Forms![ViewForm]![VendorForm].Form
Set rsFrm = Frm.Recordset
With rsFrm
    .MoveLast
    .MoveFirst
    MsgBox .RecordCount
End With

The message box returns 57. I wonder if the problem is that you are not defining and setting a DAO.Recordset object even if your subforms recordset is being defined and set (or supposedly is). Try defining the DAO.Recordset object as the subform recordset.

Obviously, change my form reference to yours.

Hm, never thought of defining a separate recordset object. I'll give that a try. Thank you for the idea.

 

[LarryE]

Hm, never thought of defining a separate recordset object. I'll give that a try. Thank you for the idea.

Still doesn't explain why a clone would work and original recordset sometimes crashes, but maybe it won't crash anymore.

 

[The_Doc_Man]

resetting the record source. Granted, to the same thing it was already indicating anyway, but that allowed the code to proceed

Resetting the .RecordSource also immediately resets and (I believe) requeries the .RecordSet. I haven't tested it, but it would not surprise me to find that the .RecordSetClone is still defined, at least briefly, just after the .RecordSet is closed.

 

[Petr Danes]

Resetting the .RecordSource also immediately resets and (I believe) requeries the .RecordSet. I haven't tested it, but it would not surprise me to find that the .RecordSetClone is still defined, at least briefly, just after the .RecordSet is closed.

I had a .Requery command, and that seemed to be where the problem was cropping up. At least I think it was. This is a little hard to track, as a built-in construct, a form's native .Recordset property suddenly is non-functional, but it does not throw an error at the point when it BECOMES non-functional, and it does not always become non-functional. That makes it a little hard to track down exactly where it is happening. But it seems to me the .Requery command ought to refresh everything. Instead, it seems to be disrupting things. I issue the 'dummy'
.Recordsource = .Recordsource
command AFTER the .Requery command. I tested it last night, and it seemed to work, so I put it on the client's machine before I went to bed. It also seemed to work there, but it was late and I didn't pound it as thoroughly as I would have liked. We'll see how it works for him when he comes in today.

I didn't know what would happen if I closed the form's recordset, so I set a breakpoint and executed:
gbl_frmPodrobnosti.Recordset.Close
in immediate mode. Everything instantly stopped working, including the .RecordsetClone property.