Getting .value property when using a string and variable

Question

I am creating a form in Access which will be used as an order sheet for classroom materials. I have the available resources listed and a text box next to the resource where the user inputs the quantity they desire.

My VBA code checks to see if any entries have been made by using the following. (I am using Nz() to allow for Null results):

QuantCheck = Nz(Box1.Value, 0) + Nz(Box2.Value, 0) + Nz(Box3.Value, 0)

Where "QuantCheck" is the variable I am using in the IF statement which begins the workflow:

If QuantCheck > 0 Then

I would like to clean this up by using some kind of loop statement, however I am not able to extract the .value from a string. I would love something like the following which I could incorporate into a loop:

"Box"&VariableNumber.Value

From what I can tell, I am not able to use a string (concatenated or otherwise) as the base for the .value call.

It is interesting that there is a way to accomplish this when using a SQL statement. I have this elsewhere in the code which works nicely:

SQLStr = "INSERT INTO OrderRequests VALUES (cbSchool, txtName, Title" & x & ".caption, Box" & x & ")"

Here I have a variable "x" which increases with each loop to change the Title line, and the Box line.

Any help is appreciated.

Dick Kusleika · Accepted Answer · 2016-05-13 15:43:37Z

1

I suggest you use the Tag property of the controls. Put "QuantCheck" in the Tag property of any control you want to include. Then

Function QuantitiesExist(frm As Form) As Boolean

    Dim Ctrl As Control

    Const sQUANTCHK As String = "QuantCheck"

    For Each Ctrl In frm.Controls
        If Ctrl.Tag = sQUANTCHK Then
            If Nz(Ctrl.Value) > 0 Then
                QuantitiesExist = True
                Exit For
            End If
        End If
    Next Ctrl

End Function

Now you get self documenting code

If QuantitiesExist(Me) Then

And when you add/delete/change controls, you don't have to edit your code. Just set up new controls with the proper tags.

answered May 13, 2016 at 15:43

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

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

Comments

Gary Evans · Accepted Answer · 2016-05-13 14:31:44Z

0

You could loop through the control on the for checking the names and then if it is the one you wanted take an action on it, is this what you was thinking of?

Dim Ctrl As Control

For Each Ctrl In Me.Controls
    If Ctrl.Name = "TxtPath" Then ' "Box" & VariableNumber Then
        MsgBox Ctrl.Value
    End If
Next

answered May 13, 2016 at 14:31

Gary Evans

1,8904 gold badges18 silver badges30 bronze badges

Collectives™ on Stack Overflow

Getting .value property when using a string and variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related