Search code examples
vbams-accessms-access-forms

Access form doesn't appear but procedure runs

I have an Access form titled frmReportMetrics that opens as the Display Form. On Open it uses UserIsInGroup() to verify authority. If the user is not in the specified group then a Select Case - Case False statement closes frmReportMetrics and opens frmAccessDenied which is just a stop sign image with text alerting the individual that they do not have permission to use the application.

'Set default values to form items
Private Sub Form_Open(Cancel As Integer)
'Check to assure user has privileges to run front-end
    Select Case UserIsInGroup("The Reports")
        Case False
            DoCmd.Close acForm, Me.Name, acSaveNo
            DoCmd.OpenForm "frmAccessDenied", acNormal, "", "", , acNormal
            Exit Sub
    End Select
    Me.lblTabGoToManagersReportsPage.Visible = False

'Only display the label if the user is a member of the security group
    Select Case UserIsInGroup("The Reports - Managers")
        Case True
            Me.lblTabGoToManagersReportsPage.Visible = True
    End Select
End Sub

I then want the app to close automatically after 5 seconds diplaying a countdown. So I used pause() in frmAccessDenied

Private Sub Form_Activate()

    Me.lblClosingIn.Caption = "This form will close in 5 seconds"
    Pause (1)
    Me.lblClosingIn.Caption = "This form will close in 4 seconds"
    Pause (1)
    Me.lblClosingIn.Caption = "This form will close in 3 seconds"
    Pause (1)
    Me.lblClosingIn.Caption = "This form will close in 2 seconds"
    Pause (1)
    Me.lblClosingIn.Caption = "This form will close in 1 seconds"
    Pause (1)
    Application.Quit

End Sub

I'm sure it could be shorter...

Trouble is when testing, and I remove myself from the AD security group and open the Access front-end, the frmAccessDenied form does not pop up as expected but the app does exit in 5 seconds. I don't ever see frmReportMetrics either. I've tried _Load, _Open, _Activate, and _Current in frmAccessDenied but none of them allow frmAccessDenied to appear. _GoftFocus works and frmAccessDenied appears and I see the stop sign and alerts but then the countdown does not proceed and the app does not quit in 5 seconds.

As I step through frmAccessDenied I can Reset at any point and frmAccessDenied appears and I see the stop sign with the alerts as well as the appropriate Me.lblClosingIn.Caption at the bottom and if I step all the way through the app quits. enter image description here

Am I missing something like an Exit Sub somewehere? Or what Event Procedure should I use?

Both UserIsInGroup() and pause() work as expected, thanks @Nigel Heffernan and @Steve Mallory, respectively

TIA, Tim

PS @Erik A I added DoEvents all over and still frmAccessDenied is not painted.

Private Sub Form_Activate()
    DoEvents

    Dim I As Integer
    Dim sFirstPart As String
    Dim sSecondPart As String
    Dim sCompleteSentence As String

    sFirstPart = "This form will close in "
    sSecondPart = " seconds!"

    For I = 5 To 2 Step -1
        sCompleteSentence = sFirstPart & I & sSecondPart
        DoEvents
        Me.lblClosingIn.Caption = sCompleteSentence
        Pause (1)
    Next
    DoEvents
    Me.lblClosingIn.Caption = "This form will close in 1 second!"
    Pause (1)
    'Application.Quit
    DoEvents
    Me.lblClosingIn.Caption = "This form will close in 5 seconds"
    Pause (1)
    DoEvents
    Me.lblClosingIn.Caption = "This form will close in 4 seconds"
    Pause (1)
    DoEvents
    Me.lblClosingIn.Caption = "This form will close in 3 seconds"
    Pause (1)
    DoEvents
    Me.lblClosingIn.Caption = "This form will close in 2 seconds"
    Pause (1)
    DoEvents
    Me.lblClosingIn.Caption = "This form will close in 1 second"
    Pause (1)
    'Application.Quit
End Sub

And I tried a For Next but that didn't help either.

As I debug and run through the _Open, _Load, _Activate, and _Current with breakpoints at each, frmAccessDenied never becomes visible. I even tried with liberal applications of DoEvents!

Option Compare Database
Option Explicit

Private Sub Form_Activate()
    DoEvents
    Me.txtMessage.Value = "This application will close in 3 seconds!"
End Sub

Private Sub Form_Current()
    DoEvents
    Me.txtMessage.Value = "This application will close in 2 seconds!"
End Sub

Private Sub Form_Load()
    DoEvents
    Me.txtMessage.Value = "This application will close in 4 seconds!"
End Sub

Private Sub Form_Open(Cancel As Integer)
    DoEvents
    Me.txtMessage.Value = "This application will close in 5 seconds!"
End Sub

And when the form finally pops up it is with the value "This application will close in 2 seconds!"

What am I missing?

Voila! @Tom Robinson! That is a nice little nugget to understand the inner workings of Access. The form, though created is not visible until Access let's it be visible or until you make it visible. I went ahead and made the form visible on _Open and the countdown in _Load. Works to spec!

Option Compare Database
Option Explicit
Private Sub Form_Load()
Dim i As Integer
Dim txtFirstPart As String
Dim txtSecondPart As String
txtFirstPart = "This application will close in "
txtSecondPart = " seconds!"
For i = 5 To 2 Step -1
    Me.txtMessage.Value = txtFirstPart & i & txtSecondPart
    Pause (1)
Next
Me.txtMessage.Value = "This application will close in 1 second!"
Pause (1)
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
Private Sub Form_Open(Cancel As Integer)
    Me.Visible = True
End Sub
Solution

  • Add Me.Visible = True as the first line of Form_Activate.

    By default, a newly-opened form does not become visible until various form events have completed. This is so the user doesn't see distracting startup activities.