Using array for shorter code

Question

I have the same line Repeating many times with small changes. i like to shorten it by using an array of objects

For example, instead of this code:

StartUpdateStr = "Update tblAfterSale SET "
EndUpdateStr = " WHERE IDAfterSale = "
IDAfterSale = Me.lblIDAfterSale.Caption

db.Execute StartUpdateStr & "Data1 = " & Me.Lable1.Caption & EndUpdateStr & IDAfterSale
db.Execute StartUpdateStr & "Data2 = " & Me.Lable2.Caption & EndUpdateStr & IDAfterSale
db.Execute StartUpdateStr & "Data3 = " & Me.Lable3.Caption & EndUpdateStr & IDAfterSale
db.Close

I'm looking for something like this:

Const dCaption = "Me.Lable1.Caption,Me.Lable2.Caption,Me.Lable3.Caption"
Public d(2) As Integer

Public Sub MyMacro()
    Dim vntTemp As Variant
    Dim intIndex As Integer
    vntTemp = Split(lCaption, "d")

    For intIndex = 0 To 2
        db.Execute StartUpdateStr & "Data"& intIndex &  " = " & d(intIndex) & EndUpdateStr & IDAfterSale
    Next
End Sub

Can someone write me the right syntax? Thank you

You can update multiple columns in a single query: update T set a=1, b=2, c=3 where ... so there is no need for 3 trips to the database. You should be using a parameterized insert to avoid injection vulnerabilities. — Alex K.
– Alex K., Commented Mar 13, 2018 at 18:42

Olivier Jacot-Descombes · Accepted Answer · 2018-03-13 18:41:11Z

1

You can simply access the labels by name with Me("Label" & i)

For intIndex = 0 To 2
    db.Execute StartUpdateStr & "Data" & intIndex &  " = " _
        & Me("Label" & (intIndex + 1)).Caption _
        & EndUpdateStr & IDAfterSale
Next

answered Mar 13, 2018 at 18:41

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Surya Prakash · Accepted Answer · 2018-03-13 20:00:36Z

0

I suppose you will be adding many labels in future. So can you use the below code

Private Sub PrintAllLabel()
     For Each ctl In Me.Controls
         If TypeName(ctl) = "Label" Then
              db.Execute StartUpdateStr & "Data" & intIndex &  " = " _
              & ctl.Caption _
              & EndUpdateStr & IDAfterSale
         End If
     Next ctl
 End Sub

edited Mar 13, 2018 at 20:00

answered Mar 13, 2018 at 19:54

Surya Prakash

3162 silver badges6 bronze badges

1 Comment

whylikethis Over a year ago

thank you all. all 3 comments help me :) my code is much nicer now.

Collectives™ on Stack Overflow

Using array for shorter code

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related