Optimizing array comparisons

[TooGoon]

'sup
I am making an unbound entry form. I load all the names of the controls and their assigned values into an array(x, 1), and then load the names of the fields in the query where the record will be created into another field. After a bit of comparison based upon the names of the fields and controls (they are the same), I assign the values to the fields, like so:
iCount = 0
For Each Field In aField
i = 0
Do Until i > iEnd
'Exclude MDRNo and MDRInitiatedBy from entry
If Field = "MDRNo" Then
i = iEnd
ElseIf Field = "MDRInitiatedBy" Then
i = iEnd
ElseIf Field = aName(i, 0) Then
rst(Field) = aName(i, 1)
Debug.Print Field & " " & aName(i, 0) & " " & aName(i, 1)
i = iEnd
End If
i = i + 1
Loop
Next Field

Very simple. But it is also VERY slow. It takes about 3 seconds to complete this stage of the operation.
What can I do to make this quicker?

Thanks
Ezra

 

[JohnYingling]

You can test "MDRNo", "MDRInitiatedBy" outside the Do Until.
For/Next faster than Do/Until.
ARe both in seq so that you can quit on ">"

iCount = 0
For Each Field In aField
i = 0
'Exclude MDRNo and MDRInitiatedBy from entry
Select Case Field
Case "MDRNo", "MDRInitiatedBy"
Case else
For I = 0 to iEnd
If Field = aName(i, 0) Then
rst(Field) = aName(i, 1)
Debug.Print Field & " " & aName(i, 0) & " " & aName(i, 1)
Exit for
End If
Next
End Select
Next Field

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing

http://www.VBCompare.com

Compare Code

http://www.VBSortGen.com

Generate Sort Class in VB

http://www.Vbcompare.com/Check.Htm(1979

Check To MS)

 

[TooGoon]

thankyou for your more efficient code. It works quite well. Unfortunately, it does not make a noticeable change in speed.

There are about fifty elements in each area, resulting in 2500 comparisons. Is it possible to remove an element from the array aName once it has been matched? I just tried setting the values to null, and editing the code to this statement:

If Field = Null Then
ElseIf Field = aName(i, 0) Then
rst(Field) = aName(i, 1)
'Debug.Print Field & " " & aName(i, 0) & " " & aName(i, 1)
aName(i, 0) = Null
aName(i, 1) = Null
Exit For
End If

But again, there is not a noticeable increase in speed.

 

[PHV]

How is the aField array populated ?

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

 

[RoyVidar]

If you are trying to do performance tests with the Debug.Print line, try commenting that and see what happens.

The elements of your array, are they variants? Would using string be applicable?

You sure some of these comparisions can't be done thru queries?

Roy-Vidar

 

[PHV]

And what about something like this ?
For i = 0 To UBound(aName, 1)
Select Case aName(i, 0)
Case "MDRNo", "MDRInitiatedBy"
Case Else
rst(aName(i, 0)) = aName(i, 1)
Debug.Print aName(i, 0) & " " & aName(i, 1)
End Select
Next i
If you have some aName not in the Fields collection, you may consider an On Error Resume Next instruction.

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

 

[MichaelRed]

A more 'traditional' approach would be to use a class to represent the data and assign the values via the class. Unless the form (design) is changing quite rapidly there is little need to asign the query fields dynamically.





MichaelRed
mlred@verizon.net

 

[TooGoon]

A more 'traditional' approach would be to use a class to represent the data and assign the values via the class. Unless the form (design) is changing quite rapidly there is little need to asign the query fields dynamically."

I'm sorry, I'm a bit new to this vba thing. Would you mind explaining that a little more?

Well, thanks everyone for your help. I just did a timer test, to locate exactly what was taking so long, and it appears that the array comparison itself is quite fast, little more than .023 of a second. BUT, the writing to the file is incredibly slow. I isolated the matches and their data, and then used a seperate loop to assign the data to their corresponding fields. And that is where all the slowdown is.
Yes, I am using a variant data type for the array, so that is no doubt a source of the slowdown, but unless I want to split everything into multiple arrays according to data types (and i'd really rather not) in order to accomodate the multitude of check, text, list, and other boxes in the form, I need to keep the variant array.

Is there any other method of assigning these values to the recordset?

 

[MichaelRed]

To respond to the second issue first, Ms. A. Forms / controls "GENERALLY" use the text type for displayed information (no slight intended to check boxes, option buttons, etc) so as long as the recordset data type is correctly assigned in the "table". the corecion will do the translation will work quite nicely to convert to the various numeric (including Date) types.

I hesitate toi start a tautology on Class modules for ever so many reasons. On the other hand, I think you would find the use of the functionality to be an improvenment in the processing, so will instead suggest that you do some amount of independent research into the basics both here within Tek-Tips (Advanced Search: Key words [Class | Module | Let | Get] and various combinations thereof. Also, asa 'bit new' at this I would suggest that an extended visitation to your local bookstore (computer sectio) will be a valuable investment. While there peruse several or more of the available texts re VISUAL BASIC as well as the slightly different subject of Ms. A. While Ms. A. utililzes a variation of Visual Basis, it includes some additional topics and many of the Ms. A. tomes do not delve as deeply into the details of the code. I usually have at least one third party 'reference' manual on each for the current version of each program.





MichaelRed
mlred@verizon.net