Loop to find lowest text box value

[10kz]

I've got a number of text boxes that have calculations in the control source that work off other fields in the form. There are 32 of these boxes named R1 - R32

I want to run VBA in the background to:

1. Pass through all 32 text boxes - R1 through to R32
2. Ignore any text box values that are 0 or null
3. Identify the lowest value text box
4. Change the color of the lowest value text box to green

Thanks

 

[Ranman256]

If you can't use a query ,then your design may be wrong.
Having to search across 32 fields is not a normalized db.
But
You can loop thru them,

VVal = 99999
For each ctl in controls
   If typeName(ctl) = "textbox" then 
        If ctl.value < vVal then
               VVal = ctl.value
               VName = ctl.name
        End if
   End if
Next

 

[Cronk]

There could be other text boxes on the form that contain values. In which case the loop might be

for n = 1 to 32
  set ctl =me("R" & n)
  VVal = ctl.value
  VName = ctl.name
next

 

[10kz]

Silly question again

The loop cycles each of the controls in the form via the if statement, it looks at the text box value and asks if it is less than VVal (99,999).

If this turns out to be true it does what exactly? Sorry I'm still getting my head around loops.

 

[MarkK]

I think cronks loop is the best way to go, but you need to add the validation too, like...

    Dim i As Integer
    Dim tb As Access.Textbox
    
    For i = 1 To 32
        Set tb = Me.ccontrols("R" & i)  [COLOR="Green"]'get control reference[/COLOR]
        If Nz(tb, 0) > 0 Then          [COLOR="Green"] 'only deal with valid boxes[/COLOR]
[COLOR="Green"]           'find the lowest in here somehow[/COLOR]
        End If
    Next

 

[Minty]

Also what if more than one value is the lowest - which one do you highlight? the first, last or all the low ones ?