asked on

Insert Msg box in MS Access VBA Code

Hi Experts

I need a message box to appear when the following vba code is run to say the following and close down once the macro is complete

"This May Take Several minutes to run...........Please Wait!!!"

Private Sub Export_Quarterly_Reports_Click()

DoCmd.Echo False, "Exporting, Please Wait.........."

Dim RootMIdir As String
Dim FilePath As String
Dim xlPath As String
Dim rs As DAO.Recordset, sql As String
Dim xlobj As Object, wb As Object, ws As Object


xlPath = "\Report Templates\"
RootMIdir = "\Output Files"

Set xlobj = CreateObject("excel.application")
     Set wb = xlobj.Workbooks.Open(xlPath & "BB_Template.xls")
     Set ws = wb.Worksheets("AA_DATA")
     
          sql = "SELECT *"
          sql = sql & " FROM _DATA"
           
          Set rs = CurrentDb.OpenRecordset(sql)
          ws.Range("a2").CopyFromRecordset rs
                  
         wb.SaveAs Filename:=RootMIdir & "\REPORT" & Format(Now(), " dd-mmm-yy") & ".xls"
         
     wb.Close
     xlobj.Quit

Set ws = Nothing
Set wb = Nothing

Set xlobj = Nothing
Set rs = Nothing
         
DoCmd.Echo True

Open in new window

Jorge Paulino

A msgbox will stop the code from running. You can create a form for that or use a time to start your heavy code.

route217

ASKER

Hi Expert
I do not need a form just a Msg to say the above whilst the macro runs... Or to pop up for 10 seconds and disappear...

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jorge Paulino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clarkscott

Use the build in status bar (bottom left of screen).

Docmd.Echo True, "Type what you want to appear"

Docmd.Echo True, "" '---- turn it off

Scott C

spambler

Place a label in the middle of your existing form and set the visibility at the start and end of the code.

You can format the label to almost look like a message box without the OK button.