Render name of object on form from variable, for use in VBA

JamesMcS · May 21, 2010

Hi! Right, I'm not even sure if this is the way to go with this one, let me explain:

I've got a table with manufacturers, countries, and sales for that country. The form is based on this table and is showing all manufacturers, countries and sales absolutely fine.

Now, I've added 12 checkboxes to the header of the form, one for each of the 12 countries, all true by default. What I want to do is have these checkboxes alter the form filter string depending on which ones are selected, but still have the filter string make sense....

Each of the check boxes is called "Org_Box1", "Org_Box2"... up to 12.

In the afterupdate of each box, I'm calling a function. The function contains an array of the 12 countries, whose position in the array matches up to the name of its check box on the form - so Org_Box1 is GB, and the first entry in the array is GB. Here's the code I've got so far:

Code:

Public Sub R24_Org_Filter()
Dim Org_Array As Variant, Counter_Val As Integer, Filter_String As String, Chkboxvar As String
Org_Array = Array("'GB01'", "'FR01'", "'DE01'", "'IT01'", "'AT01'", "'BE01'", "'DK01'", "'ES01'", "'IE01'", "'NL01'", "'NO01'", "'SE01'")
 
Filter_String = "[Salesorg]="
 
Counter_Val = 1
 
Chkboxvar = "[Org_Box" & Counter_Val & "]"
 
Do While Counter_Val < 13
[B]   [COLOR=red] If Chkboxvar = True Then[/COLOR][/B]
        Filter_String = Filter_String & Org_Array(Counter_Val) & " OR "
    End If
    Counter_Val = Counter_Val + 1
Loop
 
Forms![Mfr R24 Subform].Filter = Filter_String
Forms![Mfr R24 Subform].FilterOn = True
End Sub

What I'm trying to get it to do is pull back the name of the check box on the form, but from a variable (chkboxvar), so when counter_val is 1, chkboxvar contains "[Org_Box1]", referring to the check box on the form. If the box is checked, I want it to add "GB01" into the filter string (GB01 being the first entry in the array Org_Array, of course). Clever, I thought, but I get type mismatch - obviously because it's trying to evaluate a string to a true/false.

I should also mention that when it hangs on the if line, the varaiable chkboxvar does indeed contain the value "[Org_Box1]".

Any ideas on how I can get around this, or is there a method for doing this that I haven't thought of? Probably....

boblarson · May 21, 2010

Okay, first of all you have Chkboxvar declared as a STRING. It will never be TRUE.

If you want it to be a checkbox then you have to declare it as a checkbox:

Dim Chkboxvar As Checkbox

Then you can assign it to be what it is:

Set Chkboxvar = "Org_Box" & Counter_Val

ChrisO · May 21, 2010

In A2K3 you can’t have square brackets around a control name so [Org_Box1] would be an illegal name.

You would also have to use: -
Me(Chkboxvar)

stopher · May 21, 2010

JamesMcs

Here’s how I’d do it (not necessarily the best way)...

Code:

Dim ctl As Control
Dim Filter_String As String

Filter_String = ""

For Each ctl In Me.Controls
    If Left(ctl.Name, 7) = "Org_Box" Then
        If ctl.Value Then
            Filter_String = Filter_String & ",'" & ctl.Tag & "'"
        End If
    End If
Next ctl

'deal with the preceeding comma (if there is one)
If Left(Filter_String, 1) = "," Then
    Filter_String = "[Salesorg]=In(" & Replace(Filter_String, ",", "", 1, 1) & ")"
Else
    Filter_String = "[Salesorg]='FakeOrg'"
End If

Debug.Print Filter_String

A couple of explanations...

Instead of using an array to hold the orgs, I have put the org string in the tag properties for the respective checkbox (without any quotes). The benefit of doing this is it’s dead easy to reference and also I don’t lose the plot about which check box goes with which index in the array.

I’ve used the control object which makes it easy to loop through all controls generically in the form and not worrying specifically about what they are called in order to reference their properties. I can then simply check if the control has a name starting with “obj_box”. Strictly speaking you should also check that the control is a checkbox.

The Filter_String = "[Salesorg]='FakeOrg'" expression deals with the case where there are no selections i.e. you don’t want enter records returned by the filter. It works as long as you don’t have an org called “FakeOrg”!

Instead of creating a long list of OR’s, I’ve use the IN() function. Your expression wouldn’t work anyway because....
This works:
[Salesorg]=’GB01’ OR [Salesorg]=’DE01’

This doesn’t:
[Salesorg]=’GB01’ OR ‘DE01’

Also, your code would have generated a trailing OR which would have needed to be dealt with. I have the same problem that I have to deal with a preceding comma.

Hth
Chris

JamesMcS · May 22, 2010

Thanks for your help guys, I'm going to chew on this one for a while....

Bob, the problem I came up against was that chkboxvar won't hold value contained in a field, but the name of a control - can variables be used in this way? I thought it was stretching it a bit...

Chris, thanks, I wasn't aware of that - how would you handle a control name with a space in it then? I've not come across [] causing a problem when referring to controls...

Stopher, I'll give your code a go now, there's a few functions I've not used in there before so that'll keep me entertained for a bit! The hanging OR was going to be a problem definitely - I thought about an if right(filter_string,3)=" OR" and chopping it off with a left function after that.

Will let you know how I get on!

JamesMcS · May 22, 2010

And, ctl.value brings up the error 'object doesn't support this property or method'. How do I identify if an object is a check box in VBA?

JamesMcS · May 22, 2010

Hmmm so I think the question is in fact, can (a) a variable hold the name of a control (of course it can, but can the variable be used in place of the control name in VBA?) and (b) can you reference the properties of that control , i.e. [variable].value and the like?

I've played around a bit, and thought about holding the control names in an array, in the same order as the sales orgs, so counter_val can be used to cycle through both arrays:

Code:

Dim Org_Array, Ctl_Array As Variant, Counter_Val As Integer, Filter_String As String, Ctl_Array_Var As Control
Org_Array = Array("'GB01'", "'FR01'", "'DE01'", "'IT01'", "'AT01'", "'BE01'", "'DK01'", "'ES01'", "'IE01'", "'NL01'", "'NO01'", "'SE01'")
Ctl_Array = Array("[Org_Box0]", "[Org_Box1]", "[Org_Box2]", "[Org_Box3]", "[Org_Box4]", "[Org_Box5]", "[Org_Box6]", "[Org_Box7]", "[Org_Box8]", "[Org_Box9]", "[Org_Box10]", "[Org_Box11]")
Filter_String = "[Salesorg]="
Counter_Val = 0
    Do While Counter_Val < 12
 
    Ctl_Array_Var = Ctl_Array(Counter_Val)
 
        If Ctl_Array_Var.Value = True Then
 
            Filter_String = Filter_String & Org_Array(Counter_Val) & " OR [Salesorg]="
 
        End If
 
        Counter_Val = Counter_Val + 1
 
    Loop
 
Filter_String = Left(Filter_String, Len(Filter_String) - 15)
 
Forms![Mfr R24 Subform].Filter = Filter_String
Forms![Mfr R24 Subform].FilterOn = True

Now the error is object variable or With block variable not defined. I guess this is because I've declared ctl_array_var as a control and I'm trying to assign a string to it... sigh.... "Print ctl_array(counter_val)" brings back "[org_box0]" in the immediate window, and org_array(counter_val) brings back "'GB01'".

ChrisO · May 22, 2010

For a control name with a space, Access will put an underscore in place of the space.

So control name A B becomes A_B.

We refer to it as Me.A_B and it shows in Intellisense as A_B.

But these are very easy things to test, just create a new text box and call it A B.

JamesMcS · May 22, 2010

OK, the plot thins, then thickens again.... This is what I've got now:

Code:

Public Sub R24_Org_Filter()
Dim Org_Array, Ctl_Array As Variant, Counter_Val As Integer, Filter_String As String, Ctl As Control
Org_Array = Array("'GB01'", "'FR01'", "'DE01'", "'IT01'", "'AT01'", "'BE01'", "'DK01'", "'ES01'", "'IE01'", "'NL01'", "'NO01'", "'SE01'")
Filter_String = "[Salesorg]="
Counter_Val = 0
  
For Each Ctl In Forms![mfr R24 subform].Controls

        If Ctl.ControlType = acCheckBox And Counter_Val < 12 Then
        
            Filter_String = Filter_String & Org_Array(Counter_Val) & " OR [Salesorg]="
            Counter_Val = Counter_Val + 1
            
        End If
   
Next Ctl

Filter_String = Left(Filter_String, Len(Filter_String) - 15)
Forms![mfr R24 subform].Filter = Filter_String
Forms![mfr R24 subform].FilterOn = True
MsgBox Filter_String
End Sub

As the code is, it's working fine - it's building the string OK:

Code:

[Salesorg]='GB01' OR [Salesorg]='FR01' OR [Salesorg]='DE01' OR [Salesorg]='IT01' OR [Salesorg]='AT01' OR [Salesorg]='BE01' OR [Salesorg]='DK01' OR [Salesorg]='ES01' OR [Salesorg]='IE01' OR [Salesorg]='NL01' OR [Salesorg]='NO01' OR [Salesorg]='SE01'

So - I just need to work out how to check if the control's value is true. ctl.value brings back 'object doesn't support this property or method' - as I type in "ctl.", the drop down list appears and 'value' isn't in there. Is this because it's cycling through other controls before getting to the check boxes? I'm going to see if grouping has any benefit now...

stopher · May 22, 2010

Try:

Code:

Public Sub R24_Org_Filter()
Dim Org_Array, Ctl_Array As Variant, Counter_Val As Integer, Filter_String As String, Ctl As Control
Org_Array = Array("'GB01'", "'FR01'", "'DE01'", "'IT01'", "'AT01'", "'BE01'", "'DK01'", "'ES01'", "'IE01'", "'NL01'", "'NO01'", "'SE01'")
Filter_String = "[Salesorg]="
Counter_Val = 0
  
For Each Ctl In Forms![mfr R24 subform].Controls

        If Ctl.ControlType = acCheckBox And Counter_Val < 12 Then
        
            If Ctl.Value = True Then
                Filter_String = Filter_String & Org_Array(Counter_Val) & " OR [Salesorg]="
                Counter_Val = Counter_Val + 1
            End If
            
        End If
   
Next Ctl

Filter_String = Left(Filter_String, Len(Filter_String) - 15)
Forms![mfr R24 subform].Filter = Filter_String
Forms![mfr R24 subform].FilterOn = True
MsgBox Filter_String
End Sub

JamesMcS · May 22, 2010

New one on me.... Anyhoo I got it working, thanks to everyone - Stopher's idea pointed me in the right direction:

Code:

Dim Filter_String As String, Ctl As Control
Back_To_Top:
Filter_String = "[Salesorg]="
For Each Ctl In Forms![mfr r24 subform].Controls
 
If Ctl.ControlType = acCheckBox Then
 
If Ctl.Value = True Then
 
Filter_String = Filter_String & "'" & Ctl.Name & "'" & " OR [Salesorg]="
 
Else
End If
 
Else
End If
 
Next Ctl
If Filter_String = "[Salesorg]=" Then
MsgBox "At least one Sales Org must be selected."
Forms![mfr r24 subform].[GB01].Value = True
GoTo Back_To_Top
Else
End If
Filter_String = Left(Filter_String, Len(Filter_String) - 15)
Forms![mfr r24 subform].Filter = Filter_String
Forms![mfr r24 subform].FilterOn = True

Seems slightly inelegant with all the ifs in it, but I'll have time after it's deployed to tidy it up a bit.

Using "for each control" and an if statement within to say if .controltype=accheckbox and .value=true comes back with an error if the object doesn't have the value property - hence the embedded if, it seems that these two conditions can't be evaluated in the same condition - unless of course every object on the form has the value property. Similarly the checkbox control doesn't have a tag property - so I got around it by renaming the check boxes to their respective sales org.

Thanks again for all your insight - invaluable as always.

JamesMcS · May 22, 2010

Cheers stopher I was posting at the same time.

Render name of object on form from variable, for use in VBA

JamesMcS

Keyboard-Chair Interface

boblarson

Smeghead

ChrisO

Registered User.

stopher

AWF VIP

JamesMcS

Keyboard-Chair Interface

JamesMcS

Keyboard-Chair Interface

JamesMcS

Keyboard-Chair Interface

ChrisO

Registered User.

JamesMcS

Keyboard-Chair Interface

stopher

AWF VIP

JamesMcS

Keyboard-Chair Interface

JamesMcS

Keyboard-Chair Interface

Similar threads

Users who are viewing this thread