Problem with subForm On Current event (1 Viewer)

[kirkm]

I have some sub Forms on main Form. One has an on current event and this fires before the Main Forms Load event which sets up things the SubForms current event needs. (At least it appears so by stepping through).
I can circumvent this with some hacks but wonder how it might be resolved correctly?
The current event is crucial and required but not before the main Form loads

 

[theDBguy]

Hi. Subforms are loaded before the main form, so their events fire first. What you could do is check if the information you need is not yet available, so you can skip the code.

 

[MarkK]

You can load subforms programmatically in the Open event handler of the main form. Consider code like...

Private Sub Form_Open(Cancel As Integer)
'   This is the Open event handler of the main form, and we set the SourceObject property of the 
'   subform control to load a form.  We set it to the name of the form we want to load.
    Me.SubformControl.SourceObject = "fNameOfFormToLoad"
End Sub

Make sure to delete the name of the form from the SourceObject property of the subform control in design view so that it is not present when the main form loads.

 

[arnelgp]

maybe remove the code from the Current event of your subform
and move the code to Public sub (inside subform)

on the load event of the main form, setup everything, then
call the public sub of subform

on subform:

public sub TestSub()
msgbox "success!"
end sub

on main form load:

private sub form_Load
'put code here to setup everyting
'
'after call the subform
me!subformName.Form.TestSub
end sub

 

[arnelgp]

You can load subforms programmatically in the Open event handler of the main form. Consider code like...

this won't work, tested and still subform's Load event fires before Mainform's Load event.

 

[kirkm]

I don't want to contradict arne but I think it did work. A bit hard to follow stepping through but I had an error trap (sort of what DBGuy suggested) which I've now remmed out and no error anymore.
It was Error 2455.
arne I was going to try your suggestion next, although couldn't figure out how the subForm Current event would work (when is was needed).
Thanks to all.

 

[arnelgp]

you add a Module variable, example:

Option Explicit
Dim bolInitialized As Boolean

it is better you understand the flow of code
through sample.

open Form1.

 

[Pat Hartman]

If you post the code, we can tell you if it belongs in a different event. it is the open, load, and current events that run multiple times. If you code is adversely affected by that, you can skip the code as theDBguy suggested or there might be a better event to use.

 

[kirkm]

Had never seen a report before arne, and some of the code to set twips and sizing was impressive - if a bit daunting. But knowing such things are possible.... Your design is more concise than mine . Is that the right word? I'm sort of doing more too. MarkK suggestion has worked though and the error has gone (Not that I can see exactly why). I also tried to use a boolean but for some raeson it wasn't working. It was "empty". I didn't think a boolean could be empty. Pat, how much code to post, hopefully this is enough.
Main Form

Private Sub Form_Load()

    'Maximise Access
    DoCmd.RunCommand acCmdAppMinimize
    DoCmd.RunCommand acCmdAppMaximize
    'select the navigation pane
    Call DoCmd.NavigateTo("acNavigationCategoryObjectType")
    'hide the selected object
    DoCmd.Minimize
    Grid = 1  'Set default
    setupCombo
    ResetDatasheet

End Sub

Sub setupCombo()
    With Me.cboYear
        .RowSource = ""
        Select Case Grid
            Case 1
                For i = 1936 To 2000
                    .AddItem i
                Next
                .DefaultValue = 1952
            Case 2
                For i = 1959 To 1967
                    .AddItem i
                Next
                .DefaultValue = 1959
            Case 3
                For i = 1956 To 1979
                    .AddItem i
                Next
                .DefaultValue = 1956
            Case 4
                For i = 1936 To 1965
                    .AddItem i
                Next
                .DefaultValue = 1936
        End Select
    End With
End Sub

Sub ResetDatasheet()
    Dim s As String

    s$ = "SELECT tblMain4.Year, tblMain4.Prefix, tblMain4.CH, tblMain4.[40], tblMain4.[10], tblMain4.[#], tblMain4.High, tblMain4.Artist, tblMain4.Title, tblMain4.[A-Time], tblMain4.[A-Side Composer],  tblMain4.[B-Side Artist], tblMain4.[B-Side Title], tblMain4.[B-Time], tblMain4.[B-Side Composer], tblMain4.Label, tblMain4.Number, tblMain4.Format,"
    s$ = s$ & " tblMain4.sort, tblMain4.sheet FROM tblMain4 where Sheet = '" & Switch(Form_Form1.Grid = 1, "Britburn", Form_Form1.Grid = 2, "EPCharts", Form_Form1.Grid = 3, "AlbumCharts", Form_Form1.Grid = 4, "SheetMusic") & "' and Year = '" & cboYear & "' Order By Sort;"

    Forms.Form1.Datasheet.Form.RecordSource = s$
    Bar.Caption = Switch(Form_Form1.Grid = 1, "Britburn", Form_Form1.Grid = 2, "EP Charts", Form_Form1.Grid = 3, "AlbumCharts", Form_Form1.Grid = 4, "SheetMusic") & " Data for " & cboYear

End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.Datasheet.SourceObject = "Datasheet"
End Sub

SubForm

Private Sub Form_Current()

    On Error GoTo err99
   
    Dim Dat As String
    Dim Comm As Variant
    Dim item As Variant
    Me.Parent.Form2!lstacomment.RowSource = ""
    Dat = Nz(DLookup("AComment", "tblMain4", "Prefix ='" & Me.Prefix & "'"))
    If Dat > "" Then
            Comm = Split(Dat, vbLf)
            For Each item In Comm
                Me.Parent.Form2!lstacomment.AddItem item
            Next
    End If

    Me.Parent.Form3!lstbcomment.RowSource = ""
    Dat = Nz(DLookup("BComment", "tblMain4", "Prefix ='" & Me.Prefix & "'"))
    If Dat > "" Then
            Comm = Split(Dat, vbLf)
            For Each item In Comm
                Me.Parent.Form3!lstbcomment.AddItem item
            Next
    End If
   
qt:
   
    Exit Sub
err99:
    Stop
    If Err = 2455 Then Exit Sub
    Debug.Print Err, Err.Description
    Stop
    Resume qt
End Sub

The subForm also has a Load event but that just sets up column widths in the datasheet.
I do not (now) ever reach the stop in the error handler.

 

[kirkm]

Hey arne, as I get more into this I do see you were right and I understand what your sample shows. However MarkKs idea about Me.SubformControl.SourceObject = "fNameOfFormToLoad" did stop the error I was getting.
I'd like to prove this by undoing what I did e.g.
Make sure to delete the name of the form from the SourceObject property of the subform control in design view so that it is not present when the main form loads.

But I can no longer see the subform Control under the main form. Once that change was made, theres nothing to click on to get to the SourceObject property. I don't the option on the subForm itself (or I can't find it). Does that make sense ?

 

[Minty]

To back up @MarkK's comment - I normally use the main forms Load event to set subforms when I need them to appear in a set order.
I don't use form Open.

 

[kirkm]

Something is weird.
In arne's sampleForm db I put a stop In Form1 Form_Open and a Stop in ds500 Form_Current.

I open Form1 and it stops in ds500 Form_Current.
But doing the same thing in my db sees it stop in Form1 Form 1 Form_Open.
Can anyone explain this ?

 

[kirkm]

I think I've figured it out. All depends on SourceObject being set, or not.

 

[Pat Hartman]

Me.Parent.Form2!lstacomment.RowSource = ""

Why is code in the subform modifying the RowSource of a combo on the main form? This doesn't seem rational since everytime a new record becomes current, the combo on the main form has its RowSource changed. Also, since the subform loads first, the main form hasn't yet been loaded the first time this code runs.

 

[kirkm]

It took me ages to work out what ran first and why. Source Object property had to exist for SubForm Current event run first.
I ended up using the method in arnes sampleform.accdb which bypaseses the current event if a boolean is false
lstacomment would just keep populating if it wasn't cleared.

 

[kirkm]

May I refer back to the sampleForm.accdb in Msg #7 ?
I have used the method there with Dim bolInitialized As Boolean to allow Current Even to run.
This was working.
But now more routines are added, this is changing back to False and thus Current Event is being bypassed.
No command to change it exists
I moved the declaration from the Form to a module but now the Form doesn't recognise it.
Why would it change with no instruction to? Can it be made Global so any/everything can use it and it's value "stick"?

 

[The_Doc_Man]

Why would it change with no instruction to? Can it be made Global so any/everything can use it and it's value "stick"?

I think I'm addressing your question directly. When you DIM a variable in a subroutine, it exists only for the lifetime of that subroutine because (with one exception I will explain in a moment) ALL sub/function variables are stored on the program call stack in something called a call frame. This frame is dissolved when you do an Exit Sub (or End Sub). So there is no place to hold the variable. A new call to the same sub has a NEW copy of the variable, and by rules of object security, you get a new, blank copy of the variable. Which means you get an empty string, a zero number, a date of 30-Dec-1899, or a FALSE Boolean.

If you actually have something you want to persist across invocations of a subroutine, you must declare it as a STATIC variable. Statics remain extant across multiple calls to the same routine - with one glaring "gotcha" left... If you declare a STATIC variable in a form's class module, that static STILL ceases to exist if the form closes.

 

[kirkm]

Thanks I'll try Static. But - it isn't Dim'd in a subroutine - it's in the Forms declaration area. Or is that the same thing as you're saying. I have used in various modules and I thought the same applied to Form as well... obviously not... or must it be Public or Global, confused now...

 

[kirkm]

I changed Dim bolInitialized As Boolean to Static bolInitialized As Boolean and it reported :

The expression 0n Load you entered as the event property setting produced the following error: Invalid outside procedure.

 

[The_Doc_Man]

True, STATIC is when you want a PROCEDURE/FUNCTION to retain a value across calls. If something is declared in the class module's general declaration area and its value changes, some other mechanism is changing it.