Making multiple Label/Checkbox controls visible/invisible

[cardgage]

Good afternoon all,

I am creating a database that will cause a group of checkboxes with labels to become visible/invisible based on whether two criteria are met. Here is the code that I have so far:

Private Sub cmdCheckInclusion_Click()

'--Verify that DOB and Therapy Length have not been changed
   Me.txtDOB.SetFocus
   Me.txtTherapyLength.SetFocus
   Me.txtAdmitDate.SetFocus
   
'--Check if patient meets inclusion criteria; if so, shows checklist for exclusion criteria; if not, checklist remains/becomes hidden
   If Me.iAdult = True And Me.iZosyn48Hrs = True Then
      MsgBox "Patient " & Me.PatientID & " has met inclusion criteria.", vbOKOnly
      fmeExclusion.Visible = True
      lblExclusion.Visible = True
      lblPregnant.Visible = True
      lblBothStrategies.Visible = True
      lblChronicHD.Visible = True
      lblDialysis48Hrs.Visible = True
      lblBaselineSCr2.Visible = True
      chkPregnant.Visible = True
      chkBothStrategies.Visible = True
      chkChronicHD.Visible = True
      chkDialysis48Hrs.Visible = True
      chkBaselineSCr2.Visible = True
    
    ElseIf Me.iAdult = False Or Me.iZosyn48Hrs = False Then
       If Me.iAdult = False Then
          MsgBox "Patient " & Me.PatientID & " has not met inclusion criteria (<18 years old).", vbCritical + vbOKOnly
       
       ElseIf Me.iZosyn48Hrs = False Then
          MsgBox "Patient " & Me.PatientID & " has not met inclusion criteria (<48 hours of Zosyn therapy).", vbCritical + vbOKOnly
       
       ElseIf Me.iAdult = False And Me.iZosyn48Hrs = False Then
          MsgBox "Patient " & Me.PatientID & " has not met inclusion criteria (<18 years old and <48 hours of Zosyn therapy).", vbCritical + vbOKOnly
       
       End If
       
       fmeExclusion.Visible = False
       lblExclusion.Visible = False
       lblPregnant.Visible = False
       lblBothStrategies.Visible = False
       lblChronicHD.Visible = False
       lblDialysis48Hrs.Visible = False
       lblBaselineSCr2.Visible = False
       chkPregnant.Visible = False
       chkBothStrategies.Visible = False
       chkChronicHD.Visible = False
       chkDialysis48Hrs.Visible = False
       chkBaselineSCr2.Visible = False
       
   End If


End Sub

Although this code is functioning properly, it is extremely cumbersome (as you can see). On the form, I have the labels and checkboxes placed within an OptionGroup frame called fmeExclusion. I was wondering...is there any way to flag that all of these checkboxes and labels are associated with fmeExclusion, so that all of them become invisible if fme.Exclusion.Visible is set to False?

Thanks in advance for any help with this!

[June7]

None that I know. Have to run code to set property.

It would be nice if an expression could be used in the Visible property on the Property Sheet, but alas not possible.

[pbaldy]

You can use the Tag property of the desired controls and a loop:

  Dim ctl                As Control

  For Each ctl In Me.Controls 
    If ctl.Tag = "Clear" Then
      ctl = Null
    End If
  Next ctl

[June7]

Ahh, Tag property. I always forget that.

Just be aware that the code will touch every single control on the form unless you also have code specify type of control (textbox, combobox, listbox, checkbox, label, etc).

What you already have might be just as fast and efficient, or more so.

[ssanfu]

If you have two boolean fields and they are ANDed, there are 4 possible results :

iAdult	iZosyn48Hrs	Result
False	False	False
True	False	False
False	True	False
True	True	True

Only of both check boxes are true will the result be true.


So you can use

Private Sub cmdCheckInclusion_Click()
   Dim VisStatus As Boolean

   '--Verify that DOB and Therapy Length have not been changed
   Me.txtDOB.SetFocus
   Me.txtTherapyLength.SetFocus
   Me.txtAdmitDate.SetFocus

   '--Check if patient meets inclusion criteria; if so, shows checklist for exclusion criteria; if not, checklist remains/becomes hidden
   If Me.iAdult = True And Me.iZosyn48Hrs = True Then
      MsgBox "Patient " & Me.PatientID & " has met inclusion criteria.", vbOKOnly
   ElseIf Me.iAdult = False Or Me.iZosyn48Hrs = False Then
      If Me.iAdult = False Then
         MsgBox "Patient " & Me.PatientID & " has not met inclusion criteria (<18 years old).", vbCritical + vbOKOnly
      ElseIf Me.iZosyn48Hrs = False Then
         MsgBox "Patient " & Me.PatientID & " has not met inclusion criteria (<48 hours of Zosyn therapy).", vbCritical + vbOKOnly
      ElseIf Me.iAdult = False And Me.iZosyn48Hrs = False Then
         MsgBox "Patient " & Me.PatientID & " has not met inclusion criteria (<18 years old and <48 hours of Zosyn therapy).", vbCritical + vbOKOnly
      End If
   End If


   'set visible status based on two check boxes
   VisStatus = (Me.iAdult And Me.iZosyn48Hrs)

   fmeExclusion.Visible = VisStatus
   lblExclusion.Visible = VisStatus
   lblPregnant.Visible = VisStatus
   lblBothStrategies.Visible = VisStatus
   lblChronicHD.Visible = VisStatus
   lblDialysis48Hrs.Visible = VisStatus
   lblBaselineSCr2.Visible = VisStatus
   chkPregnant.Visible = VisStatus
   chkBothStrategies.Visible = VisStatus
   chkChronicHD.Visible = VisStatus
   chkDialysis48Hrs.Visible = VisStatus
   chkBaselineSCr2.Visible = VisStatus

End Sub

You could comment out the message box lines....

I'm not sure what the first 3 lines are for.... all they do is move the focus from one control to the next. You might as well just have the last set focus line.

[cardgage]

I'm not sure what the first 3 lines are for.... all they do is move the focus from one control to the next. You might as well just have the last set focus line.

I have some calculations that take place when the Focus is set on those first two text boxes ('Age' based on DOB and AdmitDate, and 'TherapyLength' based on AdmitDate and TherapyStartDate); I do that to make sure that the user didn't change any information after the checklist becomes visible that would cause either to become false - if 'iAdult' and 'iZosyn48Hrs' are not both true, data entry should stop (I have some other If/Then statements that would require clearing out the checklist if this happens).


I appreciate the help...too bad there are no Form controls that would cause everything contained within the Frame to become invisible when the Visible status is changed. I do like the truncated version that ssanfu posted - thanks!

[ssanfu]

the Frame to become invisible when the Visible status is changed

What causes this to happen? Couldn't you move the lines:
fmeExclusion.Visible = VisStatus
lblExclusion.Visible = VisStatus
.
.

to that routine and change "VisStatus" to whatever causes the frame to be not visible?

[cardgage]

I found a solution to this using a Public Function...here's what I have:

Public Function ChangeForm(Visible As Boolean)

'--Set visible status of checklist
   [Forms]![frmInclusionExclusion]![fmeExclusion].Visible = Visible
   [Forms]![frmInclusionExclusion]![lblExclusion].Visible = Visible
   [Forms]![frmInclusionExclusion]![lblPregnant].Visible = Visible
   [Forms]![frmInclusionExclusion]![lblBothStrategies].Visible = Visible
   [Forms]![frmInclusionExclusion]![lblChronicHD].Visible = Visible
   [Forms]![frmInclusionExclusion]![lblDialysis48Hrs].Visible = Visible
   [Forms]![frmInclusionExclusion]![lblBaselineSCr2].Visible = Visible
   [Forms]![frmInclusionExclusion]![chkPregnant].Visible = Visible
   [Forms]![frmInclusionExclusion]![chkBothStrategies].Visible = Visible
   [Forms]![frmInclusionExclusion]![chkChronicHD].Visible = Visible
   [Forms]![frmInclusionExclusion]![chkDialysis48Hrs].Visible = Visible
   [Forms]![frmInclusionExclusion]![chkBaselineSCr2].Visible = Visible
   
'--Update command buttons
   [Forms]![frmInclusionExclusion]![cmdCheckInclusion].Enabled = Not Visible
   [Forms]![frmInclusionExclusion]![cmdDone].Enabled = Visible


End Function

Private Sub cmdCheckInclusion_Click()


   Dim vbAnswer As String


'--Verify that DOB and Therapy Length have not been changed
   Me.txtDOB.SetFocus
   Me.txtTherapyLength.SetFocus
   Me.txtAdmitDate.SetFocus
   
'--Verify that AdmitDate, DOB and TherapyLength are not null; prompt to enter data if so
   If Not IsNull(Me.txtDOB) And Not IsNull(Me.txtTherapyLength) And Not IsNull(Me.txtAdmitDate) Then
   
'--Check if patient meets inclusion criteria
      If Me.iAdult = True And Me.iZosyn48Hrs = True Then
         lblInstructions.ForeColor = RGB(34, 139, 34)
         lblInstructions.Caption = "Patient " & Me.PatientID & " has met inclusion criteria. Please address the exclusion criteria in order. If you check any of the exclusion criteria, stop entering data (DO NOT check more than one). Click 'Done' when finished."
    
      ElseIf Me.iAdult = False Or Me.iZosyn48Hrs = False Then
    
'--Patient does not meet inclusion criteria; prompt to enter new patient or verify data
         If Me.iAdult = False And Me.iZosyn48Hrs = False Then
            vbAnswer = MsgBox("Patient " & Me.PatientID & " has not met inclusion criteria (<18 years old and <48 hours of Zosyn therapy). Add another patient? (Click 'Cancel' to go back and verify the entered data)", vbYesNoCancel)
       
         ElseIf Me.iAdult = False Then
            vbAnswer = MsgBox("Patient " & Me.PatientID & " has not met inclusion criteria (<18 years old). Add another patient? (Click 'Cancel' to go back and verify the entered data)", vbYesNoCancel)
       
         ElseIf Me.iZosyn48Hrs = False Then
            vbAnswer = MsgBox("Patient " & Me.PatientID & " has not met inclusion criteria (<48 hours of Zosyn therapy). Add another patient? (Click 'Cancel' to go back and verify the entered data)", vbYesNoCancel)
       
         End If
      
            If vbAnswer = vbCancel Then
               lblInstructions.ForeColor = RGB(0, 0, 128)
               lblInstructions.Caption = "Please review the entered information, and click 'Done' when finished."
         
            Else
'--Patient is not included
               Me.Included = False
            
               If vbAnswer = vbYes Then
                  DoCmd.GoToRecord , , acNewRec
                  lblInstructions.ForeColor = RGB(0, 0, 128)
                  lblInstructions.Caption = "Click 'Check Info' before clicking 'Done'"
                  ChangeForm (False)
         
               ElseIf vbAnswer = vbNo Then
                  DoCmd.Close acForm, "frmInclusionExclusion"
                  DoCmd.OpenForm "frmOpening"
            
               End If
         
            End If
       
      End If
   
'--Set visible status of exclusion criteria checklist
      If Not IsNull(Me.iAdult) And Not IsNull(Me.iZosyn48Hrs) Then
         ChangeForm (Me.iAdult And Me.iZosyn48Hrs)
      
      End If


   Else
      lblInstructions.ForeColor = vbRed
      lblInstructions.Caption = "Please make sure that data entry is complete. Click 'Check Info' when finished."
   
   End If


End Sub

It still looks a bit like spaghetti code, but not as cumbersome anymore.