CODEWITHSUNDEEP
ms-accessvbams-access-2010
spcurtis81
spcurtis81

Reputation: 285

Calling Form From VBA

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.

Upvotes: 1

Views: 2235

Answers (1)

HansUp
HansUp

Reputation: 97131

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

Upvotes: 2

Related Questions