There are a couple of ways.

1. Create a template for the expression, and set the control source based on

replacing a special substring. For example...

Public Sub SetupMonthlyCalcs() ' Call this from Form_Open

Const cstrCalcTemplate = "=IIf([m~rr] And [m~dbed] And [m~vd]=-1" & _

",1,IIf([m~rr]=-1 And [m~dbed]=-1 And " & _

"[m~vd]=0,2,IIf([m~rr]=-1 And [m~dbed]=0 " & _

"And [m~vd]=0,3,IIf([m~rr]=0 And " & _

"[m~dbed]=-1 And [m~vd]=-1,4,IIf([m~rr]=0" & _

"And [m~dbed]=-1 And [m~vd]=0,5," & _

"IIf([m~rr]=0 And [m~dbed]=0 And " & _

"[m~vd]=0,6,IIf([m~rr]=-1 And [m~dbed]=0 " & _

"And [m~vd]=-1,7)))))))"

Dim lngMonthNum As Long

Dim strSetControlSource As String

For lngMonthNum = 1 To 12

strSetControlSource = Replace(cstrCalcTemplate, "~", 1)

Me.Controls("MonthCalc" & lngMonthNum).ControlSource = _

strSetControlSource

Next

End Sub

2. Convert this expression into a a user-defined function, and call that from

the control expression - something like this...

Public Function CalculationXyz(ByVal MonthNum As Long) As Variant

Dim lngRr As Long

Dim lngDbed As Long

Dim lngVd As Long

Dim lngCombinationCode As Long

lngRr = Me!Controls("m" & MonthNum & "rr")

lngDbed = Me!Controls("m" & MonthNum & "dbed")

lngVd = Me!Controls("m" & MonthNum & "vd")

' Makes conditional logic simpler (below)

lngCombinationCode = Abs(blnRr) * 100 + _

Abs(blnDbed) * 10) + _

Abs(lngVd)

Select Case lngCombinationCode ' (rr, dbed, vd)

Case 111: CalculationXyz = 1

Case 110: CalculationXyz = 2

Case 100: CalculationXyz = 3

Case 11: CalculationXyz = 4

Case 10: CalculationXyz = 5

Case 0: CalculationXyz = 6

Case 101: CalculationXyz = 7

Case Else: CalculationXyz = Null

End Select

End Function

On 28 Oct 2004 06:44:46 -0700,

jo********@hotmail.com (John Smith) wrote:

Hi

Anyone know a quick and simple way of consecutively naming text box

names (eg RR1, RR2, RR3 etc) or control source calculations? For

example, the following calculation is for month 1:

=IIf([m1rr] And [m1dbed] And [m1vd]=-1,1,IIf([m1rr]=-1 And [m1dbed]=-1

And [m1vd]=0,2,IIf([m1rr]=-1 And [m1dbed]=0 And

[m1vd]=0,3,IIf([m1rr]=0 And [m1dbed]=-1 And [m1vd]=-1,4,IIf([m1rr]=0

And [m1dbed]=-1 And [m1vd]=0,5,IIf([m1rr]=0 And [m1dbed]=0 And

[m1vd]=0,6,IIf([m1rr]=-1 And [m1dbed]=0 And [m1vd]=-1,7)))))))

For month 2 I want to change m1rr for m2rr and so on - quite time

consuming if you have to go and do each one manually.

Thanks

John