Conditional Formatting with multiple criteria VBA code?

[Guest]

Access 2003 headache, new guy thinks he's bitten off more than he can chew...

I have a report that has 11 controls to display the current subtotal of all
expenses relating to 11 budget catagories. These relate back to individual
budget limits for each catagory, limits set for each building in a project.
Building 1 has limits for Budget 1, Budget 2, Budget 3 etc.

This report needs to show where the budget subtotal to date is approaching
the limit and apply conditional formatting to highlight the appropriate
field. I thought about writing code for each control seperately, but I think
there has to be a way to capture the control names as a variable and the
control value as another so that a function can act on each on in some kind
of loop as set the formt prorties accordingly.

What
i have so far:
Dim strFieldName As String
Dim curFieldValue As Currency
strFieldName = "Me!txtBdgContExp"
curFieldValue = Me!txtBdgContExp

Select Case strFieldName
Case curFieldValue < 0
With strFieldName
.BackColor = 0
.ForeColor = 16777215
End With
Case... for the other criteria

 

[Douglas J. Steele]

I'm afraid I don't follow what you're trying to do.

You've got Select Case strFieldName, which means that it's looking at a
string (that contains the name of a field), but the Case underneath it is
looking at a numeric value.

To use a variable to refer to a specific control, you can use
Me.Controls(strFieldName), so perhaps what you want is:

With Me.Controls(strFieldName)
If Me.Controls(strFieldName) < 0
.BackColor = 0
.ForeColor = 16777215
Else
.BackColor = xxx
.ForeColor = yyy
End If
End With

 

[Guest]

Doug, you have given me the piece that I needed. Syntax is killing me. I got
this to work by using a For...Next loop to create a string of the field name.
I couldn't figure out how to use the string to reference a control. I need
to be able to create more levels of criteria so I used a Select Case rather
than If Then.

For a = 1 To 11 Step 1
strName = "txtBudget" & a
With Me.Controls(strName)
Select Case Me.Controls(strName)
Case Me.Controls(strName) < 0
....
End Select
End With
Next a

Thanks a bunch!
Eric

PS - Now all I have to do is figure out how to make the BudgetLimit relate
to the counter in the for next loop so that the case statements use the
correct values for calculations, but I believe that is another thread.