Use variable as field name in IIf expression (1 Viewer)

[heslopg]

Very new to VBA. I know this question probably has a very simple answer but I'm not able to find anything about it on the interwebs.

When using the IIf function, I am wanting to use the value of a variable ("subj") to determine which field in the form I am wanting to evaluate.

So, if my variable name is "subj", how would I do this? Since, to check the field "S1" of the form, my code would look like this:

(IIf([S1], 1, 0))

...I would assume that my variable would need to be wrapped in some way to identify it as such rather than as the name of the form. How would I do this?

 

[jzwp22]

You need to define a variable in code using the Dim statement

DIM subj as long (sets subj as a long integer number datatype; there are other types as well depending on your needs)

Now if you want to assign the result of the IIF() to subj, the statment would look like the following. If your code is triggered by a form event or by an event associated with a control on the form, you can use the shorthand me. to designate the form control holding the value you want to evaluate.

subj=IIf(me.S1=???, 1, 0)

 

[heslopg]

I have my variable defined already. I'm not trying to assign the variable to anything; it already has its value assigned to it. What I'm wanting to do is use the variable "subj" inside the IIf function and assign what it returns to a different variable ("subject_checked").

The field I am evaluating is a Yes/No datatype so I am looking to find whether it is checked or not.

So, what I'm looking for is something like this:

subject_checked = IIf(subj, 1, 0)

 

[jzwp22]

For a yes/no field or a boolean variable, -1 or TRUE=checked, typically zero or FALSE is used for unchecked.

The IIF() function returns a value based on whether the condition is true or false. Is that what you are really after? Or, perhaps you need the IF..THEN...ELSE...END structure to do something based on whether the field is checked or not.


IF field=-1 THEN

execute...

ELSE

do something else

END IF

 

[heslopg]

I am needing the value of subject_checked, if true, to be returned as 1 if true and 0 if false. My question here is mainly about the syntax for using the variable inside the IIf function to specify which field I am wanting to check.

 

[jzwp22]

You can use this:



or this

subject_checked = IIf(subj=TRUE, 1, 0)

or this

subject_checked = IIf(subj=-1, 1, 0)

I believe that the following will also work provided that subj is defined as a boolean data type. You would not need any delimiters around the variable.

subject_checked = IIf(subj, 1, 0)



subject_checked = IIf(subj, 1, 0)

 

[heslopg]

What if subj is a string that contains the field name? For instance:

subj = "S1"

How would I then use the variable in the IIf function? What delimiters would I need to use?

 

[jzwp22]

No, you cannot do that. S1 is the name of a control, you need to do your comparison to the value in the control.

Can you explain more about the bigger picture? Are you trying to loop through a series of yes/no controls on your form and you don't want to write a line of code for each control?

 

[heslopg]

That's exactly it. There are 56 checkboxes (named S1-S56) and each time one is checked I am to change the value of a text field (#Subj) to the total number of boxes checked.

Currently, what I have done is created an array (array_subj) and added the names of each checkbox to it. Then I do a loop where I am trying to check to see if the control has the value of yes or no. If yes, I add 1 to the variable "subject_checked" each time. After the loop, I assign the value of "subject_checked" to the text field controler "#Subj".

If I am not able to check to see if the box is checked with the method I am using, can you suggest another way I might do this?

 

[jzwp22]

You can use a loop to loop through the sequentially named controls (S1 through S56) and count the number of checkbox controls that are checked. You would increment a counter for each checked checkbox control. When you are done looping, you would assign the value of the counter to the textbox control on the form. I used the on click event of a button on the form to execute the code.

The code would look something like this:

Private Sub Command32_Click()

Dim i As Long
Dim mycounter As Long

For i = 1 To 56
If Me.Controls("S" & i) = -1 Then
mycounter = mycounter + 1
End If
Next i
Me.subj = mycounter
End Sub

BTW, having 56 fields may indicate that your table is not normalized.

 

[heslopg]

Adding the variable for the control name seems to be causing a problem. Here is what I have:

Private Function CountSubjects()
    Dim i As Long
    Dim subj As Long
    
    For i = 1 To 56
        If Me.Controls("S" & i) = -1 Then
        subj = subj + 1
        End If
    Next i
    
    [#Subj] = subj
End Function

Private Sub S1_AfterUpdate()
Call CountSubjects
End Sub

So first I have my function that counts all checked boxes and then a sub that runs after a control has been updated. There is one of these sub for each checkbox.

What I have above does nothing to my form. However, if I take out the variable "i" and use a string for the control name, I get the expected results (which is always 0 if unchecked or 56 if checked, but at least it is working).

If Me.Controls("S1") = -1 Then

Does "i" need to be converted to a string before being added to another string? Does this not matter?

 

[jzwp22]

Does "i" need to be converted to a string before being added to another string? Does this not matter?

If you concatenate a number to a string the result is a string. The number does not have to be converted prior to the concatenation.

What is the name of the control on the form that holds the count? If it is [#Subj], you need the me. shorthand or the full form reference. Further, I would strongly recommend not using special characters (#) in your control names (or field or table names for that matter). The # sign has special significance in Access as a date delimiter.

By the way, there is no need to store the count in a table, so that control should be unbound.

I've attached an example database, please check out the form. I've used 5 checkbox controls as an example.

 

[MSAccessRookie]

Is this the kind of thing that you are looking for?

    Dim ctl As Control
    
    For Each ctl In Me.Controls
    
        If (ctl.ControlType = acCheckBox) Then
            
                'Do Something Here
 
        End If
        
    Next ctl

 

[mdlueck]

I would assume that my variable would need to be wrapped in some way to identify it as such rather than as the name of the form. How would I do this?

Eval() is VBA's tool to dynamically evaluate a variable entity. It is weaker than Interpret in the Rexx / Object Rexx / Open Object Rexx language. It is not able to interact with run-time created objects, and a bunch of other restrictions.

A while ago I was shopping for comparable functionality in VBA, and ended up finding a roundabout solution... write more code rather than a tight loop with dynamic ability to refer to different objects.

Fun/side project: Build dynamic SELECT query using ADODB.Command / .Parameters objs
http://www.access-programmers.co.uk/forums/showthread.php?t=230838

 

[heslopg]

so... you're going to enjoy this.

i'll start out by mentioning that i did not create this form. hopefully that will allow some of you to forgive me for what's to come (along with the other suggestions for how to better the form).

the reason my original code was not working is because i was using incorrect control names. the 56 checkboxes are all labeled "Subject x", with x of course being the number of the checkbox. however, after the 30th checkbox, named "S30" the naming switches to use "B" and starts the number over. so the 31st checkbox is named "B1" and the 56th is named "B26". because i was using the incorrect names, the function would stop. if i used the same code as above but changed my loop to only run from 1-30, then it would work properly.

so, here is my working code:

Private Function CountSubjects()
    Dim i As Long
    Dim subj As Long
    Dim p As String
    
    For i = 1 To 56
        If i < 31 Then
            p = "S" & i
        Else
            p = "B" & (i - 30)
        End If
        
        If Me.Controls(p) = -1 Then
        subj = subj + 1
        End If
    Next i
    
    [#Subj] = subj
End Function

thank you all so much for your help. i apologize for my lack for thorough research before posting.

 

[jzwp22]

Glad to hear that you got it sorted out!