Changing names of text boxes to create loop

[TBOB61]

I have this small amount of code to gather values from a form's text boxes, perform a calculation, then place the results into other text boxes. It works fine. The example below is for one section. I will have up to 5 sections to perform this on. Instead of creating 5 command buttons and 5 sub routines, I would like create a loop to run through all five sections using one cmd button. Every text box name that I'm using ends in the section number it represents. S1 would be section 1, S2 would be section 2, etc. Everywhere there is an S1, I would like it increment to S2, S3, S4, and S5 to perform the calculation. I've tried a few things to get the text box name to change but have had no success. That's why I'm here. Can't really create the loop until I can figure out how get the right data. If I'm not able to do this or there is a different way to approach this then let me know. Thanks ahead of time.

Private Sub cmdSECTION1_Click()
On Error GoTo Err_ cmdSECTION1_Click

'SECTION 1

VALUE1 = Me.txtVALUE1

If optATTACHED = -1 Then
LENGTH = Me.txtLENGTHS1 - 4.921
Else
LENGTH = Me.txtLENGTHS1
End If

MCL = Me.cboTYPES1.Column(2)
MCLS = MCL * LENGTH

LAF = TRANSLOSS(MCLS, VALUE1, LENGTH)

TERMS = ((Me.cboCONS1 - 1) + (Me.cboPOLYS1 - 1) + (Me.cboTHRUS1 - 1)) * 0.03

Me.txtLOSSS1 = LAF + TERMS
Me.txtINITCLS1 = LAF + TERMS + 0.5

Exit_ cmdSECTION1_Click:
Exit Sub

Err_ cmdSECTION1_Click:
MsgBox Err.Description

Resume Exit_ cmdSECTION1_Click

End Sub

 

[lameid]

I am not sure I followed exactly what you are trying to do however a few ideas that may get you going in the right direction.

Every Control has
type or control type - I honestly forget the property
Name
Tag

You can loop over all the controls in the form quite easily...

dim cntl as Control

For each cntl in Me.Controls
  if cntl.name Like "*S#*" Then
    'Do something
  End if
Next cntl

Or loop controls...

Dim i as long
dim cntl as control
For i = 1 To 5 
  Set cntl = Me.Controls("txtS" & i)
  'Do stuff with cntl
Next i

 

[dhookom]

I hope you don't mind if I make some suggestions to upgrade your code and recommend using TGML to format your posts (like LameID did):

Private Sub cmdSECTION1_Click()
    On Error GoTo Err_ cmdSECTION1_Click
    
[COLOR=#4E9A06]    'SECTION 1
    ' VALUE1 isn't Dim'd so not sure if this will compile
    ' maybe LENGTH, LAF, MCL, MCLS, TERMS should be Dim'd
    ' Always add "Option Explicit" at the top of every Module
    ' Remove all hard-coded values from code and put them in variables at top
    ' I guessed at memory variable names but they should change to make sense to you
    ' Comments in code are also useful ;-)[/color]

    Dim VALUE1 as Double [COLOR=#4E9A06]'just a guess[/color]
    Dim dblLenSubstract as Double
    Dim dblMultiplier as Double
    Dim dblHalf As Double
    Dim dblOne as Double

    dblLenSubstract = 4.921
    dblMultiplier = 0.03
    dblHalf = 0.05
    dblOne = 1

    VALUE1 = Me.txtVALUE1

    If optATTACHED = -1 Then
        LENGTH = Me.txtLENGTHS1 - dblLenSubstract  
     Else
        LENGTH = Me.txtLENGTHS1
    End If

    MCL = Me.cboTYPES1.Column(2)
    MCLS = MCL * LENGTH

    LAF = TRANSLOSS(MCLS, VALUE1, LENGTH)

    TERMS = ((Me.cboCONS1 - dblOne) + (Me.cboPOLYS1 - dblOne) + (Me.cboTHRUS1 - dblOne )) * dblMultiplier 

    Me.txtLOSSS1 = LAF + TERMS
    Me.txtINITCLS1 = LAF + TERMS + dblHalf 

Exit_ cmdSECTION1_Click:
    Exit Sub

Err_ cmdSECTION1_Click:
    MsgBox Err.Description

    Resume Exit_ cmdSECTION1_Click

End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Shouldn't this:

Dim dblLenSubstract As Double
Dim dblMultiplier As Double
Dim dblHalf As Double
Dim dblOne As Double

dblLenSubstract = 4.921
dblMultiplier = 0.03
dblHalf = 0.05
dblOne = 1

be just this:

Const dblLenSubstract As Double = 4.921
Const dblMultiplier As Double = 0.03
Const dblHalf As Double = 0.05
Const dblOne As Double = 1

And do you really need to define 1?


---- Andy

There is a great need for a sarcasm font.