I can't figure out how to use a variable in place of a
![column] statement in a With statement. In the following
code, I would like to replace [m1] with a variable so that
I can use the same code on different columns by changing
the variable rather than reusing the entire code. If I
try using a string variable in place of [m1] it doesn't
work. Do I need to declare a different type of variable?
Here is the code that I'm trying to use:

Do While myCount <> 0
With MyTbl
If ![Type] = dType Then
If ![m1] = colVal Then
mrank = mrank
Else
mrank = mrank + 1
End If
Else
If ![Type] <> dType Then
mrank = 1
dType = ![Type]
End If
End If
colVal = ![m1]
..Edit
![m1Rank] = mrank
..Update
..MoveNext
End With

myCount = myCount - 1

Loop

May need to avoid the With block, but you can use:
MyTbl("MyField")
so
strFieldName = "MyField")
MyTable(strFieldName)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Tim" <(E-Mail Removed)> wrote in message
news:00ab01c37df3$22795dd0$(E-Mail Removed)...
> I can't figure out how to use a variable in place of a
> ![column] statement in a With statement. In the following
> code, I would like to replace [m1] with a variable so that
> I can use the same code on different columns by changing
> the variable rather than reusing the entire code. If I
> try using a string variable in place of [m1] it doesn't
> work. Do I need to declare a different type of variable?
> Here is the code that I'm trying to use:
>
> Do While myCount <> 0
> With MyTbl
> If ![Type] = dType Then
> If ![m1] = colVal Then
> mrank = mrank
> Else
> mrank = mrank + 1
> End If
> Else
> If ![Type] <> dType Then
> mrank = 1
> dType = ![Type]
> End If
> End If
> colVal = ![m1]
> .Edit
> ![m1Rank] = mrank
> .Update
> .MoveNext
> End With
>
> myCount = myCount - 1
>
> Loop

I didn't know you could do it that. I learn something
every day. I typically use

rs.Fields(strFieldName)

>-----Original Message-----
>May need to avoid the With block, but you can use:
> MyTbl("MyField")
>so
> strFieldName = "MyField")
> MyTable(strFieldName)
>
>--
>Allen Browne - Microsoft MVP. Perth, Western Australia.
>Tips for Access users - http://allenbrowne.com/tips.html
>
>"Tim" <(E-Mail Removed)> wrote in message
>news:00ab01c37df3$22795dd0$(E-Mail Removed)...
>> I can't figure out how to use a variable in place of a
>> ![column] statement in a With statement. In the
following
>> code, I would like to replace [m1] with a variable so
that
>> I can use the same code on different columns by changing
>> the variable rather than reusing the entire code. If I
>> try using a string variable in place of [m1] it doesn't
>> work. Do I need to declare a different type of
variable?
>> Here is the code that I'm trying to use:
>>
>> Do While myCount <> 0
>> With MyTbl
>> If ![Type] = dType Then
>> If ![m1] = colVal Then
>> mrank = mrank
>> Else
>> mrank = mrank + 1
>> End If
>> Else
>> If ![Type] <> dType Then
>> mrank = 1
>> dType = ![Type]
>> End If
>> End If
>> colVal = ![m1]
>> .Edit
>> ![m1Rank] = mrank
>> .Update
>> .MoveNext
>> End With
>>
>> myCount = myCount - 1
>>
>> Loop
>
>
>.
>