Recordset bombs, RecordsetClone works

[pdanes]

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.

[Minty]

Does anything change the subforms recordset in the background?
If the underlying recordset is changed sometime access conveniently decides that the recordset is no longer accessible and gives up that error.

As a potential workaround, you could use the record set clones bookmark to then move around, then set the main recordset to the same bookmark?

[pdanes]

Does anything change the subforms recordset in the background?
If the underlying recordset is changed sometime access conveniently decides that the recordset is no longer accessible and gives up that error.

The recordsource of the subform is always the same query. The recordset changes when the text of the query changes. That change is followed by a Requery command, and the new record(s) are visible. They are visible when this thing bombs.

But it still makes no sense to me. If ...Recordset Is Nothing returns False, how can ...Recordset.RecordCount then claim that the object does not exist, and why would ...RecordsetClone.RecordCount work properly? It's not even an object variable that was set earlier - it's the RecordsetClone property of the recordset which it just told me is NOT Nothing, but says does not exist when I ask for its count.

[pdanes]

As a potential workaround, you could use the record set clones bookmark to then move around, then set the main recordset to the same bookmark?

Tried that - I get the same error of the recordset object not existing.

[accesstos]

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

By using wrongly the class of the loaded object as pointer to the loaded object. You can refers to me by calling me "user" (of this forum) but, maybe be me (accesstos), maybe not. If you you had two instances of the Form_Akces loaded, which one of two them would be the Form_Akces?
You have to refer always to the loaded form(s) through the Forms() collection (or by using an object variable as pointer).

Try:

i = Val(Forms("Akces").sfPodrobnosti.Form.Recordset.RecordCount)

Cheers,
John

[Minty]

I assume the underlying Subform recordset is not editable in the subform?
Have you set it to a snapshot or dynaset? I always open them as a snapshot if they don't need to be updated barring a forced refresh.

I will stick with my original (vaguely educated) guess that access thinks the recordset has changed, thus destroying the original dataset.

Edit - I just noticed the FORM reference as mentioned by John - that will cause a problem.

[pdanes]

By using wrongly the class of the loaded object as pointer to the loaded object. You can refers to me by calling me "user" (of this forum) but, maybe be me (accesstos), maybe not. If you you had two instances of the Form_Akces loaded, which one of two them would be the Form_Akces?
You have to refer always to the loaded form(s) through the Forms() collection (or by using an object variable as pointer).

Try:

i = Val(Forms("Akces").sfPodrobnosti.Form.Recordset.RecordCount)

Cheers,
John

There is never more than one instance of this form/subform combination loaded. It is the one and only interface to the application, loaded on DB startup and permanently open. There are other dialog forms that are called from that main form, which do their work and close, falling back to the main form. But I'll certainly try your suggestion.

[pdanes]

I assume the underlying Subform recordset is not editable in the subform?

Correct - it is not editable. Edits are done in a overlaying dialog-mode pop-up form. And the recordsource is not editable anyway - it is a recordset generated by a remote SQL stored procedure, with complex joins and computed fields.

Have you set it to a snapshot or dynaset? I always open them as a snapshot if they don't need to be updated barring a forced refresh.

It is a snapshot, with a Requery command executed when the query calling the stored procedure changes.

I will stick with my original (vaguely educated) guess that access thinks the recordset has changed, thus destroying the original dataset.

But doesn't Requery renew the contents? Those contents are displayed properly, and again, the RecordsetClone property works correctly. If the recordset as an object was trashed, why is the clone, newly referred to AFTER the error, which points to the SAME recordset, still functional?

Edit - I just noticed the FORM reference as mentioned by John - that will cause a problem.

I will give that a try, although I must confess, I don't understand the significance of the difference between the two syntaxes.

[accesstos]

I will give that a try, although I must confess, I don't understand the significance of the difference between the two syntaxes.

Form_Akces is the class of the form Akces (if Akces has module) that points by chance to the loaded form Forms("Akces"), in some cases not. So, for some reason at run time, you lose the connection (the pointer) with the loaded form object (and its members as well).
Forms("Akces") is always the loaded form Akces. The object, in your case, that you have to refer to.

[pdanes]

Form_Akces is the class of the form Akces (if Akces has module) that points by chance to the loaded form Forms("Akces"), in some cases not. So, for some reason at run time, you lose the connection (the pointer) with the loaded form object (and its members as well).
Forms("Akces") is always the loaded form Akces. The object, in your case, that you have to refer to.

But once it loses such a connection, would it not be lost until the DB is restarted? And would not the lost connection affect everything downline from it?

Why would

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

work, while

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

does not? They both have the same leading object that you say may be losing scope - Form_Akces.

In any case, I just did a mass replace of every instance of Form_Akces with Forms("Akces") and am now testing it. No problems yet, but it's usually the users who have these problems.

One of my bugbears in this is trying to replicate the users' problem. Often I get called to see the problem and can't even reproduce it on their machine. I manually walk around the error and the next time through, it executes fine. Or I restart the DB and follow exactly what they say they did the first time, but no error. A running joke around here is that all I have to do is come glare at their computers and they start working properly, which is funny, but hardly a proper long-term solution.

[pdanes]

Form_Akces is the class of the form Akces (if Akces has module) that points by chance to the loaded form Forms("Akces"), in some cases not. So, for some reason at run time, you lose the connection (the pointer) with the loaded form object (and its members as well).
Forms("Akces") is always the loaded form Akces. The object, in your case, that you have to refer to.

Another possibility - would setting a global object variable to the form in the Form_Load event maybe solve it as well? I think I had that earlier in the development, and then took it out for some reason I no longer remember. That would account for the problem only surfacing recently. Would that also not be more efficient than Forms("Akces") in literally hundreds of places?

But I still don't understand why the Clone property of the same thing works when the original does not.

[accesstos]

The most secure way is the reference to the Forms("Akces") because you have a fresh pointer in any time to the loaded object. The most "well practice" way is by using an object variable (global or local) as pointer to the Forms("Akces"). Using the class as pointer, is the worst way and you are already face the related problems. We have discussed again in the past the same issue, if you remember.

The RecordsetClone, is an independent, loaded object with a new, available pointer to it.

Cheers,
John

[pdanes]

The most secure way is the reference to the Forms("Akces") because you have a fresh pointer in any time to the loaded object. The most "well practice" way is by using an object variable (global or local) as pointer to the Forms("Akces"). Using the class as pointer, is the worst way and you are already face the related problems. We have discussed again in the past the same issue, if you remember.

The RecordsetClone, is an independent, loaded object with a new, available pointer to it.

Cheers,
John

I think I see - the Form_Akces construct does NOT specifically refer to the open and running instance of the form. In that case, I am surprised that it ever works. What does it refer to, then? Or is that an undefined operation, the way I have been using it, which may sometimes work and sometimes not, depending on how the stars align at that particular moment?

I do not recall the specific instance in the past when we discussed something like this, but if you have a link to it, I will review it.

[pdanes]

I've replaced all instances of referring to a form by the Form_xx syntax with either the Forms("xx") variant or with a global object variable set by Set gbl_frmXX = Me in the Form_Load event. So far seems to be working.

[Minty]

The Form_xx version will create an new instance of the form if I remember correctly. (I didn't know this it was pointed out to me in a thread on another forum here: https://www.access-programmers.co.uk...9/post-1747913)

Also, assuming your code is in the parent form, if you were to refer to the subform container form the Main form using Me.MySubformControl.Form .... It would always be referencing the correct instance.