Form doesn't update or show while code is running

[MikeDuffield]

Hi guys & girls,

I "borrowed" some file copying code from the web and modified it to suit my needs. It works just fine, but isn't very user friendly.

The goal is to have a form load, 4 labels which are black text that individually change to green text as each file is copied. The code I believe is correct, but for some reason the form doesn't show until all the code has been executed.

Is there a way to make the form "refresh" or something while the code is running?

I tried to make it "repaint" a couple of times but that didn't help. I'm sure it's an easy problem to solve, but I'm stumped!

Thanks in advance for any help. For reference, the code is below:

Private Sub Form_Current()
'Your db must have a reference set to the "Microsoft Scripting Runtime"!
'From a module, click Tools/Reference then select "Microsoft Scripting Runtime".
'The scripting file is located @ C:\Windows\System32\scrrun.dll for Windows XP.
    Me.Label130.Visible = True
    Me.Label33.Visible = False
    Me.Label29.Visible = True
    Me.Label34.Visible = False
    Me.Label30.Visible = True
    Me.Label35.Visible = False
    Me.Label31.Visible = True
    Me.Label36.Visible = False
    
On Error GoTo Err_bCopyDirectoryFiles_Click
 
    
    Dim fso As Scripting.FileSystemObject
    Dim sSourceDir As String, sDestinationDir As String
        Dim sSourceDir1 As String, sDestinationDir1 As String
            Dim sSourceDir2 As String, sDestinationDir2 As String
                Dim sSourceDir3 As String, sDestinationDir3 As String
                    Dim sSourceDir4 As String, sDestinationDir4 As String
    
    Set fso = New Scripting.FileSystemObject
    
    sSourceDir = "G:\Sales Area\AEC Database\*.*"
    sDestinationDir = "F:\AEC Database" 'No ending back slash for the final directory!
    
    fso.CopyFile sSourceDir, sDestinationDir, True
        
    Me.Label130.Visible = False
    Me.Label33.Visible = True
    
        Set fso = New Scripting.FileSystemObject
    
    sSourceDir1 = "G:\Sales Area\AEC Database\Drawings\*.*"
    sDestinationDir1 = "F:\AEC Database\Drawings" 'No ending back slash for the final directory!
      
    fso.CopyFile sSourceDir1, sDestinationDir1, True
    Me.Label29.Visible = False
    Me.Label34.Visible = True
    
        Set fso = New Scripting.FileSystemObject
    
    sSourceDir2 = "G:\Sales Area\AEC Database\Performance Packs\*.*"
    sDestinationDir2 = "F:\AEC Database\Performance Packs" 'No ending back slash for the final directory!
    
    fso.CopyFile sSourceDir2, sDestinationDir2, True
    
    Me.Label30.Visible = False
    Me.Label35.Visible = True
    
        Set fso = New Scripting.FileSystemObject
    
    sSourceDir3 = "G:\Sales Area\AEC Database\DUK Performance Packs\*.*"
    sDestinationDir3 = "F:\AEC Database\DUK Performance Packs" 'No ending back slash for the final directory!
    
    fso.CopyFile sSourceDir3, sDestinationDir3, True
    
    Me.Label31.Visible = False
    Me.Label36.Visible = True
    
        Set fso = New Scripting.FileSystemObject
    
    sSourceDir4 = "G:\Sales Area\AEC Database\Info\*.*"
    sDestinationDir4 = "F:\AEC Database\Info" 'No ending back slash for the final directory!
    
    fso.CopyFile sSourceDir4, sDestinationDir4, True
    
    MsgBox "File transfer complete!", vbInformation
 
Exit_bCopyDirectoryFiles_Click:
    Exit Sub
 
Err_bCopyDirectoryFiles_Click:
    If Err = 53 Then 'File not found
        Beep
        MsgBox "I can't find any files to copy for you!", vbInformation
        Exit Sub
    ElseIf Err = 76 Then 'Path not found
        Beep
        MsgBox "I can't find the folder you want me to copy to!", vbInformation
        Exit Sub
    Else
        MsgBox Err.Number & " - " & Err.Description
        Resume Exit_bCopyDirectoryFiles_Click
    End If
    
    Me.Label130.Visible = True
    Me.Label33.Visible = False
    Me.Label29.Visible = True
    Me.Label34.Visible = False
    Me.Label30.Visible = True
    Me.Label35.Visible = False
    Me.Label31.Visible = True
    Me.Label36.Visible = False
    
    DoCmd.Close

End Sub

 

[gemma-the-husky]

try adding DoEvents at points where you expect the screen to refresh

 

[apr pillai]

Try using DoEvents wherever you need to refresh the Form.

 

[MikeDuffield]

Ah that's great - DoEvents did exactly what was needed.

Thanks for your help!

Mike

 

[missinglinq]

Glad you got this fixed! Just to explain, Access executes code asynchronously. In practical terms this means that if you have, in code

CommandA
CommandB
CommandC

CommandA starts to execute, then CommandB starts to execute before the CommandA has finished executing, and so forth, on down the line. This can lead to timing problems, and as Dave and pillai have advised, the solution is to use DoEvents. This command, in essence, releases control to Windows to complete code execution before dropping down and executing the next line of code.

Linq ;0)>