Using ARRAY element in IF...ELSE statement - Error

[RPACDN]

I am using an IF...ELSE loop to check the status of a set of buttons in a form. When I use the array element in an IF...ELSE loop nested inside a FOR loop, I am getting an error message. Here is my code:

  Dim tglButtons As Variant
 Dim fieldNames As Variant
 Dim searchValue As Variant

 tglButtons = Array("tglGermany", "tglFrance", "tglSRep)
 fieldNames = Array("Country", "Country", "Occupation")
 searchValue = Array("Germany", "France", "Sales Representative")

 Dim ctl As Control
 For intI = 1 To UBound(tglButtons)
   If [tglButtons(intI)].Value = -1 Then       ' I get the error on this line
       Debug.Print (tglButtons(intI) & ", " & fieldNames(intI) & ", " & searchValue(intI))
   End If
 Next intI

Error: Runtime error 2465: MS Access can't find the field '|1' referred to in your expression.

All I am doing is looping through the tglButtons array and for each item, determining if the button is pressed (.value = -1) and do something if pressed!

Do you see anything wrong in the way that I am declaring the array or accessing the element. Debug.print works fine!



Thank you for you time in advance.

[ranman256]

Arrays are obsolete. Collections are in. The controls are already in an 'array'...collection.

Dim ctl
For Each ctl In Controls
  If TypeName(ctl) = "CommandButton" Then
      MsgBox ctl.Name
  End If
Next
End Sub

[RPACDN]

Arrays are obsolete. Collections are in. The controls are already in an 'array'...collection.

Okay, didn't know that although I have come across "controls" arrays in the past!.

But how do I establish the relationship between the buttons and it's corresponding search string and the fieldname that I need to append to the final SQL search string?

So in my example, if user press the button, say, tglSRep, then the associated field name in the table will be "Occupation" and the associated search string will be "Sales Representative".
And in the loop, I would use the following command to genereate the SQL search string:

        strWHERE = strWHERE & "([myTable." & strFieldName & "]= " & strSearchString & ")
        Me.Filter = strWHERE
        Me.FilterOn = True

If you have any recommendations on how to do that, that would be greatly appreciated.

[ssanfu]

In answer to your question:

I modified your code a little:

Sub tesst1()
    Dim tglButtons As Variant
    Dim fieldNames As Variant
    Dim searchValue As Variant
    Dim intI As Integer

    tglButtons = Array("tglGermany", "tglFrance", "tglSRep")
    fieldNames = Array("Country", "Country", "Occupation")
    searchValue = Array("Germany", "France", "Sales Representative")

    Dim ctl As Control
    
    Debug.Print LBound(tglButtons)
    Debug.Print UBound(tglButtons)
    
    For intI = 1 To UBound(tglButtons)
        If tglButtons(intI) = -1 Then       ' I get the error on this line
            Debug.Print (tglButtons(intI) & ", " & fieldNames(intI) & ", " & searchValue(intI))
        End If
    Next intI
End Sub

It executes, BUT.....

In your post, there is a missing double quote after tglSRep in the array tglButtons.
I added the blue lines:
the variable "intI" was not declared
The debug lines are to show the lower and upper limits of the array.

For the error (the IF statement), you cannot/should not use the brackets or the .Value property.
The debug statement within the IF() statement will never execute because the array has predefined text values and you are trying to compare the text value to -1 (True?).

Try running this code:

Sub ArrayTest1()
    Dim tglButtons As Variant
    Dim fieldNames As Variant
    Dim searchValue As Variant
    Dim intI As Integer

    tglButtons = Array("tglGermany", "tglFrance", "tglSRep")
    fieldNames = Array("Country", "Country", "Occupation")
    searchValue = Array("Germany", "France", "Sales Representative")

    Dim ctl As Control

    Debug.Print "Lower bound = " & LBound(tglButtons)
    Debug.Print "Upper bound = " & UBound(tglButtons)

    For intI = 1 To UBound(tglButtons)
        Debug.Print "Counter = " & intI
        Debug.Print "tglButtons Array value " & intI & " = " & tglButtons(intI)
        If tglButtons(intI) = -1 Then       ' I get the error on this line
            Debug.Print (tglButtons(intI) & ", " & fieldNames(intI) & ", " & searchValue(intI))
        End If
    Next intI
End Sub

Edit: Our posts crossed. Perhaps you can explain what you are trying to do.

[ItsMe]

...Edit: Our posts crossed. Perhaps you can explain what you are trying to do.

Hey Steve, how's it going? I think it may have to do something with this thread here.
https://www.accessforums.net/program...ate-52740.html

[ssanfu]

@ItsMe
Still kicking
Yep, looks like same problem.... I'll have to study it.

[RPACDN]

Steve & itsme,

Yes, this is an issues related to that same problem. I didn't want to add to that post since that problem was already resolved and the post was getting too long as well.

Thanks for your help Steve. Yes, I forgot to declare "intI". That is fixed now!

So the only issue now remaining is the nested IF statement.

How can we determine if the toggle button is pressed to get in to the IF statement?

The following code works fine on it's own. That is, if the button tglSRep button is pressed, then it prints "Hello World".

If [tglSRep].Value = -1  Then
    Debug.Print "Hello World!"
End If

Debug.Print [tglButtons(intI)] yields the values tglGermany, tglFrance, tglSRep etc.
So I thought it would do the same thing inside the FOR statement, if I replace the [tglSRep] with [tglButtons(intI)]. But apparently not... that's where I am stuck now.

One solution that I found on the net is something like this below, but this seems like a round about way of doing it. There must be a property like the .value that we can check to determine if the button has been pressed or not!

Dim ButtonOneClick As Boolean

Private Sub tglSRep_Click()
ButtonOneClick = True
End Sub

Private Sub Submit_Click()
If ButtonOneClick Then
    MsgBox "Button 1 Was Clicked"
Else
    MsgBox "Button 1 Was NOT Clicked"
End If

ButtonOneClick = False
End Sub

[ssanfu]

I still don't have any idea what you are trying to accomplish.

Command buttons, toggle buttons, option group??? Control names?
The code is the easy part.

For your specific question about referencing the toggle buttons in a FOR..NEXT loop, I created an example dB. Does it help?

[RPACDN]

Sorry for the confusion.

What you have done in your sample is essentially what I am trying to do, BUT without the use of those Click events for each button.

So what you did works fine for me. The mistake was that I was trying to evaluate tglButtons(intI).value = -1 to see if the button is pressed, while all I have to do was what you did, which is Me(tglButtons(intI)) = True

And I don't even need the click events for each buttons, unless I need to do something else like display text "pressed" or so, as you have done!

The reason that I wanted a loop that would process the buttons is because I am dealing with almost 40 buttons and it only makes sense to create a loop to cycle through them and execute the commands as required.

The reason for the three arrays:

1. tglButtons = Array("tglGermany", "tglFrance", "tglSRep") ' Names of the Toggle Buttons
2. fieldNames = Array("Country", "Country", "Occupation") ' Names of fields in the Table associated with each toggle button
3. searchValue = Array("Germany", "France", "Sales Representative") 'Search string that has to be matched for each of the toggle button.

'Example: When tglSRep is pressed, the following search string will be generated in the FOR loop:
SELECT * from Customers WHERE Occupation = 'Sales Representative'

Thank you kindly for your help.

[ssanfu]

Glad you got it sorted out. I put the click events on the toggles for me, just to ensure I was getting the correct toggle status.

Good luck with your project......