CODEWITHSUNDEEP
databasevbams-accessloopstextbox
designspeaks
designspeaks

Reputation: 203

Loop through Textboxes in access database

I have an Access database that I want to loop through certain textboxes in order to do a calculation and display the answer in a seperate textbox. When I attempt to loop it loops through every control on my form (with the Form.Controls) method. I would like to only loop through 4 specific textboxes (txtbx1, txtbx2, txtbx3, txtbx4) when my button is clicked.

Explanation... TextBox_A contains a Number Upon button click take the number from TextBox_A, Multiply by 2800, then Divide by 12 Display the answer to the calculation in txtbx1.

I would do this for each of the 4 textboxes named above. Then have a "Total" textbox that adds up the total from each textbox (txtbx1, txtbx2, txtbx3, txtbx4). Please help, new to this and at a complete loss.

Upvotes: 1

Views: 11070

Answers (2)

Andy Brown
Andy Brown

Reputation: 5522

Every control has got a Tag property. Set the Tag property for textboxes whose values you want to include in your algorithm (say to "INClUDE"). It's free format text, so you can put what you like.

Then write code attached to some form event similar to this:

 Dim c As Control
Dim txt As TextBox

For Each c In Me.Controls

    'check it's a text box ...
    If TypeOf c Is TextBox Then

        'see if including ...
        Set txt = c
        If Len(txt.Tag) > 0 Then

            'do something here (I've coloured, to show works)
            txt.BackColor = 10

        End If

    End If

Next c

I've set an extra variable txt to refer to the textbox in question, so that I can get at the TAG property using autocompletion (I believe this is called a narrowing conversion!).

Upvotes: 5

KekuSemau
KekuSemau

Reputation: 6852

It would be easier if you rename TextBox_A (_B / _C etc) to TextBox_1 / _2 / _3 etc. And then try this: 

Dim i As Integer
For i = 1 To 4
    Controls("txtbx" & i) = Val(Controls("TextBox_" & i)) * 2800 / 12
Next

Otherwise, this would work too, but it is not well readable: 

= Val(Controls("TextBox_" & Chr(Asc("A") + i - 1))) * 2800 / 12

Upvotes: 0

Related Questions