Hide/Show Form Controls based on Table Info

[jiblankman]

I am trying to make a form that is user friendly and easy to expand. I have the following table:

[Panels]
PanelNo - unique number
CatA - yes/no
CatB - yes/no
CatC - yes/no

PanelNo 1 = CatA CatB
PanelNo 2 = CatA CatC
PanelNo 3 = CatA CatB CatC

I have fields on a form for each of PanelNo, CatA, CatB and CatC. I want to use [Panels] as a lookup table for the Form [TestResults]. For example, I want CatA and CatB to be visible and have a default value if PanelNo 1 is selected from a combobox. I want CatA and CatC to be visible if PanelNo 2 is selected and have CatB hidden. The show/hide should change with each record in the form.

The actual table has 15 Panels and 18 Categories that have some overlap and some unique. I do not want the unused fields to be visible (or editable) to the user.

I have been able to programmatically set this up but it is difficult to add new categories to, which happens once or twice a year. Ideally, I would only need to add a new PanelNo to the table and a field to the form (if needed).

Any ideas would be greatly appreciated. If there is a better way to handle this, please let me know. Thanks.

 

[jiblankman]

Although there were no responses to this question, I did figure out how to make it work. If anyone else is interested, here is the code that I used. I am sure there are other/better ways to do this, but it works nicely and quickly.

I placed the code into the Form_Current and Panel_LostFocus events as follows:

Private Sub Form_Current()

    If CInt(Me.Panel.Value) = 0 Then
        ' do nothing - this is a new record with no panel no yet
    Else
        ShowDrugs CInt(Me.Panel.Value), Me.Name
    End If

End Sub
    


Private Sub Panel_LostFocus()
    
    Dim ctrl As Control
    If CInt(Me.Panel.Value) = 0 Then
        MsgBox ("There is no Panel number 0." & vbCrLf & _
            "Please enter a valid Panel number.")
        Me.Panel.SetFocus
    Else
        ShowDrugs CInt(Me.Panel.Value), Me.Name
    End If

End Sub

In a new module, I added the following code.

Sub ShowDrugs(panelNo As Integer, frm As String)

    On Error GoTo Err_ShowDrugs
    
    Dim strSQL As String
    strSQL = "SELECT * FROM Panels WHERE panels.panelid = " & panelNo & ";"

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Dim flag As Boolean
    Dim ctrlName As String
            
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL)
    
    'MsgBox (rs.RecordCount)
    If rs.EOF And rs.BOF Then
        MsgBox ("There are no records with the panel number " & panelNo)
        Exit Sub
    End If
    
    
    For Each fld In rs.Fields
        If fld.Name = "PanelID" Or fld.Name = "PanelDescription" Then
            'do nothing
        Else
            If frmControlExists("frm_Results_Maintenance", fld.Name) Then
                ctrlName = fld.Name
                If rs.Fields(ctrlName).Value = True Then
                    With Forms(frm).Controls(ctrlName)
                        .Enabled = True
                        .Visible = True
                    End With
                Else
                    With Forms(fmr).Controls(ctrlName)
                        .Enabled = False
                        .Visible = False
                    End With
                End If
            End If
        End If
    Next
    
Exit_ShowDrugs:
    rs.Close
    Set db = Nothing
    Set rs = Nothing
    Exit Sub

Err_ShowDrugs:
    MsgBox Err.Number & Err.Description
    Resume Exit_ShowDrugs
End Sub




Public Function frmControlExists(ByRef frm As String, ctrlName As String) As Boolean

'   This function will return a true value if the name of the field in the panel
'   table matches the name of a control on the form

    Dim ctrl As Control
    frmControlExists = False
    
    For Each ctrl In Forms(frm)
        If StrComp(CStr(ctrl.Name), ctrlName, vbTextCompare) = 0 Then
            frmControlExists = True
            Exit Function
        End If
    Next
End Function

The only issue I have not yet worked out is when changing the panel number results in the next control in the tab order becoming disabled. The focus changes to the first control and you have to tab through the panel number again.