Multiple Values using Tag Field Grouping (1 Viewer)

Juett · Jul 25, 2019

Hi everyone,

I found a good piece of VBA code on this site to group multiple form textBox and comboBox fields together using the TAG property and then apply the enabled or disabled property to them, based on a selected value from a comboBox. The function is:

Code:

Function ShowHideControls(frm As Form, strTAGtoUse As String)
Dim ctl As Control

For Each ctl In frm.Controls
intControlType = ctl.ControlType
If (intControlType = acComboBox) Or (intControlType = acTextBox) Then
     ctl.Enabled = (InStr(1, ctl.Tag, strTAGtoUse) > 0)
     End If
Next ctl
End Function

The form action is:

Code:

Private Sub Form_Current()
ElseIf Me.Parent.Parent.[No: Stations] = "7" Then
    r = ShowHideControls(Me, "7")
ElseIf Me.Parent.Parent.[No: Stations] = "8" Then
    r = ShowHideControls(Me, "8")
    End If
End Sub

The above works perfectly for single tags (for example, if 7 is selected in the comboBox, then only fields with the TAG "7" are enabled, and the same for "8" and so on), but........what I'd like to do is display all the fields with 7 and 8 TAGS based on the combo box selection. So...you select 8 in the comboBox and all fields with TAGs 7 and 8 are enabled.

I have tried r = ShowHideControls(Me, "7" And "8") and this doesn't work. I have tried other variations of this, but the code only allows for one TAG to be specified and passed to the form.

Any help would be greatly appriciated.

isladogs · Jul 25, 2019

Hi
Your post was moderated. Now approved.
I have similar code that allows you to show/hide groups of controls at once including those with different tag values. You can also enable/disable or lock/unlock groups of controls in the same way.
See my example app https://www.access-programmers.co.uk/forums/showthread.php?t=293439

Juett · Jul 25, 2019

Hi,

Thanks for the pointer.

I think my problem is that I am trying to assign more than one value to String2, which is essentially what I want to do.

So where the below works:

Code:

If Me.Parent.Parent.[No: Stations] = "8" Then
    r = ShowHideControls(Me, "8")

What I want is something similar to:

Code:

ElseIf Me.Parent.Parent.[No: Stations] = "8" Then
    r = ShowHideControls(Me, "7" And "8")

I'm looking at what you have done in that database, but I'll be honest, I do not fully understand it in relation to my specific application. I am not an Access / VBA superhero.

arnelgp · Jul 25, 2019

is it possible for you to show us the code.

isladogs · Jul 25, 2019

Juett
You don't need to be a vba superhero to use my code.
I'm also unclear how the code you supplied works as I've never seen it before.

Lines like If Me.Parent.Parent(No: Stations) look very odd to me.
It looks like you are in a sub-subform with a badly named control in the main form called No:Stations

The conditional part of your code should start with If ...not with Else If
Furthermore where does your code do anything with the 'r' value?

However whatever your conditions are, if you have controls with tag 7 and others with tag 8, using my code you show both groups at once using

Code:

ShowControls True, "7","8"

Or to enable both groups at once use

Code:

EnableControls True, ”7","8"

You hide or disable controls by changing True to False.

NOTE you need the quote marks as the tag value is a string not a number

Import the module modControlState from the example app then place code If...Else..End If code similar to that above in the after update event of the combo or textbox. Scrap all your other code as not needed

Juett · Jul 26, 2019

Thanks Colin,

I have put your code into my project, and I do now see what it does...one thing... whenever I click the button to action it, it applies the control to the entire form, so for example:

Code:

   EnableControls False, "7"

Disables everything in the entire form.

arnelgp · Jul 26, 2019

you can also modify your orig code to accept unlimited number of tags:

Code:

Function ShowHideControls(frm As Form, ParamArray varTAGtoUse() As Variant)
    Dim ctl As Control
    Dim intControlType As Integer
    Dim var As Variant
    
    For Each ctl In frm.Controls
        intControlType = ctl.ControlType
        If (intControlType = acComboBox) Or (intControlType = acTextBox) Then
            For Each var In varTAGtoUse
                ctl.Enabled = (InStr(1, ctl.Tag, var & "") > 0)
            Next
        End If
    Next ctl
End Function

r = ShowHideControls(Me, "7", "8")

isladogs · Jul 26, 2019

By design my code will apply to everything with the same tag values.
If there are other controls you don’t want disabled either assign a different tag value to them or no tag at all.
You could specify all controls to be
Enabled on form load to ensure behaviour is what you want

Hope that helps

Sent from my iPhone using Tapatalk

Juett · Jul 26, 2019

isladogs,

I'm happy to report the code does work, and I think I have uncovered the issue. I want the code to be triggered on a sub form only, so I have placed it on the corresponding form, which when opened directly, works fine, but when its nested in the parent from, it applies the control to everything that's open, even fields without tags.

My current form layout is Parent Form - SubForm1

The code needs to be confined to SubForm1 only.

arnelgp · Jul 26, 2019

here is a revision of your original code.
see the AfterUpdate event of the combo.
see Module1.

isladogs · Jul 26, 2019

I haven't looked at arnel's version of this.

With regard to my code, I don't recall that ever being an issue.
You could try this as a workaround
Add a hidden textbox to the form or subform whose controls you want to enable/disable. Set its control source to equal your combo value (or leave it unbound and use the combo after update event to set the text box to the combo value)
Now add the If...Else...End If code with the EnableControls lines to the textbox after update event. It should now work.

If you still have problems or if arnel's modified version of your original code doesn't help, please upload a cut down version of your app for me to look at.
I'm busy for the next few hours but could look at it this evening (UK time) if that helps.

Juett · Jul 26, 2019

isladogs,

I had already started thinking the very same, and placed a hidden textbox in the subform, and also changed the form source in your module from:

Screen.ActiveForm.Controls to:

Form_MYSUBFORMNAME.Form.Controls

And my after update code on the subform is:

If Me.[No: Stations] = "6" Then
EnableControls False, "7", "8"
ElseIf Me.[No: Stations] = "7" Then
EnableControls True, "7"
EnableControls False, "8"
ElseIf Me.[No: Stations] = "8" Then
EnableControls True, "7", "8"
End If

This is now working. Many thanks for all your help.

isladogs · Jul 26, 2019

Excellent. Glad to hear it worked.
Good point about Screen.ActiveForm.Controls line.
I didn't have the code in front of me and had forgotten that line was there! That explains the earlier issue!

Multiple Values using Tag Field Grouping (1 Viewer)

Juett

Registered User.

isladogs

MVP / VIP

Juett

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

isladogs

MVP / VIP

Juett

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

isladogs

MVP / VIP

Juett

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Attachments

isladogs

MVP / VIP

Juett

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread