How To Display MS Access 2010 Form Open Event Message

Hi Experts,

In the Form Open Event I check a Condition and if it is true when the Form displays I want to:
- Set the Background Colour of an Unbound Field [PROCESS_MSG] to Red.
- Display Message Text in the [PROCESS_MSG] for a time.  e.g. 2 Seconds.
- Set the [PROCESS_MSG] Field Background Colour to White.
- Set the [PROCESS_MSG] filed value to spaces.

Here is the current code.
If [WRK_EVENT_LOCK_040] = "Y" Then
   'MsgBox "Get Error Message - W0021-Event is locked. Changes cannot be made."
   Dim COLOUR_LITE_RED_W0021 As String
   [COLOUR_LITE_RED_W0021] = RGB(255, 102, 102)
   Dim COLOUR_WHITE_W0021 As String
   [COLOUR_WHITE_W0021] = RGB(255, 255, 255)
   Dim CURRENT_TIME_W0021 As Date
   [CURRENT_TIME_W0021] = Now
   Dim DURATION_SECONDS_W0021 As Integer
   [DURATION_SECONDS_W0021] = 2
   PROCESS_MSG.BackColor = [COLOUR_LITE_RED_W0021]
   Dim WRK_MSG_ID As String
   [WRK_MSG_ID] = "W0021"
   [PROCESS_MSG] = MessageLookup01(WRK_MSG_ID)
   Do While DateDiff("s", [CURRENT_TIME_W0021], Now()) < [DURATION_SECONDS_W0021]
      DoEvents
   Loop
   PROCESS_MSG.BackColor = [COLOUR_WHITE_W0021]
   [PROCESS_MSG] = " "
   GoTo STEP_100
ElseIf [WRK_EVENT_LOCK_040] = "N" Then
   GoTo STEP_100
End If

The problem is that after the form displays, the Message Text never displays in the [PROCESS_MSG] Field and the [PROCESS_MSG] field Colour only displays White.  i.e. The Message never displays with the Red Background.
Increasing the Duration simply delays the time it takes for the Form to Open.  If I comment out the 'Do While Loop' and next two statements (PROCESS_MSG.BackColor = [COLOUR_WHITE_W0021]
   [PROCESS_MSG] = " ") it displays the message with the Red Background.

How can I get this to work the way I want it to?
Thanks.
Bob C.
Bob CollisonSystem ArchitectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
the Open event might be too early to check the condition/content  of the controls, since the data are not fully loaded yet,
try placing your codes in the LOAD event of the form.
Bob CollisonSystem ArchitectAuthor Commented:
Hi Rey,

I have tried placing the code in the Form Load Event and also the Form Activate Event but I get the same results.  The Form Current isn't an option since it occurs each tine the form data changes.

Ideally it would be best to be within an Event that occurred after the form was 'Displayed' - Rendered.
Thanks.
Bob C.
Rey Obrero (Capricorn1)Commented:
what is the record source of the form?
is this a single form?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Gustav BrockCIOCommented:
You really should remove that loop and just open the form.

In the OnLoad event, set the timer to 2000, and then use the timer event to turn off the message.

/gustav
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,
To answer Rey.
The Form View is 'Continuous Forms'.

With reference to Gustav.
I'm not sure what you mean by 'Timer'.  If you mean the   [DURATION_SECONDS_W0021] = 2  I haven't changed it.  If I should use some other statement I need to know what it would be.

I have removed the 'Do' Loop' and following two statements from the Form On Open Event and placed them in a Form Load Event.  I still get the same result.  i.e.  The [PROCESS_MSG] Filed is White and contains spaces.  The Message and Red Background never appear.

Thanks.
Bob C.
Gustav BrockCIOCommented:
No, I mean the Timer property of the form and the OnTimer event.

Remove this completely:

   Do While DateDiff("s", [CURRENT_TIME_W0021], Now()) < [DURATION_SECONDS_W0021]
       DoEvents
   Loop

/gustav
Rey Obrero (Capricorn1)Commented:
what is this function >  MessageLookup01(WRK_MSG_ID)  ?


after this line

 [PROCESS_MSG] = MessageLookup01(WRK_MSG_ID)

'ADD this lines

Me.Repaint
DoEvents
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,
Gustav,
The Form On Timer Event doesn't have any Code and the Timer Interval is zero.

I have removed the  'Do While DateDiff("s", [CURRENT_TIME_W0021], Now()) < [DURATION_SECONDS_W0021] DoEvents Loop' code from the Form Open Event and placed it in the Form Load Event.  This didn't work.  If I remove it from both Events then the Message Displays with the Red Background which is the First Step but the Second Step of replacing the Message with spaces and changing the [PROCESS_MSG] field Background to white obviously won't happen.

The ' MessageLookup01(WRK_MSG_ID' Function looks up the Message to display from a Table using the [WRK_MSG_ID] = "W0021" parameter.  This works ok in all scenarios and is not an issue.

Rey,
The Me.Repaint doesn't have any affect. on solving the problem.

Thanks.
Bob C
Rey Obrero (Capricorn1)Commented:
upload a copy of your db
Bob CollisonSystem ArchitectAuthor Commented:
Hi Rey,

It is over 70 Meg of Code plus a number of Back End DBs.

I'll have to create a limited one with the issue in it which will take a while.

Thanks.
Bob C.
Rey Obrero (Capricorn1)Commented:
just include the concerned form and related objects
Gustav BrockCIOCommented:
> The Form On Timer Event doesn't have any Code and the Timer Interval is zero.

No ... what did you expect? You are the only one to insert code in your app.

This is the code you need:
Private Sub Form_Load()

    Me!txtMessage.Value = "Get Error Message - W0021-Event is locked. Changes cannot be made."
    Me!txtMessage.Visible = True
    
    ' Start timer.
    Me.TimerInterval = 2000
    
End Sub

Private Sub Form_Timer()

    ' Stop timer.
    Me.TimerInterval = 0
    
    Me!txtMessage.Visible = False
    
End Sub

Open in new window

/gustav
Message.accdb
Bob CollisonSystem ArchitectAuthor Commented:
Hi Experts,
Gustav
Thanks for the Demo DB.  I have changed my code to reflect that in the Demo DB but I get the Error "...Can't Find the field 'TimerInterval' referred to in your expression. (2465)' within the Form Load Event.

Rey
I have attached the Message Update DB that contains my original code that doesn't work as I originally described.

Click the F-41-005 - Event Group Registration Master Maint Button to launch the form that has the problem.  The code in question is in the Form Open Event at STEP_040.  If the 'Do While Loop and next two statements are commented out (as I am originally supplying) the message displays correctly on the form but doesn't change to spaces with a White Background after 2 Seconds.  In the uncommented version the message doesn't display and the Background is always White.

Thanks.
Bob C.
Message-Update.accdb
Gustav BrockCIOCommented:
> Can't Find the field 'TimerInterval'

Then you probably have made some typo. It's not a field but a property:

    https://msdn.microsoft.com/en-us/library/office/ff836371.aspx

/gustav
Gustav BrockCIOCommented:
Here is the working demo.

/gustav
Message-Update.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob CollisonSystem ArchitectAuthor Commented:
Hi Gustav,
Thanks for the solution.  I wasn't aware of this function previously.

I'll wait for a bit to hear back from Rey before assigning the points.

Thanks.
Bob C.
Gustav BrockCIOCommented:
The form's timer is smart for this and the natural choice.

Should you - in another case - need to pause the application, use the API call Sleep as demonstrated here:

http://www.experts-exchange.com/Database/MS_Access/A_17684-Modern-Metro-style-message-box-and-input-box-for-Microsoft-Access-2013.html

Move down to this paragraph:

Simulating dialogue mode

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.