asked on

Add indicator to code which file currently processing, out of # remaining files.

Hi Experts,

I have the following code running that suppose to process all files in a folder, and deletes each of those files upon completion.

Public Sub CallImportDataToCaspio()
   Dim StrFile As String, strTable As String, sFileStr As String
   Dim sDir As String
   Dim l As Long, s As String, i As Long
   Dim db As Database
   Set db = CurrentDb
   sDir = "H:\FTP\test\"
    'StrFile = Dir(sDir & "*PatChanges*")
    StrFile = Dir(sDir & "*" & sFileStr & "*")
    Do While Len(StrFile) > 0 And Not IsFileOpen(sDir & StrFile)
''        Do While IsFileOpen(sDir & StrFile)
''            ' do nothing
''        Loop
        If InStr(1, StrFile, "Full") = 0 And InStr(1, StrFile, "Part") = 0 Then
            i = CountOfRecords(sDir, StrFile)
            If i > 1 Then
                'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
                If InStr(1, StrFile, "PatChanges") > 0 Then
                    strTable = "Patients"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "SchChanges") Then
                    strTable = "Schedule"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "CGChanges") Then
                    strTable = "Caregivers"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "PatMedProfileChanges") Then
                    strTable = "Patients_Medications"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                End If
                If l > 0 Then
                    s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & strTable & "','" & StrFile & "'," & l & ",'Success') "
                Else
                    s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & strTable & "','" & StrFile & "'," & l & ",'Failure') "
                End If
                db.Execute s
            End If
        End If
        On Error Resume Next
        Kill sDir & StrFile
        StrFile = Dir
        On Error GoTo 0
    Loop
End Sub

Open in new window

Would like to have an indicator
1- Which file is it currently processing.
2- How many files currently remaining in this folder

When I tried this code

    Forms!formmain.LabelFileName.Caption = "Now processing: " & StrFile & " - " & i & " records"

Open in new window

it only shows if I stop execution (contrl+break), however while the code is running I can't get it to display.

Thanks

ASKER CERTIFIED SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

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

Start Free Trial

Norie

Try repainting the form.

Forms!formmain.Repaint

Open in new window

bfuchs

ASKER

Thank you experts!

bfuchs

ASKER

@Norie,
This only worked for first few files, but later stopped.
What else can be done?
Thanks,
Ben

bfuchs

ASKER

Hi Experts,
Any solution for the (displaying) repaint issue?
W/o that I cannot use the other suggestion either...
Thanks,
Ben

SOLUTION

Fabrice Lambert

membership

This solution is only available to members.

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

Start Free Trial

bfuchs

ASKER

@Fabrice,

This works, just wondering if the Form.Repaint is still necessary.

Also realized an issue when switching to another app then getting back to my Access form (the one who calls that function, where we want this message to be displayed), it shows a blank screen.
See attached.

What can be done for this?

Thanks,
Ben
Untitled.png

Fabrice Lambert

Repainting the form is rarely necessary, unless you need a very precise control on what is going on.

Ms Access showing something like your screen-shot is usually an indicator that a long processing is in the work.
Like above, give a chance for the application to respond to events with the DoEvents instruction.

bfuchs

ASKER

Hi,

Like above, give a chance for the application to respond to events with the DoEvents instruction

My program does the following
1- loops thru all files for specific folder and calls a function sending the name of the file.
2- That function loops thru all records of that file (files are data files in CSV format), and for every 25 records constructs a dynamic string to an API call.
3- Later it loops again thru all records of that file and for each record it calls an API.

So my question, where should I insert the do events (besides for the first loop #1 above, which I already did)?

Thanks,
Ben

Fabrice Lambert

guess at the and of each loops..

bfuchs

ASKER

So if a file has few thousand records (very often) it will need a do events for each record, will this not slow down the process?
Any other alternative?
Thanks,
Ben

Fabrice Lambert

you can count how many loops it does, and only perform a doevent every 10 or 100 loops.

dim i as long
while not(rs.eof)
    i = i + 1
    if(i mod 100) then
        doevents
    end if
wend

Open in new window

bfuchs

ASKER

Thank you!

SOLUTION

PatHartman

membership

This solution is only available to members.

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

Start Free Trial