Dynamic modification of code

[Rick Caird]

I am relatively new to VBA and have roots in TCL and 'C'. I was trying to
generalize a program by stuffing column names in an array and then using
array elements in a statement. For example:

Dim I As Integer
Dim Array(2) As String
Array(0) = "Contract Date"
Array(1) = "Paid Date"
Array(2) = "Some other date"

For I =0 To 2
If IsNull(rstTable!Array(I)) Then
Do Something
End If
Next I

Well, clearly it isn't working. I get a message saying that
rstTable!Array(I) is not a member of the group.

Is it possible to construct such a loop in VBA and get the substitution I
want?

Rick

[Azl]

hi rick, i think i know what you are trying to do.

the error is because you don't have a field call "Array(I)" in your table.

anything after the "!" is the physical field name fro the table.

what you need to do instead is use the 'fields' collection from the
recordset. you line should read

if IsNull(rstTable.Fields(Array(I))) then

good luck

Al.

"Rick Caird" <cc****@CairdConsulting.com> wrote in message
news:3f********@news.snappydsl.net...

I am relatively new to VBA and have roots in TCL and 'C'. I was trying to
generalize a program by stuffing column names in an array and then using
array elements in a statement. For example:

Dim I As Integer
Dim Array(2) As String
Array(0) = "Contract Date"
Array(1) = "Paid Date"
Array(2) = "Some other date"

For I =0 To 2
If IsNull(rstTable!Array(I)) Then
Do Something
End If
Next I

Well, clearly it isn't working. I get a message saying that
rstTable!Array(I) is not a member of the group.

Is it possible to construct such a loop in VBA and get the substitution I
want?

Rick

[Rick Caird]

Al,

Thanks. It is now working with your pointer.

Rick
"Azl" <A.B.Barker@_NoSpam_btinternet.com> wrote in message
news:bg**********@titan.btinternet.com...

hi rick, i think i know what you are trying to do.

the error is because you don't have a field call "Array(I)" in your table.

anything after the "!" is the physical field name fro the table.

what you need to do instead is use the 'fields' collection from the
recordset. you line should read

if IsNull(rstTable.Fields(Array(I))) then

good luck

Al.

"Rick Caird" <cc****@CairdConsulting.com> wrote in message
news:3f********@news.snappydsl.net...

I am relatively new to VBA and have roots in TCL and 'C'. I was trying to generalize a program by stuffing column names in an array and then using
array elements in a statement. For example:

Dim I As Integer
Dim Array(2) As String
Array(0) = "Contract Date"
Array(1) = "Paid Date"
Array(2) = "Some other date"

For I =0 To 2
If IsNull(rstTable!Array(I)) Then
Do Something
End If
Next I

Well, clearly it isn't working. I get a message saying that
rstTable!Array(I) is not a member of the group.

Is it possible to construct such a loop in VBA and get the substitution I want?

Rick