1

Sometimes trying to accomplish the most minor turns into a major headache... I somehow corrupted a database while experimenting to solve this issue. Now I'm really determined to figure this out. (I'll worry about the corrupt DB later!)

End Goal:

When opening .accdb file, automatically:

  1. open form unbound)
  2. run code to:
    • get Last Update Date from table
    • display Last Update Date in textbox on form (must be visible to user)
  3. if table data is outdated, run code to:
    • update data in table
    • display Update Progress Message(s) in textbox on form (must be visible to user)
    • when finished:
      • display Last Update Date in textbox on form (must be visible to user)

The problem is the goal of (must be visible to user).

I can't get the form to display anything until all the code's finished running, regardless of how I run the code (including: set the form to auto-open with FileOptionsDisplay Form; add an AutoExec macro to call code to open the form and run the update; or, run it manually.)


I've been experimenting with the form's opening events to see if I missed something.

According to the Order of Events documentation:

Working with data on a form

Form and control events occur as you move between records in the form and change data. For example, when you first open a form, the following sequence of events occurs:

Open (form) → Load (form) → Resize (form) → Activate (form) → Current (form) → Enter (control) → GotFocus (control)

At no point does the form visibly update. I've tried adding Me.Repaint and even tried Echo=True and DoEvents but obviously they didn't change anything.


My code for testing:

Private Sub Form_Open(Cancel As Integer)
    txtTest = "Form_Open()": MsgBox "1. Form_Open"
End Sub

Private Sub Form_Load()
    txtTest = "Form_Load()": MsgBox "2. Form_Load"
End Sub

Private Sub Form_Current()
    txtTest = "Form_Current()": MsgBox "3. Form_Current"
End Sub

Private Sub txtTest_Enter()
    txtTest = "txtTest_Enter()": MsgBox "4. txtTest_Enter"
End Sub

Private Sub txtTest_GotFocus()
    Echo True : Repaint : DoEvents
    txtTest = "txtTest_GotFocus()": MsgBox "5. txtTest_GotFocus"
End Sub

Private Sub txtTest_Change()                        : MsgBox "This Does't Run!" : End Sub
Private Sub Form_AfterUpdate()                      : MsgBox "This Does't Run!" : End Sub
Private Sub txtTest_Change()                        : MsgBox "This Does't Run!" : End Sub
Private Sub txtTest_BeforeUpdate(Cancel As Integer) : MsgBox "This Does't Run!" : End Sub
Private Sub txtTest_AfterUpdate()                   : MsgBox "This Does't Run!" : End Sub

The result of opening the form:

(animated screenshot)

(Note that the form's Activate and AfterUpdate events and the control's Change, BeforeEvent and AfterEvent events do not fire at all.)

Theoretically, the form should be visible behind the MsgBox, but neither running the actual update code, nor running a "pause" (loop with DoEvents) doesn't display the form either.

I could swear I've done this in the past without issue but no matter what I do try, the form only displays once all code is finished running.

9
  • I can replicate this. That's how it is, I believe. Access 2016. Commented Mar 6, 2018 at 12:01
  • Are you saying that it's specific to Access 2016? Commented Mar 6, 2018 at 12:02
  • 1
    I often use the Form_Timer event to trigger code. Set the Form.TimerInterval to 1, and set it to 0 in the Form_Timer event. Commented Mar 6, 2018 at 12:08
  • 1
    No, but Access 2016 is what I tested with. Commented Mar 6, 2018 at 12:18
  • @ErikvonAsmuth - I gather that a form's initial "paint" doesn't occur until all of the forms' "opening events" have finished execution? Do you know if this changed at some point? I'm using Access 2016 and I might be mistaken but I could've sworn that in older versions (maybe 2007?) I was able to run code on open of the database and provide visible updates to the user. Commented Mar 6, 2018 at 12:28

1 Answer 1

4

You can use the Form_Timer event to delay execution until the form fully finishes loading.

Private Sub Form_Load()
    Me.TimerInterval = 1 'Trigger 1 millisecond, but asynchronously from other tasks
                         'Triggers after AutoExec macro has finished
End Sub

Private Sub Form_Timer()
   Me.TimerInterval = 0 'Trigger only once
   'Your other tasks here
End Sub
Sign up to request clarification or add additional context in comments.

1 Comment

This method is easy to implement but I still have a feeling that the form can become visible during the Form_Load event.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.