435,255 Members | 2,575 Online + 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
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: HiAnyone know a quick and simple way of consecutively naming text boxnames (eg RR1, RR2, RR3 etc) or control source calculations? Forexample, the following calculation is for month 1:=IIf([m1rr] And [m1dbed] And [m1vd]=-1,1,IIf([m1rr]=-1 And [m1dbed]=-1And [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]=0And [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 timeconsuming if you have to go and do each one manually.ThanksJohn 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 wrote: There are a couple of ways.1. Create a template for the expression, and set the control source based onreplacing 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 NextEnd Sub2. Convert this expression into a a user-defined function, and call that fromthe 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 SelectEnd FunctionOn 28 Oct 2004 06:44:46 -0700, jo********@hotmail.com (John Smith) wrote:HiAnyone know a quick and simple way of consecutively naming text boxnames (eg RR1, RR2, RR3 etc) or control source calculations? Forexample, the following calculation is for month 1:=IIf([m1rr] And [m1dbed] And [m1vd]=-1,1,IIf([m1rr]=-1 And [m1dbed]=-1And [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]=0And [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 timeconsuming if you have to go and do each one manually.ThanksJohn 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 wrote: Thanks for your help Steve.I've taken a look at this but really don't have the understanding ofAccess to take advantage of what you've provided. I'm afraid that Isimply don't have sufficient knowledge of VB code to intepret and useit.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. 