OnCurrent Event of a Subform; Subform VBA

[RonL]

I have the following in the Form_Current() event of a subform.

If Not Me.NewRecord Then
Exit Sub
End If

It doesn't work on opening the main form. No error; the code simply proceeds without exiting. However, the following does work:

If Me.NewRecord = -1 Then
Exit Sub
End If

So what does the value "-1" mean, if not False? Is it Null? I thought I read somewhere that VBA interprets Null as False in an IF block. If I open only the subform, then Me.NewRecord=0. So why the difference? Is it because the subform is considered to be within the subform control of the main form, and that control is not actually loaded yet when the subform's first Current event fires?

I'm trying to keep code I have in the Current event of a subform from executing until the master/child links with the main form are established, which as I understand it, won't happen until after all subform opening events have fired. I've found threads dealing with issue of a subform's current event firing on startup - when it's not wanted - but so far nothing definitive on proper way to prevent it, or best workaround. Would appreciate advice.

Thanks, Ron

[June7]

-1 is Access/VBA for Boolean True and 0 is False. The code could be:

If Me.NewRecord = True Then

or

If Me.NewRecord Then

[June7]

Allen Browne has a lot of good stuff.

[RonL]

-1 is Access/VBA for Boolean True and 0 is False. ....

Thanks June (I did know that once upon a time.)

So now I'm left with the question as to why me.newrecord is False when the current event first fires for the subform when opened by itself, but True when the subform is loading as consequence of opening the main form?

I've learned a lot from Allen Browne's stuff, and he does discuss the complexities of the Current event in selected contexts, but I've yet to find a clear exposition of when, where, how a subfom's current event will or will not fire. I see there are some properties, such as the Filter on Empty Master of the subForm control I can toggle, so maybe I'll learn more there.

[Missinglinq]

...So now I'm left with the question as to why me.newrecord is False when the current event first fires for the subform when opened by itself, but True when the subform is loading as consequence of opening the main form...

When you open the Form the Subform is based on, by itself, the first Record that displays is always going to be an existing Record, hence the False. The only way that it could True, when opening it independently, would be if you had code, in the Form_Load event, to move to a New Record on opening, or there were absolutely no Records in the Table or Query the Subform was based on.

When opened as a Subform, whether Me.NewRecord is True or False depends on whether or not the first displayed Main Form Record has any Records in its Subform; if the Main Form Record has one or more Records in the Subform, Me.NewRecord will be False, otherwise it'll be True.

Linq ;0)>

[ssanfu]

Also see http://office.microsoft.com/en-us/ac...005186761.aspx
for order of events. The third + is about "Order of events for forms and subforms"

[RonL]

Also see http://office.microsoft.com/en-us/ac...005186761.aspx
for order of events. The third + is about "Order of events for forms and subforms"

Yes, thanks, I've been staring at that page off and on for days. And the section to which you allude begins:
"Working with subforms

When you open a form containing a subform, the subform and its records are loaded before the main form. Thus, the events for the subform and its controls (such as Open, Current, Enter, and GotFocus) occur before the events for the form. The Activate event doesn't occur for subforms, however, so opening a main form triggers an Activate event only for the main form. "

I read that to mean the main form hasn't loaded at the point when subform's Current fires, and debug tracing supports this.

Which is why I'm confused when Linq says: "....When opened as a Subform, whether Me.NewRecord is True or False depends on whether or not the first displayed Main Form Record has any Records in its Subform; if the Main Form Record has one or more Records in the Subform, Me.NewRecord will be False, otherwise it'll be True."

The recordsource for my Main form is a predefined select query comprised of three joined tables. It's the same as the relationship defined for the db. So it should contain every record in the db (because there's no where clause),no? So yes, any main form record should have one or more records in the subform. Yet the first time the subform's Current event fires, me.newrecord is True.

I think I'm missing something really basic here.

[RonL]

............I think I'm missing something really basic here.

I thought I'd bump this, because I've made an observation that is probably relevant to the above, and to a lot of threads over the years, some of which have reported 3021 errors, and other apparent misbehavior when referencing a subform from a main form's load event.

Consider the following fragment, which comes from the main form Load event (which a MsgBox debugging line shows has indeed fired).

' other stuff
' '--------------set the subform-----------
 
  strDelim = """"
  varPlaceMark = DLookup("fldValue", "tblInterSessionPlacemark", "[fldVariable] = 'fldShotIDLast'")

        Pause (0.8)

         Me!ctlsbfShotList.Form.Recordset.FindFirst "[ShotNumber] & [ShotNumSuffix] = " & strDelim & varPlaceMark & strDelim

             If Not Me!ctlsbfShotList.Form.Recordset.NoMatch Then 
                  MsgBox ("there was a match for " & strDelim & varPlaceMark & strDelim)
             End If
            
End Sub

As an aside, tblInterSessionPlacemark keeps track of which record was current in the subform when the app was last closed, so that the user can come back to it on next startup.

But here's the thing: This code works as expected with that Pause function (in red) in there. If I comment it out, the NoMatch changes from False to True, and the subform comes up with the first record as current. (I'm inferring the "toggle" of NoMatch, because if I put a breakpoint in the assignment statement before the Pause, then the Pause is not needed and the code still executes as expected. ie. either a Pause OR a breakpoint works around the problem.)

I interpret this to mean that, despite what the event order documentation leads us to believe, a subform load is not necessarily complete at the time the main form load fires. I think it is behind the observation I made earlier in the thread about the current record in the subform being "new" when it should not be.

There are various threads over the years about subform recordsets behaving unexpectedly on loading - search on the 3021 error, for example. Various workarounds are given, frequently involving a .movelast and/or .movefirst. (Those tricks didn't help here.) But either I'm still missing something fundamental, or this is a genuine quirk inherent in at least this particular implementation (A2010 under Win7x64). It's sure costing me a bunch of frustration. Maybe this post will help ease the pain for someone else in the future. -Ron

[ssanfu]

Interesting.... I have noticed that once in a while, I have to add a "DoEvents" to allow processes to complete. Most notably using Excel Automation.

So Pause (.08) is a UDF you wrote?

Have you tried using one or two DoEvents instead of Pause??

[RonL]

Interesting.... I have noticed that once in a while, I have to add a "DoEvents" to allow processes to complete. Most notably using Excel Automation.

So Pause (.08) is a UDF you wrote?

Have you tried using one or two DoEvents instead of Pause??

I've never used DoEvents expicitly, but the Pause function does. Not sure where I got it, but FWIW:

Public Function Pause(NumberOfSeconds As Variant)   'got this from the net
On Error GoTo Err_Pause

    Dim PauseTime As Variant, start As Variant

    PauseTime = NumberOfSeconds
    start = Timer
    Do While Timer < start + PauseTime
    DoEvents
    Loop

Exit_Pause:
    Exit Function

Err_Pause:
    MsgBox Err.Number & " - " & Err.Description, vbCritical, "Pause()"
    Resume Exit_Pause

End Function

Incidentally, if I debug.print the subform's recordcount before and after the pause, it is zero before and correct after, if the Pause parameter is 0.3 or greater. At 0.2, the recordcount is still zero after the pause. (This is a very small, test recordset.) If I try a recordset object property called .StillExecuting it throws an out of context type error - not surprising, I guess, since I don't really understand how that property should be used.

What's frustrating for a hobbiest like me is I don't know if 1) I'm coding suboptimally, 2) this is a good old fashioned bug, 3) Access is built from modular components which are necessarily (for some reason) insulated from each other by "shockabsorbers" such that asynchrony between events is introduced, or 4) me.computer = gremlins.

The beat goes on. -Ron