CODEWITHSUNDEEP
vbams-access
Richie
Richie

Reputation: 11

Getting .value property when using a string and variable

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.

Upvotes: 1

Views: 113

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33175

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.

Upvotes: 1

Gary Evans
Gary Evans

Reputation: 1890

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

Upvotes: 0

Related Questions