Calling Same Function Simultaneously from Access macro

[DougTucker]

I have an MS Access macro that calls a VBA function several times in a row and I'm getting incorrect data in some of the fields. I suspect that the function is collecting the results for the first call, then the function is being called again, and might be sending back the results from the 2nd call, and returning them in response to the first call.

QUESTION: If I have back-to-back calls to the same function, does a macro wait for the first function to complete before it begins the next call of the same function?

SUMMARY OF FUNCTION: The function "RowsToColumns" takes a multi-line freeformat text block and parses it into up to 10 columns, moving to the next column at each line break in the text.

Here's a summary of the macro:
CONDITION: DLookUp("[Record ID]","[Settlement Instructions_STAGING]") Is Not Null
{the table has data}
ACTION: [RunCode] [RowsToColumns ("FreeTextColumn_A")]
ACTION: [RunCode] [RowsToColumns ("FreeTextColumn_B")]
ACTION: [RunCode] [RowsToColumns ("FreeTextColumn_C")]

The result should be that all data from column [FreeTextColumn_A] is parsed into [FreeTextColumn_A0] through [FreeTextColumn_A10]. The same process is repeated for [FreeTextColumn_B] and [FreeTextColumn_C]. All of these columns already exist in the table.

The problem is that data is being saved to the wrong rows. This leads me to believe that the function is being called simultaneously and variables are changing before the function returns a value.

Here's a copy of the function:

Function RowsToColumns(TextBlockName As String)
'Created by Doug Tucker on 7/20/2009
'This Function parses out individual rows in a single long text field
'of a specified table, and uses those rows to populate a series of columns
'in a table.  All records from the array are translated into columns
'until the end of the array, then the function advances to the next row
'and repeats the process until reaching the end of the table.
'INPUT "TextBlockName" is the name of the column to be parsed.
'OUTPUT columns begin with "TextBlockName", plus a squential number "0-n"
'All output columns (0-n) must already exist in the table.
'
Dim mydb As Database        'Current database
Dim rst As Recordset        'Read into recordset
Dim WholeString As String   'Contents of entire cell (multi-rows)
Dim StringArray             'Array of rows of WholeString
Dim RowContents As String   'Contents of a single row of the cell
Dim Check, Counter          'Variables for Do-Loop
Dim strDelimiter As String
Dim MoreRows As Boolean
'Removed 9/8/2009 - expanded table size: _
    Const LastBeneBankColumn As Integer = 9 'Last Bene Bank Name Column (BeneficiaryBank9)

strDelimiter = Chr(13) & Chr(10)    'Line Break, new Line
Set mydb = CurrentDb
Set rst = mydb.OpenRecordset("Select * from [Settlement Instructions_STAGING] order by [Record ID]")

rst.Edit
Do While Not rst.EOF 'Outer Loop; continue through entire table; exit if EOF
    MoreRows = True: Counter = 0 'Initialize Variables
    Do While MoreRows = True
        WholeString = Nz(rst("[" & TextBlockName & "]"), "") 'Column containing whole string
        If WholeString = "" Then
            MoreRows = False 'Stop immediately if array is empty
            Exit Do 'get out of inner loop immediately
        Else
                StringArray = Split(WholeString, strDelimiter)
                'Update data table with value from array
                RowContents = StringArray(Counter)
                'CurFieldName = FullFieldName & Counter
                rst.Edit
                'rst!CurFieldName = RowContents
                rst("[" & TextBlockName & Counter & "]") = RowContents
                rst.Update
                If Counter = UBound(StringArray) Then   'This is the last value of array
                'Added 9/3/2009:  Stop if there are more than 10 rows in array (ignore the rest)

                    MoreRows = False  'Set value of flag to false
                    Else
                    '    If Counter = LastBeneBankColumn Then
                    '    MoreRows = False 'exit loop on next round
                    '    Else
                        Counter = Counter + 1
                    '    End If
                    End If
        End If
    Loop 'Until MoreRows = False
    rst.MoveNext    'Go to next row of recordset;
Loop                'Exit when EOF

'<DEV MESSAGE>
'MsgBox "File is Done"
rst.Close           'Close the recordset
Set rst = Nothing   'Empty the recordset
End Function

Thank you in advance for your help!

~ Doug T

 

[MazeWorX]

Seems to me that you are editing the table (like you say) on each loop then looping again on an already edited table before the function has finshed. You may try writing the results to a new table

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[missinglinq]

No, Access does not wait for one command or collection of commands (as in a Macro) to complete before executiong the next command, and this can, at time, lead to timing problems.

On way around this is to use the command DoEvents between each command or running of a Macro.

From Access Help:

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!