Text as field reference

General Computing Coding and Design
1

I have a form, thirty one fields, labeled ‘one’,‘two’ etc

I want to essentialy do a conditional formatting on those 31 fields, but using vba

the code I have is

Sub colourise()
For i = 1 To 31
    VarField = SpellNumber(i)
    Select Case Me.[I]VarField[/I].Value
    Case Grn
        Me.[I]VarField[/I].backcolour = vbGreen
    Case Blu
        Me.[I]VarField[/I].backcolour = vbBlue
    Case Else
        Me.[I]VarField[/I].backcolour = vbWhite
    End Select
Next
End Sub

the function SpellNumber turns ‘1’ into ‘one’ etc

Clearly using VarField as a reference in the way I have fails, but how do I get it in there? In excel I could use indirect but I can’t work out what to use in access.

Please Help

2

OK, so eddited to this, which works :slight_smile:

BUT

in a Continuous Form, it colours ALL the ‘one’ fields, not just the particular one with the data in it.

Sub colourise()
For i = 1 To 31
    VarField = SpellNumber(i)
    Select Case Me(VarField).Value
    Case "Grn"
        Me(VarField).BackColor = vbGreen
    Case "Blu"
        Me(VarField).BackColor = vbBlue
    Case Else
        Me(VarField).BackColor = vbWhite
    End Select
Next
End Sub
3

Do maybe if Len(Field Name) > X then fire Color Code not sure without getting a look at dataset…