Array development

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

VBA Programming; How can I cycle through a record set and retrieve the field
name and the value for each field and store the information into an Array?
The purpose is to find out if a value is missing or is not the correct, if
the previous statement is true, then give me the name of the field so I can
append that information to an ‘error’ table. I have a procedure already
written but it seems too long with a lot of [If, Else] statements. I know
there must be a more 'elegant' way to write this with shorter code lines to
achieve the same results....
 
See the GetRows method in the help files. You haven't said whether you are
using a DAO or ADO recordset, but they both have a GetRows method which
returns an array.

Are you sure you actually need the array, though? I don't see anything in
what you've described that couldn't be done directly via the recordset or
even (depending on how complex the validation is) with an append query. For
example, if I wanted to append to an errors table all records where the
value of a specified field was not in a specified range, I'd do something
like this ...

Public Sub TestSub5()

Dim strSQL As String

strSQL = "INSERT INTO ErrorTable ( TestID, TestOne, TestTwo ) " & _
"SELECT Table1.TestID, Table1.TestOne, Table1.TestTwo " & _
"FROM Table1 WHERE (((Table1.TestOne) Not In ('a','b','c'))) " & _
"OR (((Table1.TestOne) Is Null));"
CurrentProject.Connection.Execute strSQL

End Sub
 
The more elegant way would be to filter the recordset to find the records
that have issues. Putting a recordset into an array is unnecessary given
the task you have described.
 
Hi

Here is a portion of code I wrote to audit changes to any given table into
the audit table. Just set your recordset (rst in this case) to the required
table, and then cycle through its fields.

The !Details is a field on my audit table, but you should get the idea

Hope this helps

For i = 0 To rst.Fields.count - 1
!Details = !Details & rst.Fields(i).Name & "(" &
rst.Fields(i).Value & ") : "
Next i


Regards
Chris
 
Back
Top