Form Disappears

[D Willett]

Hi guys.
I run the following code from a command button on a main form, "frmCallCentre".
Sometimes when running the code the main form doesn't reload and leaves me seeing the navigation pane even if I convert to a ACCDE, the form is in the startup "Display Form" area.
The form loads ok on startup and after Compact-Repair also.

Is there something in the code which stands out to you guys?

Code: Select all

    Dim stDocName1 As String
    Dim stDocName2 As String
    Dim stDocName3 As String
    Dim stDocName4 As String
    'Dim stDocName5 As String
    Dim stDocName6 As String
    
    
    Me.txtPaint = ""
    Me.txtBody = ""
    Me.txtMET = ""
    Me.txtInNow = ""
    Me.sbfDueIn.Form.RecordSource = ""
    Me.sbfCCarOverDue.Form.RecordSource = ""
    Me.sbfDueIn.SourceObject = "frmWait"
    Me.sbfCCarOverDue.SourceObject = "frmBlank"
    Me.Refresh

    DoEvents
    DoCmd.SetWarnings False
    
    stDocName1 = "mkJCV"
    stDocName2 = "mkDBA_VR_VEHICLES"
    stDocName3 = "mkDBA_VR_HIREHISTORY"
    stDocName4 = "mkDBA_vCourtesyAvailability"
    'stDocName5 = "mkDBA_JOB_CONTROL_VIEW"
    stDocName6 = "mkDBA_JOB_ADDITIONAL_REFS"

    
    DoCmd.OpenQuery stDocName1, acNormal, acEdit
    DoCmd.OpenQuery stDocName2, acNormal, acEdit
    DoCmd.OpenQuery stDocName3, acNormal, acEdit
    DoCmd.OpenQuery stDocName4, acNormal, acEdit
    'DoCmd.OpenQuery stDocName5, acNormal, acEdit
    DoCmd.OpenQuery stDocName6, acNormal, acEdit
    
    Me.sbfDueIn.Form.RecordSource = "qryDueIn"
    Me.sbfCCarOverDue.Form.RecordSource = "qryCCarOverDue"
    DoCmd.SetWarnings True
    Me.sbfDueIn.Visible = True
    Me.sbfCCarOverDue.Visible = True
    Me.txtScrollDate = Date
    Me.txtPaint = DSum("[Pai]", "qryDueIn", "[DueIn]=txtScrollDate")
    Me.txtBody = DSum("[Bod]", "qryDueIn", "[DueIn]=txtScrollDate")
    Me.txtMET = DSum("[MET]", "qryDueIn", "[DueIn]=txtScrollDate")
    Me.txtInNow = DCount("[JobID]", "qryDueIn", "[OnSiteDateTime]<=txtScrollDate")
    Me.Requery
    
    Me.sbfDueIn.SourceObject = "sbfDueIn"
    Me.sbfCCarOverDue.SourceObject = "sbfCCarOverDue"
    Me.Visible = True
    

[HansV]

You mention that the code is in the On Click event of a command button on frmCallCentre. So when you use Me in the code, it refers to frmCallCentre. As far as I can tell, you don't hide frmCallCentre, nor do you open another form. So I'm not sure what the purpose of the line Me.Visible = True is.

One other thing: you first change the record source of the subforms sbfDueIn and sbfCCarOverDue, then later on you change the source object of the subform controls. That overrules the record source that you set earlier.

[D Willett]

Hi Hans
I added the Me.Visible=True just before I posted the code above hoping that would cure the problem, it doesn't, and the logic was aimed at forcing the form to be visible.
As you say, I don't hide the form anywhere so I guess it just trips over without any errors.
I have to change the recordsource of the two forms as the code routine refreshes the recordsource for both by running underlying queries. The source object changes depending on what the user wants to see, the main form being static and allowing the subform to change depending on the desired view.

I'm leaning towards the time it takes to run the queries, could that be an issue?

[HansV]

You could - at least temporarily - comment out the line

Code: Select all

    DoCmd.SetWarnings False

You'll have to confirm running each make table query; that should give them enough time to finish. What is the result?

[D Willett]

Hmm, the form doesn't unload and seems stabilised.

[HansV]

That means your hunch about a timing problem was probably right. Would it be very inconvenient to the users to have to confirm running the make-table queries?

[D Willett]

Yes, the hunch was correct. It would cause chaos ... perhaps I could build in a timer to allow the queries to run and then reload the form?
I'll have a dig around, there should be plenty of info on the subject.

Thank you Hans

[D Willett]

Not sure, but I've added another DoEvents after the queries run:

Code: Select all

    DoCmd.SetWarnings False
    DoCmd.OpenQuery stDocName1, acNormal, acEdit
    DoCmd.OpenQuery stDocName2, acNormal, acEdit
    DoCmd.OpenQuery stDocName3, acNormal, acEdit
    DoCmd.OpenQuery stDocName4, acNormal, acEdit
    'DoCmd.OpenQuery stDocName5, acNormal, acEdit
    DoCmd.OpenQuery stDocName6, acNormal, acEdit
    
    DoEvents

It seems to have settled the form down, I will have to test it further...

[HansV]

If the DoEvents is sufficient, fine. You're done.

If not:

The form has a Timer Interval property (unit: milliseconds; 0 means disable the timer) and an On Timer event. You could use a a module-level variable lngCount (declared at the top of the form's module) to keep track of which make-table query is to be executed. Set the Timer Interval to (for example) 1000, and set the variable to 1 in the On Click event procedure of the command button.

Move the code from the line

Code: Select all

    Me.sbfDueIn.Form.RecordSource = "qryDueIn"

on to a separate procedure DoTheRest, to be called later (see below)

In the On Timer event procedure, use a Select Case statement:

Code: Select all

Private Sub Form_Timer()
    Select Case lngCount
    Case 1
        DoCmd.OpenQuery stDocName1
    Case 2
        DoCmd.OpenQuery stDocName2
    Case 3
        DoCmd.OpenQuery stDocName3
    Case 4
        DoCmd.OpenQuery stDocName4
    Case 5
        DoCmd.OpenQuery stDocName6
    End Select
    lngCount = lngCount + 1
    If lngCount = 6 Then
        ' We're done
        Me.TimerInterval = 0
        lngCount = 0
        Call DoTheRest ' call procedure that executes the rest of the code
    End If
End Sub

[D Willett]

That's very useful Hans

Thank you again.

Kind Regards