Recordset bombs, RecordsetClone works

[Petr Danes]

Tried this a while back in AccessForums.net (https://www.accessforums.net/showthread.php?t=84803). Got several suggestions, but no solution or explanation, and the problem continues.

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

I believe this has started since I switched to 365 - I don't recall it happening in 2007, although I 'm not absolutely certain. If it did happen before, it is certainly happening much more now, but development is ongoing, so it may be that I have just generated some new errors.

I have an unbound form with a single record, filled by VBA, containing a subform, with recordset bound to an ODBC-linked query calling various MSSQL stored procedures with various parameters. I modify the text of the query as needed and requery the subform. Not sure if that is relevant, since that is not new, but I'm trying to include as many possibly pertinent details as possible.

What is happening is that I sometimes need to find out how many records I have in the subform's recordset. The tests are for 0, 1, 4 or > user-set value. The code handling the results works, but the comparison bombs at seemingly random intervals, and I have so far been unable to pinpoint what combination of circumstances might be affecting it.

The main form is Akces, the subform is Podrobnosti, the subform's container on the main form is sfPodrobnosti.

A statement of the form:

i = Val(Form_Akces.sfPodrobnosti.Form.Recordset.RecordCount)

will sometimes work and sometimes not. When it does not, it throws an error saying that the object does not exist. But all the objects do exist. Printing the names, and ... Is Nothing tests all show that the object does exist, and besides that, the form and subform are both loaded and the record(s) displayed.

However, when I modify the code to:

i = Val(Form_Akces.sfPodrobnosti.Form.RecordsetClone.RecordCount)

it works fine. Except that I cannot use the RecordsetClone object to manipulate the actual recordset, so I cannot move from record to record under program control, which one of my graphic gimmicks does.

Does this make any sense to any one?

How can a reference to such an object throw an error, when the object very clearly exists?

How can an object 'not exist' and yet its clone be fine?

Why would the same statement sometimes work and sometimes not?

Some of this is happening in class modules, some directly in the form's code module.

 

[arnelgp]

it works fine. Except that I cannot use the RecordsetClone object to manipulate the actual recordset, so I cannot move from record to record under program control, which one of my graphic gimmicks does.

You can.
the good thing with RecordsetClone is you can navigate through the recordset without affecting the current record on the form (unless you set the form's bookmark to the rs bookmark).

 

[Petr Danes]

You can.
the good thing with RecordsetClone is you can navigate through the recordset without affecting the current record on the form (unless you set the form's bookmark to the rs bookmark).

Yes, I can, WHEN the Recordset object works correctly. It does not, hence this post.

Any reference to the actual Recordset object bombs, while a reference to the RecordsetClone object works properly. However, manipulating the RecordsetClone object does not change the displayed records of the form using that busted Recordset as its data source.

 

[arnelgp]

did you try to Set the Form's Bookmark to the RecordsetClone.Bookmark
to change the displayed record?

 

[Petr Danes]

did you try to Set the Form's Bookmark to the RecordsetClone.Bookmark
to change the displayed record?

I did. Once again, any reference to the actual Recordset object bombs, while a reference to the RecordsetClone object works properly. That includes my main problem, trying the get the RecordCount value. Recordset.RecordCount bombs, RecordsetClone.RecordCount works.

 

[arnelgp]

i don't understand RecordsetClone is but a Clone copy of Recordset.
they are identical whatever you can do with recordset you can do with it's clone.

what i the bomb you are talking here?

if you're data is Linked table, it might take time for the recordset to be "filled".
therefore you need to .MoveLast and .MoveFirst to the recordset (or clone),
before you can get the .RecordCount.

Dim frm As Form
Dim lngRecord As Long

Set frm = Form_Akces.sfPodrobnosti.Form
with frm.RecordSet
.MoveLast
.MoveFirst
lngRecord = .RecordCount
end with

 

[Petr Danes]

i don't understand RecordsetClone is but a Clone copy of Recordset.

That was my understanding, but it's not happening that way.

they are identical whatever you can do with recordset you can do with it's clone.

So I also thought, but it is not working.

what i the bomb you are talking here?

Error 3420 - "Object invalid or no longer set." I do not understand how I can have a clone of something that doesn't exist.

if you're data is Linked table, it might take time for the recordset to be "filled".
therefore you need to .MoveLast and .MoveFirst to the recordset (or clone),
before you can get the .RecordCount.

It is a recordset filled by a pass-through query calling a SQL Server stored procedure. The query works, the RecordCount property of the clone gives the correct value, but trying to reference the original recordset throws this error.

Dim frm As Form
Dim lngRecord As Long

Set frm = Form_Akces.sfPodrobnosti.Form
with frm.RecordSet
.MoveLast
.MoveFirst
lngRecord = .RecordCount
end with

Thank you, I do know about using the MoveLast method to force in the entire dataset, but that is not what is happening here.

However, I think I may have stumbled onto something just now. Somewhere in the depths of one of the graphic routines is a Requery command, although .RecordSource stays the same, and some initial experimenting has shown me that is what dislodges at least some of my references. Now that I have at least some indication of what is going wrong and where it is happening, I'm going to see if that isn't where I'm upsetting the applecart.

 

[LarryE]

If all you are doing is counting records in the current forms recordset, then you can just include a textbox control on the form with its Control Source as =Count(*). That will show how many records the form has at any time.

 

[Petr Danes]

If all you are doing is counting records in the current forms recordset, then you can just include a textbox control on the form with its Control Source as =Count(*). That will show how many records the form has at any time.

Unfortunately, that is not all. That is usually where it bombs, but that is only because it's the first step, in a series of things I want to do. But every reference to the Recordset object from there on throws this error.

 

[NauticalGent]

By chance are you calling the clone from another module? I am thinking you may be setting the recordset object to Nothing or the object is going out of scope somehow.

 

[The_Doc_Man]

This is perhaps a silly suggestion, but sometimes I have seen that style of a sub-form reference have problems. Is this particular reference made from the form's class module of that unbound form called Akces? If so, can you try

i = Val(Me.sfPodrobnosti.Form.Recordset.RecordCount)

Granted, this is grasping at straws because of this statement:

Some of this is happening in class modules, some directly in the form's code module.

Anything that happens in a form's module IS in a class module. Were you trying to indicate that it happens in non-form class modules? Because there, you have scoping issues that might be relevant. And in that case, "Me." references wouldn't work quite the same.

To debug this, I would put some kind of error trap routine in the same segment of code where this error occurs. Then, when the break occurs, I would open the Locals window because at that point, things should indeed be local. I would drill down through the sub-form to its form properties and look at the .Recordset properties. It would be clear in that context as to whether Access thought something was or was not defined.

 

[Minty]

@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.

 

[LarryE]

Unfortunately, that is not all. That is usually where it bombs, but that is only because it's the first step, in a series of things I want to do. But every reference to the Recordset object from there on throws this error.

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.

 

[Minty]

@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.

 

[The_Doc_Man]

@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.

I must have missed the other thread. Thanks for the clarification, Minty.

 

[Petr Danes]

By chance are you calling the clone from another module? I am thinking you may be setting the recordset object to Nothing or the object is going out of scope somehow.

I'm not sure. I set a global reference to the subform in the main form's load event:

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

and thereafter use the syntax:

gbl_frmPodrobnosti.RecordsetClone
gbl_frmPodrobnosti.RecordsetClone.NoMatch
gbl_frmPodrobnosti.RecordsetClone.FindFirst "EvidenceLetter = '"...

and so on, or when directly in the subform's code, I use:

Me.RecordsetClone.RecordCount
Me.RecordsetClone.AbsolutePosition

Sometimes it works, sometimes it does not, but once it throws an error, ALL references to the .Recordset object throw the error, while the .RecordsetClone object continues to work. And it seems to work less on client's machines. Several times I have though I fixed it, deployed my fix, and the next thing I hear is, "Nope, still crashing." But when I manually open the pass-through query populating the form, even on a client machines, it always works perfectly.

Yes, it does act like the reference is going out of scope, but I don't see how it could. It is a global object variable, set once on form load and never again. The SQL text of the RecordSource query changes, and the subform is requeried when that happens, but that should not affect the scope of a global variable set as public in a code module and instantiated exactly once.

 

[CarlettoFed]

If you do not attach a file with a minimum of data, replacing the sensitive ones, it will be difficult to understand what happens and consequently help you.

 

[Petr Danes]

This is perhaps a silly suggestion, but sometimes I have seen that style of a sub-form reference have problems. Is this particular reference made from the form's class module of that unbound form called Akces? If so, can you try

i = Val(Me.sfPodrobnosti.Form.Recordset.RecordCount)

Granted, this is grasping at straws because of this statement:



Anything that happens in a form's module IS in a class module. Were you trying to indicate that it happens in non-form class modules? Because there, you have scoping issues that might be relevant. And in that case, "Me." references wouldn't work quite the same.

To debug this, I would put some kind of error trap routine in the same segment of code where this error occurs. Then, when the break occurs, I would open the Locals window because at that point, things should indeed be local. I would drill down through the sub-form to its form properties and look at the .Recordset properties. It would be clear in that context as to whether Access thought something was or was not defined.

Sorry, I meant a specifically declared class module, as well as the built-in class module of the form.

I have also tried the Me. variation - same problem.

I have done what you suggest - the code stops on the error. I have disabled all error trapping and simply let the code bomb and stop, so I am sure that I have not detoured into some place that might be hiding what is going on. It stops on a statement like:

i = gbl_frmPodrobnosti.Recordset.RecordCount

with the 3420 error. ALL attempts in the immedate window to use ANY .Recordset syntax also throw that error. When I replace .Recordset with .RecordsetClone, all works.

?gbl_frmPodrobnosti.Recordset Is Nothing returns False

?gbl_frmPodrobnosti.RecordSource shows Alles_spPodrobnostiNaHlavnim, the name of the pass-through query feeding the subform. The subform shows the proper records, and manually opening the query also shows the proper set of records.

I'm having trouble with debugging because the failure is random, or at least, does not follow any pattern I have been able to detect, except that it more often crashes on a client's machine than mine.

One thing I tried a little while ago was executing:

gbl_frmPodrobnosti.RecordSource = "Alles_spPodrobnostiNaHlavnim"

i.e., resetting the record source. Granted, to the same thing it was already indicating anyway, but that allowed the code to proceed - got past the error. I have no idea why.

 

[Petr Danes]

If you do not attach a file with a minimum of data, replacing the sensitive ones, it will be difficult to understand what happens and consequently help you.

Thank you, but that is not really possible. This is a client in a client-server app. Constructing local versions of all the tables, making fake procedures to mimic the SQL Server stored procedures and changing everything to use those fakes would be an insane amount of work, with zero guarantee that it would lead anywhere, or even that it would still throw the error.

 

[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.

We did, and they are not. This is that same app, but based on that thread, I have replaced all such references with the syntax I now specified here - global object variables, set to the Me reference on form load:

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