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