Calling Form From VBA

Question

I created a VBA function in Access 2010 which opens a progress bar in "Form!" and increases the size of "frame1" incrementally and updates a caption box. Running this function in "Step Into" debug mode produces the expected results (all macros run fine, dialog opens and updates etc.

However, when I call this VBA through a "traditional" macro (sorry not sure about terminology) built in the wizard... i.e. RunCode with Function =Import_Function(), while all the queries run find and the success message box appears at the end, the form or any of it's updates do not display. I may be missing something completely obvious here but if anyone could point me in the right direction I'd be grateful.

My function code is as follows...

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Function Import_Function()

DoCmd.SetWarnings False
    DoCmd.Hourglass True
    DoCmd.OpenForm "Form1", acNormal, "", "", acReadOnly, acWindowNormal
    Forms!Form1!Frame1.Visible = True
    Forms!Form1!Prog_Description.Caption = "Caption1..."
    Forms!Form1!Frame1.Width = 2000
    Sleep (2000)
    DoCmd.OpenQuery "Q002a", acViewNormal, acEdit
    Forms!Form1!Prog_Description.Caption = "Caption2..."
    DoCmd.TransferSpreadsheet acImport, 10, "Import Sheet", "File Location\File Name.xls", True, "A4:AA50"
    Forms!Form1!Frame1.Width = 4000
    Sleep (2000)
    Forms!Form1!Prog_Description.Caption = "Caption3..."
    DoCmd.OpenQuery "Q002b", acViewNormal, acEdit
    Forms!Form1!Frame1.Width = 5000
    Sleep (2000)
    Forms!Form1!Prog_Description.Caption = "Caption4..."
    DoCmd.OpenQuery "Q002c", acViewNormal, acEdit
    Forms!Form1!Frame1.Width = 6000
    Sleep (2000)
    Forms!Form1!Prog_Description.Caption = "Caption5..."
    DoCmd.OpenQuery "Q002c", acViewNormal, acEdit
    Forms!Form1!Frame1.Width = 7000
    Sleep (2000)
    Forms!Form1!Prog_Description.Caption = "Caption6..."
    DoCmd.OpenQuery "Q002a", acViewNormal, acEdit
    Forms!Form1!Frame1.Width = 8000
    Sleep (2000)
    Forms!Form1!Prog_Description.Caption = "Caption7..."
    DoCmd.OpenQuery "Q002e", acViewNormal, acEdit
    Forms!Form1!Frame1.Width = 9000
    Sleep (2000)
    Forms!Form1!Prog_Description.Caption = "Caption8..."
    DoCmd.DeleteObject acTable, "Temp_T"
    Forms!Form1!Frame1.Width = 10000
    Sleep (1000)
    Forms!Form1!Prog_Description.Caption = "Update Complete!"
    DoCmd.Hourglass False
    DoCmd.Close acForm, "Form1"
    Beep
    MsgBox "Process Successfully Updated", vbInformation, "Process Update"

End Function

and then from there, as mentioned earlier, I am running it from a macro using the "RunCode" command.

@Remou Thanks for the response. The need for this method is actually aesthetic. I'm using this particular code as learning example with a view to using features such as the sleep API and a manual progress bar in a future splash screen for my application. Don't know whether my motives make more sense now. I can understand where you're coming from it terms of advice and it is much appreciated. — spcurtis81
@HansUp That's done it. Thanks very much for that. I know that this is a long winded way of doing this but I like the way this looks visually and it will be a great help with my future plans. Cheers. — spcurtis81
You might want to take a look at this. It's a generic reusable form I created for a progress bar. christopherjmcclellan.wordpress.com/2014/03/08/… — RubberDuck

HansUp HansUp · Accepted Answer · 2014-03-11T15:39:24

Examine the Access DoEvents Function help topic: "Yields execution so that the operating system can process other events."

In your case, call DoEvents each time after you adjust Frame1.Width so that the display can be updated.

Forms!Form1!Frame1.Width = 2000
DoEvents

Calling Form From VBA

1 Answers