How to pop up "Please Wait" message while running a process in access vba

Question

I have a button in my access form ,when clicks it checks some code to find data in excel cells. It is taking a while to finish the process. By that time i need to display Please wait message to users.

I'm calling this method, but not working

         Sub plswaitmsg()
         Dim statusmsg As Variant
         statusmsg = SysCmd(acSysCmdRemoveMeter)
         statusmsg = SysCmd(acSysCmdSetStatus, "Loading dropdown data,please wait.")
         End Sub

RubberDuck · Accepted Answer · 2014-09-23 17:17:36Z

3

You could use this status bar in execution mode. http://christopherjmcclellan.wordpress.com/2014/03/08/progress-bar-for-ms-access/

It's a bit much to post here in it's entirety, but it relies on two big things. The first is that you can create a new instance of any form on the fly in your code. This hinges on the fact that Access forms are really just glorified class modules. You can try it yourself by saving a new form with the default name "Form1", and then placing this code in a regular *.bas module.

Sub ShowForm()
    Dim frm as New Form_Form1
    frm.Visible = True

    Stop
End Sub

Note that I put a breakpoint in because the form will auto-destruct when it goes out of scope.

The second big thing is using DoEvents to give the OS enough time to repaint the screen.

Understanding these two things, you should be able to design a form with a textbox that changes size as your process runs. Also, I would actually recommend a UserForm instead of an Access Form. I didn't know it at the time, but you can use UserForms not just in Excel, but also in Access.

edited Sep 23, 2014 at 17:17

answered Mar 11, 2014 at 20:55

RubberDuck

12.9k5 gold badges56 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A. Masson Over a year ago

Also had to set the form's ShowModal property to false

Steven Martin · Accepted Answer · 2014-03-07 20:25:38Z

2

You cant display a MsgBox in Excel without pausing execution of the marco. You should use the Status Bar to display the message

Application.StatusBar = "Please be patient..."

then to clear it

Application.StatusBar = False

answered Mar 7, 2014 at 20:25

Steven Martin

3,3201 gold badge23 silver badges29 bronze badges

2 Comments

vuyy1182 Over a year ago

Thank you for reply. But it is not working in access.

Brad Over a year ago

in Access it is SysCmd acSysCmdSetStatus, "your message" which looks like what you had above...is I don't see what a MsgBox has to do with this?

J. Chomel · Accepted Answer · 2016-09-23 08:51:25Z

-1

Here is a very easy way:

The true process ending time is latest code line.

So you put finish massage as label caption above end sub code line.
Then wait massage label caption should be before processing.
Your command button can then use "please wait" message in mouse down input instead click input.

edited Sep 23, 2016 at 8:51

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

answered Sep 22, 2016 at 16:55

Yelay

1

Collectives™ on Stack Overflow

How to pop up "Please Wait" message while running a process in access vba

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related