Need help creating a button to hide any unchecked box.

[GoodLife22]

I have a form called "frm_CLIENTS" tied directly to a table with about 40 checkboxes and one ID.

so the first couple fields in the tbl look like:

ID (unique ID for each of my clients)
BB
CC
CPT
DEW
FRE
FZ
HYT


ect for about 40 total of these shorthand codes. My form has all 40 of these checkboxes. So if "WALMART" is my client I may have about 10 of these 40 checked. If "SEARS" is a different client I may have 25 of these checked. If "TARGET" is a client they may only have 2 of these checked.

So what I want is on my form I want to add a button that says "REMOVE UNCHECKED" which will hide/remove any of those 40 fields if they are not checked. Clear the clutter if you would.

So if I open my form and go to the Walmart record and push that button only the 10 checked buttons will stay on the screen. The others will all go away until I leave that form to search for a different record.

I did not know if this should go in the FORMS section or VBA section so sorry if this is not in the correct spot.

THANK YOU TO ANYONE WHO CAN HELP.

 

[DCrake]

What you ned is another field that holds the value of how many check boxes you want to display. When you first open the form you make everyone invisible, then you get the number from the table for the supplier and turn on the first n checkboxes when n is the number found in the table.

 

[GoodLife22]

So there is no way to simply say something like

on button click
look at current form
make any box with a current value of 0 = invisible

I know this is extremely watered down, but it is what I am trying to get to.

 

[pbaldy]

I have reservations about the design, but this type of thing:

  Dim ctl As Control

  For Each ctl In Me.Controls 
    Select Case ctl.ControlType
      Case acCheckBox
        If ctl.Value = 0 Then
          ctl.Visible = False
        Else
          ctl.Visible = True
        End If
    End Select
  Next ctl

You can use Boolean logic and simply have this:

ctl.Visible = ctl.Value

 

[GoodLife22]

OK I will try this. Why would you have reservations about doing this?

 

[missinglinq]

I'd do the same kind of thing, but set all checkboxes to not visible if not ticked (except on new records) then have a button to make all checkboxes visible, temporarily, so that additional checkboxes can be selected.

Private Sub Form_Current()
Dim ctrl As Control

If Not Me.NewRecord Then

For Each ctrl In Me.Controls
 If TypeOf ctrl Is CheckBox Then
  If ctrl.Value = -1 Then
   ctrl.Visible = True
  Else
   ctrl.Visible = False
  End If
 End If
Next

Else
For Each ctrl In Me.Controls
 If TypeOf ctrl Is CheckBox Then
  ctrl.Visible = True
 End If
Next

End If
End Sub

Private Sub btnShowAll_Click()
Dim ctrl As Control

For Each ctrl In Me.Controls
 If TypeOf ctrl Is CheckBox Then
  ctrl.Visible = True
 End If
Next
End Sub

 

[pbaldy]

I don't have reservations about doing this, I have them regarding the design itself. You have 40 fields of which some are used for some customers, others for other customers. In most situations the proper normalized design would have a related table where each option for a given customer was a record. I don't know what your data represents, but that's a general rule. What's going to happen when you get a 41st item? You're going to have to change every form/report/etc to handle it. In a properly normalized database, a user would simply add a 41st record to a table containing the options, and it would flow through the database without the designer having to do anything.

 

[GoodLife22]

THANK YOU both. These are great options and they both worked really well. I was able to get both ways to work. I will see which way will work the best overall.

THANK YOU both for the quick and excellent answers. This is why I love this place.



"Doesn't everyone deserve the good life?" - DMB

 

[GoodLife22]

pbadly,

I see what you are saying. I thought you had reservations about the VB. Instead it is about my DB set up which I can easily see you questioning. I am still trying to learn the best way to set things up. Bob has been helping me as well. Thank you for the explanation.