Cycle through controls in form

[twgonder]

Newbie (again) to Access VBA and I am working on my template for forms.
Since I don't want to repeat code in each class module, I try to move as much common code to a general purpose "form module".
This often causes me headaches, not knowing exactly/properly what to pass to the generalized form module so that things can work.

In any case, what I'm trying to do now is call a form module procedure (from the class procedure) to cycle through all the controls on the form and change the background color of certain text boxes to alternating colors (yellow not available for change and green for okay). Any control that I want to change has a prefix of "tbxm_" (text box modifiable). I've got the color code part figured out. I just need the loop through controls portion and what to pass from the form class procedure to get the loop part to work for the active form.

Can someone please provide a brief code example of the proper way to do this?
Thanks

 

[jdraw]

See https://www.access-programmers.co.u...gh-controls-ms-access-365.323211/post-1826519 for some insight. There are other posts in that thread also.

 

[moke123]

Any control that I want to change has a prefix of "tbxm_" (text box modifiable).

Personally I'd use the tag property. Much easier to select all the controls and add a tag, instead of changing the name and parsing out the prefix.

I'm a little confused by your terminology and/or description of the modules.

I'd assume your code would look something like this:

Public Sub ChangeColor(frm As Form)


    Dim ctl As Control


    For Each ctl In frm.Controls
        If ctl.Tag = "TBM" Then
            ctl.BackColor = vbBlue
        End If
    Next


End Sub

The calling code:

ChangeColor Me

 

[moke123]

Just to add, if you want to use the tag property for more than one thing you can use InStr().

If InStr(1, ctl.Tag, "TBM") Then . . .

If InStr(1, ctl.Tag, "XYZ") Then . . .

 

[Uncle Gizmo]

You might find my blog here of interest:-

Loop Through a Set of Controls - Nifty Access

Loop Through a Set of Controls A common requirement is to check a set of controls for a particular value or condition. Here, I demonstrate two methods. The technique employed is what I call the "For Each Control in Me Controls" - it's a technique where you interrogate each control "in/on" the...

www.niftyaccess.com

In particular:-

Private Function fSomeMT()

 

[twgonder]

Thanks for the pointers, I'll check them out. As to the tag thing, it was being used for another purpose. So, I've been very careful to give the table box names a special format (used for other purposes like help and translation in multi-language). Hopefully it will work here for this purpose.

I found the Strip command, but I'm looking for the same command that will "extract" a subfield. Example: a ="a]b123]c]d"; I want to extract b123. How?

As to "I'm a little confused by your terminology and/or description of the modules.", I'm still confused by the M$ terminology, so that's no surprise. But I'm learning.

 

[jdraw]

Will b always be between ]b]? If not we need to see where "b" resides.

 

[CJ_London]

Value2=split(a,”]”)(1)

note the split function does not work in sql

 

[twgonder]

Will b always be between ]b]? If not we need to see where "b" resides.

I added 123 to b to clarify I want the whole field between the "]" characters. My old BASIC had extract command for this purpose, but I'll be damned if I can find it in the VBA documentation (which I don't often have access to offline).

 

[MajP]

May want to look at the Mid, Right, Left, and Instr functions.

 

[jdraw]

Try CJ's suggestion.

Please show us your STRIP command.

 

[MajP]

Public Sub ChangeControls(frm as access.form)
  dim ctrl as access.control
  for each ctrl in frm.controls
     if left(ctl.name,4) = "tbxm" then
       'do something
     end if
  next ctrl
end sub

 

[moke123]

As to the tag thing, it was being used for another purpose.

Thats why I added

Just to add, if you want to use the tag property for more than one thing you can use InStr().

If InStr(1, ctl.Tag, "TBM") Then . . .

If InStr(1, ctl.Tag, "XYZ") Then . . .

you can have a tag property that looks like

ABC  XYZ   4|7J55

and use InStr() to do different things. The tag property can hold 2048 characters so you can fit a lot of options

 

[Uncle Gizmo]

As to the tag thing, it was being used for another purpose

An alternative to using the Tag property is to place your controls within a container/box as described in my blog here.

Nifty Container - Nifty Access

Nifty Container The "Nifty Container" offers you a graphical way of managing the controls on your form. All you do is surround a group of controls with a box, choose one of the coding examples, or write your own, and there you go, you have a simple and easy-to-use method, which avoids using your...

www.niftyaccess.com

If you want a free copy of the example then drop me a line in this forums private messaging system and I will send you a coupon code to get a free copy....

 

[twgonder]

...

Public Sub ChangeColor(frm As Form)
    Dim ctl As Control
    For Each ctl In frm.Controls
        If ctl.Tag = "TBM" Then
            ctl.BackColor = vbBlue
        End If
    Next

End Sub

The calling code:

ChangeColor Me

Thank you, it seems simple, but I've had trouble finding good examples. It did the trick!

 

[twgonder]

An alternative to using the Tag property is to place your controls within a container/box as described in my blog here.
...

That is indeed a "nifty" little trick. I got it to work on the items I wanted, they have special names for this. But, I have another place it may be useful, I'll keep it in mind. Thanks.

 

[twgonder]

Value2=split(a,”]”)(1)

note the split function does not work in sql

Thanks. That was too easy. I missed that option in the documentation I read. I thought I was going to have to use an array and then pull it back out to individual variables. Much better your solution!

 

[twgonder]

Here is what finally worked for me (thanks to the comments), in the local class code for the form.
I still need to move it, with a lot of other stuff to a generalized "form module" that the individual form can call through its class procedure.
Without using global variables for lots of little arrays (like CmbCol() below), it becomes a bit of a chore that's probably going to require some kind of routines to move "control data" between the different modules.
I'm no longer limited to control prefixes of 5 characters. Thank you!

Public Sub ChngTbxmBackColor(frm As Form, Cpos As Byte)
  'changes all modifiable text and combo boxes to a specified background color
  Dim ctl As Control, PfxCtrl As String
  For Each ctl In frm.Controls
    'PfxCtrl = Left(ctl.Name, 5)
    PfxCtrl = Split(ctl.Name, "_")(0) ' get the first "_" field of the control name
    If PfxCtrl = "tbxm" Or PfxCtrl = "cbxm" Then
      ctl.BackColor = CmbCol(Cpos)
    End If
  Next
End Sub

 

[gemma-the-husky]

^^^
when you are comfortable with this, there's lots you can do.

Note that you can just call your routine in the form's open event. (or maybe the load event)
I can't think having cpos as a byte makes sense. Doesn't it need to be a long?

setcontrols me, newcolour


note that each control has a controltype as well so you can, say ignore check boxes, buttons and so which don't have a backcolor.

TextBox.ControlType property (Access) | Microsoft Docs
AcControlType enumeration (Access) | Microsoft Docs

you can use this sort of thing to highlight the active control in a form, using the enter/exit or maybe the gotfocus/lostfocus pair of controls. Not so useful on a continuous form, unfortunately.