Updating an unbound box during a proceedure

[JamesBBB]

Hi All,

Sorry, its me again, everytime I think I'm getting somehwhere, something else comes up and stumps me, I dont know why this is not working but any help would again, be very much appreciated.

Basically I have a routine that gets a direcory name, it then goes and gets all the file names from that directory and feeds them into a table, it then moves to the next directory and does the same, this process is repeated until there are no more directories. The actual process takes about 6 minutes or so to run through all the directories, so I would like to keep the user informed of the progress of the job by pasting in the directory name the procedure is working on in an unbound box called [Checker].

When the procedure runs, it puts the first directory in the unbound box and then it does not update again until the entire procedure has completed.

Any ideas, because Im completely confused.

My code is below

many thanks

James

Private Sub Command0_Click()

DoCmd.SetWarnings False
DoCmd.OpenQuery "DeleteIssues"
DoCmd.SetWarnings True
Dim rst As New ADODB.Recordset
rst.Open "Titles", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTable

While Not rst.EOF
Collection = rst![F1] ' Get record from table
[CHECKER].value = Collection ' Unbound box on form

Me.Repaint ' Tried using these to get the unbound box to update
Me.Refresh ' Tried using these to get the unbound box to update

Seldrv = Sysdrive & ":" & Sysdir & "Issues.txt"
Selbat = Sysdrive & ":" & Sysdir & "Issues.bat"
Issuedir = Sysdrive & ":" & Sysdir & Collection & "\*.cbr"

runstr = "Dir " & Chr$(34) & Issuedir & Chr$(34) & " /b > " & Chr$(34) & Seldrv & Chr$(34)

'write a batch job to get file names from a directory
Dim fso, myfile
Set fso = CreateObject("Scripting.filesystemobject")
Set myfile = fso.CreateTextFile(Selbat, True)
myfile.writeline (runstr)
myfile.Close

'Run the Batch file
adx = Shell(Selbat, vbMinimizedFocus)

'wait for a few seconds while the batch job finishes
Call sSleep(3000)

'Transfer the text from the text file to a table
DoCmd.TransferText acImportDelim, , "Issues", Seldrv

rst.MoveNext ' repeat for the next directory
Wend
rst.Close
MsgBox "Procedure Complete"
End Sub

 

[PHV]

Have a look at the DoEvents function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JamesBBB]

Hi PHV

You have come to my rescue yet again, perfect

many thanks

James