By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,255 Members | 2,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,255 IT Pros & Developers. It's quick & easy.

Consecutive Numbering of Text Box Names or Control Properties in Access

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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


Nov 13 '05 #2

P: n/a
Thanks for your help Steve.

I've taken a look at this but really don't have the understanding of
Access to take advantage of what you've provided. I'm afraid that I
simply don't have sufficient knowledge of VB code to intepret and use
it.

Thanks for your time anyway.
John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

P: n/a
Oops - mistake in example #1. Should be ... = Replace(cstrCalcTemplate, "~",
lngMonthNum)

On Thu, 28 Oct 2004 14:22:21 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:
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


Nov 13 '05 #4

P: n/a
Do you have any specific questions on this that we could help with?

It's not really that hard get to the VBA code module behind a from, and paste
some VBA code. Do note my other reply, though in which I found a typo in my
example.

On 30 Oct 2004 05:07:24 GMT, John Smith <jo********@hotmail.com> wrote:
Thanks for your help Steve.

I've taken a look at this but really don't have the understanding of
Access to take advantage of what you've provided. I'm afraid that I
simply don't have sufficient knowledge of VB code to intepret and use
it.

Thanks for your time anyway.
John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.