Formatting form controls from a public function?

[jt463]

I have a form that has a couple hundred labels that I need to have some display and some remain invisible based on a category flag on a table.

(I don't know how to avoid this - which is a separate issue.)

Bottom line is I have 18 rooms for each of 26 different plans - all unique combinations. So, based on the plan, only the 18 rooms associated with that plan will display.

My problem is that 468 lines makes the Private Sub procedure too large. So, I am trying to call Public Functions that will make certain controls visible and invisible.

Is there some syntax that will allow me to make a form's control visible/invisible?

I am thinking maybe something similar to:

[Forms]![COFlooring]![Room1A].visible
[Forms]![COFlooring]![Room1B].visible

Because Me.Room1A.Visible doesn't work since it is a Public function.

Thanks in advance.

 

[BoxHead]

We'd have to know more about the flag values, but you should be able to iterate through the controls collection on the fors Current event. You would probably want to use the label's tag property to link to the flag.

Quick example (typed not tested):

The flag value can be the integers 1, 2, 3 or 4. I have a label I want to ahow if the flag value is either 1 or 3. I set the tag property to that label to be 13. I have another label I want to show if te flag value is 1 or 4, I set that label's tag property to 14.

On the form's current event:

[COLOR=green]' Grab the Flag Value[/color]
Dim myFlagVal as String
myFlagVal = WhateverFieldItIs

[COLOR=green]'Iterate through the controls colection and check to see if myFlagVal is present in the label's tag[/color]

For Each ctl in me.Controls
If ctl.ControlType = acLabel and inStr(ctl.tag,[myFlagVal]) > 0 Then
ctl.Visible = true
Else
ctl.Visible = False
End If
Next Ctl

HTH

John



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[MajP]

Boxheads solution is probably the easiest, and probably would have saved you a couple of hundred lines of code.
Another thing to consider, although more complex, is to build your own custom classes and custom collections. Then you could have collections of different controls reacting to their own events.

 

[jt463]

Thanks to both of you.

Perhaps BoxHead's solution will be exactly what I am looking for.

I'll give you a bit more information to see if you still feel that would be the best solution.

Example:
I have house plans - PlanA, PlanB, PlanC, etc...
Each plan has 18 rooms - Room1A, Room2A, Room3A, etc...
So, my form has 26 room labels for Room1 (Room1A, Room1B, etc...).

The flag to which I referred is simply a Category field on a table. PlanA will have a category field of 'A'.

So for PlanA, when the form opens, it will look at the category field and, based on the category letter being 'A', will show all 'A' rooms for that category and suppress all non-A labels. (My Estimator wants our report to show all the rooms as they appear on our plans so as not to confuse anyone in the field.)

Therefore, my code looks something like this:

Form code:

If CatRst!FlooringCategory = "A" Then
    Call CategoryA
    Exit Sub
Else
    GoTo CategoryB:
End If

CategoryB:
If CatRst!FlooringCategory = "B" Then
    Call CategoryB
    GoTo Finish:
Else
    GoTo CategoryC:
End If

Module code:

Public Function CategoryA()
    
[Forms]![COOtherSelectionsFlooringNew]![Room1A].Visible = True
[Forms]![COOtherSelectionsFlooringNew]![Room2A].Visible = True
[Forms]![COOtherSelectionsFlooringNew]![Room3A].Visible = True
[Forms]![COOtherSelectionsFlooringNew]![Room4A].Visible = True
[Forms]![COOtherSelectionsFlooringNew]![Room5A].Visible = True
'Other Rooms
[Forms]![COOtherSelectionsFlooringNew]![Room1B].Visible = False
[Forms]![COOtherSelectionsFlooringNew]![Room1C].Visible = False
[Forms]![COOtherSelectionsFlooringNew]![Room1D].Visible = False
[Forms]![COOtherSelectionsFlooringNew]![Room1E].Visible = False
[Forms]![COOtherSelectionsFlooringNew]![Room1F].Visible = False

I hope that provides a more specific perspective of what I am trying to achieve. Like I said, the suggestion may work. I am just having a tough time wrapping my mind around how specifically that would play out with the specifics of what I am trying to do.

Thanks again for your time in reading this.

 

[MajP]

After all that work you may kill your self. This works for all of your controls, if you tag them with A,B,C etc.

Public Sub subHideUnhide(strTag, blnVisible As Boolean)
  Dim cntrlHide As Access.Control
  For Each cntrlHide In Me.Controls
    If cntrlHide.ControlType = acLabel And cntrlHide.Tag = strTag Then
     cntrlHide.Visible = blnVisible
    End If
  Next cntrlHide
End Sub

Private Sub cmdOne_Click()
  'How to use it 
  Call subHideUnhide("A", False)
  Call subHideUnhide("B", True)
End Sub

 

[BoxHead]

If I understand the schema, then a category "A" plan would mean that all labels with the letter 'A' at the end of their name would be visible and all others would be hidden?

You could still use the Controls collection. I'd suggest setting the tag property for all of these labels to "room" so that Access doesn't even have to evaluate the other labels on the form. This will be faster, too. You can multi-select all of the labels in design view and set the tag property for all of them at once.

The code would look at the tag property to see if it equals 'room' and if it does, the code will tthen look at the last letter in the control name (Right(ctl.Name,1)) to see if it equals the category value.

[COLOR=green]' Grab the Flag Value[/color]
Dim myFlagVal as String
myFlagVal = CatRst!FlooringCategory 

[COLOR=green]'Iterate through the controls collection and check to see if the label's tag = 'room' and if the flagValue is the last letter of the control's name[/color]

For Each ctl in me.Controls
If ctl.ControlType = acLabel and ctl.tag = "room" Then
If Right(ctl.Name,1) = myFlagVal
ctl.Visible = true
Else
ctl.Visible = False
End If
End If
Next Ctl

I do have to ask if it's really necessary to have that many labels. Could you simly create labels with Control Names [Room1], [Room2], ... [Room18] and set the caption property to be the Control Name and the Category:

For Each ctl in Me.Controls
If ctl.Tag = "room" Then
ctl.Caption = ctl.Name & myFlagVal
End If
Next ctl

HTH

John

 

[MajP]

Same idea with a little more functionality. Since you have so many room labels, tag only the non room labels "Other".

Now this will work with your naming convention, and will hide the non selcted categories.

You can also run this the other way using blnVisible, where it hides the selected category and unhides all the other categories.

Public Sub subHideUnhide(strCategory, blnVisible As Boolean)
  Dim cntrlHide As Access.Control
  For Each cntrlHide In Me.Controls
    If cntrlHide.ControlType = acLabel And Not cntrlHide.Tag = "Other" Then
       If Right(cntrlHide.Caption, 1) = strCategory Then
          cntrlHide.Visible = blnVisible
       Else
         cntrlHide.Visible = Not blnVisible
       End If
    End If
  Next cntrlHide
End Sub

 

[jt463]

Wow! Thanks to both of you for your input.

Since I am not experienced with the approaches you guys are suggesting, I will try BoxHead's suggestion first - just because it appears more similar to what I am used to.

To answer your questions about the schema - for the sake of example:

For Plan 'A'
Room1A = Bedroom 2
Room2A = Kitchen
Room3A = Pantry

For Plan 'B'
Room1B = Upstairs Hall
Room2B = Mud Room
Room3B = Hearth Room

So, for a user entering selections for plan 'A', I want the flooring room selection labels to reflect 'Bedroom 2', 'Kitchen', 'Pantry'. And then for plan 'B', the other rooms would display.

I was hoping I could get some sort of a loop as you both suggest. Especially since the module I just created for displaying/hiding the Room labels was over 21,000 lines of code!!! Which is ridiculous, I know!

I will let you know how it goes.

By the way, the:

[Forms]![COOtherSelectionsFlooringNew]![Room1A].[b]Visible[/b]

worked - though it is extremely cumbersome.

 

[MajP]

Boxhead and my idea are fundamentally similar, just different takes on it.
21,000 lines of code. You might not be the smartest, but you sure have Perseverance, Motivation and Determination. I am cutting my lawn with nail clippers. Want to help? Sorry could not resist. Good luck.

 

[jt463]

I appreciate your humorous candor! That is exactly how I feel by doing it the way I am trying to do it.

I will work on the suggestions you both provided and will probably have some questions.

But, again, I greatly appreciate both of you taking the time and for your input.

 

[BoxHead]

jt463,

I think your recent post has added a layer of complexity. But, it may also lead to a simpler and more durable solution.

Do you have a table that includes the room number (1, 2, 3...), the plan letter (A, B, C...), and the room name (laundry room, kitchen, pantry...)?

I ask because with either of the solutions we've posted, anytime you add or modify a floor plan, it will be necessary to modify the design of the form and add more labels.

If you have the data in a table, the caption for each label could be pulled from the table. In fact, it may be possible to use a textbox bound to the table or query.

Could you explain a bit about the use of the form? Is there a different record for each plan or a different record for each homeowner? Are you entering the flooring selections in this form? Where is that data stored? What fields are in the table/query to which the form is bound?


PS: When I first came to tek-tips it was with a Boggle game I had tried making in Access. Once they helped me get the 10K lines of code down to about 400, it worked pretty well.


John

 

[jt463]

You all are so patient - which is greatly appreciated.

Let me attempt to give more clarity of what I am doing.

When a homeowner makes flooring selections for their home, they get the opportunity to make unique selections for each room on their plan (of course many usually stick to one or two flooring types, but we want to give them the option to customize this portion of their home). As such, there is a form on which they can make their selections. On this form they have a column which shows them all the rooms for their home plan (these are the 'Room' labels I am trying to hide/unhide) and then a column of drop-down boxes that allows them to select from a pre-determined list of products we provide.

Once those selections are made, the homeowner can click a 'submit' button which basically takes a 'snapshot' of the values in the drop-downs and updates a 'selections table'. The form itself is not bound to anything. The drop-downs query a 'product selections' table. But other than that, everything is unbound.

I think your idea of having the labels stored in a table and then populating the labels from that table based on which plan it is is BRILLIANT! I just do not know how to do it. I imagine I could try to figure it out, but you see what happens when I try to do that! This actually would let me keep my original form and report which would be so great I can't even imagine how great that would be!

My 21,000+ lines of code actually works, but I am embarrassed, and I want to get better.

Does this description clear up what I am trying to do, or does it just add to the confusion?

 

[jt463]

Since I am against a deadline, I have been able to get my 21,000+ line monster working.

As such, I am still interested in improving on this thing so I appreciate any further assistance you can provide.

 

[BoxHead]

Glad it's working.

I think it would probably be a good idea to take a look at

http://r937.com/relational.html

because the structure of the tables will dictate the possibilities with queries and the options with forms. A lot of what you're dealing with now could be handled in the database design upstream.

When you have some time, start a new thread including some description of your tables and fields. There are plenty of people in this forum who can quickly reduce most of that code to standard, repeatable steps.

HTH


John



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.