3021 Error

[RonL]

I've run into something strange with a mainform/subform situation, where everything behaves as desired if the main form is opened from design view. But if I close it, and double click it in the navigation pane, it doesn't work right. If I then put a breakpoint at various places, it does work right. After lots of futzing around, I can focus to the nut of the problem with the following fragment:

..................
Set rst = Me!sbfShotList.Form.RecordsetClone 
        With rst
        Debug.Print .RecordCount
        .MoveLast
         Debug.Print .RecordCount
....................

This code is from the AfterUpdate event of an unbound combobox (which is used to filter the subform), but is called from the onload event of the main form. It is not relevant to the actual desired functionality, but used here to illustrate the problem. If the form is loaded from design view, both print statements give the same (>0) number. But if loaded fresh, the first number is zero and the .movelast statement triggers a 3021 error (empty recordset). If I then press F8, the program moves on, and there is no more error and the second print statement gives the proper value for the full complement of records in the recordset.

So it appears something keeps the recordset from filling fast enough to keep up with the code when the form is first loaded, but not thereafter. FWIW, there are only 29 records in the entire (unfiltered) subform recordset. I thought it might have something to do with some conditional formatting I have built into the form controls, but removing the formatting makes no difference.

Any ideas? Is there some global setting I've missed?

Thx, Ron

[June7]

I got lost on "code is from the AfterUpdate event of an unbound combobox (which is used to filter the subform), but is called from the onload event of the main form".

Why call from form OnLoad event code in combobox AfterUpdate event?

[RonL]

I got lost on "code is from the AfterUpdate event of an unbound combobox (which is used to filter the subform), but is called from the onload event of the main form".

Why call from form OnLoad event code in combobox AfterUpdate event?

The combo's after update is where the code is for resetting the subform's current record to the one that was current when the prior recordset was loaded. I didn't want to duplicate it in the OnLoad event.

But is that relevant? Can you think of any reason why the five lines above would run correctly when going from design to form view, yet trigger a "no recordset" error when opening the form fresh? I was trying not to confuse things by posting the real code, but I could if folks think it will help. Just seems to me there's some global issue getting in the way.

[June7]

As I understand it, the subform and its records are loaded before the main form so why the code in main form Load event fails baffles me. I have seen at last one thread with similar question about the form behaving differently opening from Design than directly to Form view. Don't remember how or if it was resolved.

However, I still don't really understand what you want to accomplish. What is the 'prior recordset'?

[RonL]

As I understand it, the subform and its records are loaded before the main form so why the code in main form Load event fails baffles me. I have seen at last one thread with similar question about the form behaving differently opening from Design than directly to Form view. Don't remember how or if it was resolved.

That's two of us baffled, but thanks for considering it. The thread to which you refer may have been my own: https://www.accessforums.net/forms/e...ame-34206.html Now I'm thinking the problems I was having there are really related to the phenomenon I've pinpointed here.

However, I still don't really understand what you want to accomplish. What is the 'prior recordset'?

By "prior" I meant the previous display in the subform associated with the just prior choice of current record in the main form. I may start a separate thread on this, but I'll explain briefly what I actually want to do:

I have a simple one to many relationship: Shoots to Shotlist. I've got it set up in (what I take from my reading to be) a typical way. Main form, subform, with an unbound combo box on the main form from which I can choose the shoot. The relationship then automatically resets the subform (with a new (right?) recordset). It works fine. There will be few shoots, with many shots each. Occasionally, the user will leave shoot A to look at shoot B. When she returns to shoot A, I'd like the current record in the subform set to whatever it was when she left shoot A. I've been trying to adapt the Allen Browne technique I referenced in that other thread.

But the present difficulty seems to be getting in the way. There are various refs to the 3021 error in VBA over the years. Some are caused because the user really didn't properly code for the recordset. But for others, that seems not the issue, and, in some cases, people have reported that setting a breakpoint causes the same behavior I see here: the code then continues normally - ie. it's as if it needed a "rest" in order to form the recordset. The usual comment is just that: that the code (without breakpoint) runs faster than the formation of the recordset. But I've yet to see a good workaround or solution to such a dilemma, if indeed that's the true explanation.

-Ron

[June7]

Yep, that's the thread I remember. The difference from the Allen Browne example is that you want to return to record of a subform. That code is what I would have built even if I never saw Browne's example. Can't imagine any other way to accomplish. Can only wish you good luck.

[RonL]

Well I found a homemade Pause function on the net, and it bypasses the immediate problem. I put a half-second pause right after "with rst". Not very satisfying, but it allows me to move on.

My recordset is miniscule, so if this really is a simple timing issue, I'm surprised.

If anyone is interested, the function is here .

-Ron