Multiple Forms or Hide Controls?

[MrYoda1]

I have a database related to 3rd - 6th grade students and their end-of-year testing.

There are four possible tests (Math, Language Arts, Science, Social Studies). No grade takes all four tests. They either take two or three out of the four possible and each grade is different, though there is some overlapping. The information for each test is displayed in columns under the testing subject column header.

I want to display, on a Form, only the testing information related to the specific grade. So, if 3rd grade students take a Math and Language Arts test I only want to display the information for those two tests. I'm vacillating between two ways to go about this:

1. Create four separate Forms specific to each grade.
2. Create one Form with all four columns and just hide the column header and controls for those subjects not needed for the grade being displayed.

The problem with the first method is there is more to try to maintain if any changes come along, which does tend to happen due to changes that come from the state Board of Education that are beyond my control (they never seem to be able to make up their mind regarding testing).

If I use the second method I don't particularly like the appearance since it can look like a smile with missing teeth depending on the grade.

My initial feeling was to just create the four separate Forms so I could better control how they look to the user. I'd rather not have them complain about the visual gap between the tests for those grades that would have one.

Does anyone have a suggestion on how I might make this decision? I'm sure there are more than two ways to go about this, so if there is another alternative I'm open to that, as well.

Thank you!

[NeoPa]

Why not use sub-forms within a Tab-Control?

Each sub-form would be on a separate Page (of the Tab-Control) and use the same form with different settings. Only those which are required need be visible. Invisible Pages leave no gap.

[mbizup]

Seconding NeoPa's suggestion for subforms. A common layout when dealing with multiple subforms is to use a tab control on the main form, and place each subform on a separate page of the tab control. With this approach, only the subform on the currently selected tab/page displays at any given time.

[jforbes]

This is probably overkill for what you are wanting to do...

I have a similar situation where we use a "View" (not to be confused with a query or SQL View) that shows the user only specific columns for a SubForm. It also manages which columns are editable. This "View" is selected by the user based on what roll they are performing, so that they only see the information that they are interested in and only edit the columns that they should. It also addresses the "smile with missing teeth" problem by moving the controls to the left and removing any space between the controls.

This "View" functionality is data driven, so the function to hide/show controls is stored in a Module and can be applied to any Form/View combination. Here is the function to apply the Form/View. I doubt you would want to use it as is, but it might give you an idea or two:

    'applyFormView
Public Function applyFormView(ByRef oForm As Form, ByRef sFormViewName As String, ByVal dHeight As Double) As Boolean
    On Error GoTo ErrorOut

    Dim oRst As DAO.Recordset
    Dim sSQL As String
    
    Dim iTabOrder As Long
    Dim iSpacing As Integer
    Dim lLeft As Long
    
    Dim sFormName As String
    Dim sLabel As String

        
    iTabOrder = 1
    iSpacing = 30
    lLeft = 20
    
    sFormName = oForm.Name
    
    ' Determine Control Height
    If dHeight <> 0 Then oForm.Detail.Height = (dHeight * 1440)
    
    ' Get View and reorder the Form
    sSQL = ""
    sSQL = sSQL & "SELECT FormViewControls.* "
    sSQL = sSQL & "FROM FormViewControls "
    sSQL = sSQL & "WHERE FormName='" & sFormName & "' "
    sSQL = sSQL & "AND FormViewName='" & sFormViewName & "' "
    sSQL = sSQL & "ORDER BY DisplayOrder "
    Set oRst = CurrentDB.OpenRecordset(sSQL, dbOpenDynaset, dbForwardOnly + dbSeeChanges)
        If oRst.RecordCount > 0 Then
            While Not oRst.EOF
                If Not oRst!Ignore Then
                
                    sLabel = Nz(oRst!ControlLabelName, "")
    
                    If oRst!Visible <> 0 Then
                        ' Show Control and Label
                        oForm.Controls(oRst!ControlName).Visible = True
                        If Len(sLabel) > 0 Then
                            oForm.Controls(sLabel).Visible = True
                            oForm.Controls(sLabel).Width = oForm.Controls(oRst!ControlName).Width
                        End If
                        
                        ' Move Control into Position
                        oForm.Controls(oRst!ControlName).Left = lLeft
                        If Len(sLabel) > 0 Then oForm.Controls(sLabel).Left = lLeft
                        
                        ' Set Height
                        If dHeight <> 0 Then
                            Select Case oForm.Controls(oRst!ControlName).ControlType
                                Case acComboBox, acTextBox
                                    oForm.Controls(oRst!ControlName).Height = (dHeight * 1440)
                                Case Else
                            End Select
                        End If
                        
                        ' Enable or Disable
                        If oForm.Controls(oRst!ControlName).ControlType <> acCommandButton Then
                            If oRst!Enabled = 0 Then
                                oForm.Controls(oRst!ControlName).Locked = True
                                If Len(sLabel) > 0 Then oForm.Controls(sLabel).BackColor = mColorAccessTheme2
                            Else
                                oForm.Controls(oRst!ControlName).Locked = False
                                If Len(sLabel) > 0 Then oForm.Controls(sLabel).BackColor = mColorRequired
                            End If
                        End If
                                                
                        ' Tab Order
                        oForm.Controls(oRst!ControlName).TabIndex = iTabOrder
                        iTabOrder = iTabOrder + 1
                        
                        lLeft = lLeft + oForm.Controls(oRst!ControlName).Width + iSpacing
                    Else
                        ' Hide Control
                        oForm.Controls(oRst!ControlName).Visible = False
                        If Len(sLabel) > 0 Then oForm.Controls(sLabel).Visible = False
                    End If
                End If
                
                oRst.MoveNext
            Wend
        End If
    
    If dHeight <> 0 Then oForm.Detail.Height = (dHeight * 1440)
    applyFormView = True
    
ExitOut:
    oRst.Close
   Exit Function

ErrorOut:
    ' Removed error handling
    msgbox err.description
End Function