Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Question Unanswered: Multiple Mulit-select option boxes to create query?

    I have a database where there are many (100+) records per person, multiple people (10-20) per section, and 7 sections.
    I have a table for each section so that they can store their own people's data individually, and the same form for each table linked to it's specific table.

    I have a main menu form (not tied to any table) with links to each form, and I'm looking to add a create query button. The idea is to have an option box to select one or more sections to pull data from, and another option box to select one or more fields to pull, with a button that will get each of the option box's statuses and build a query based on it.
    I know this is incredibly complex, and I've been trying to figure it out for a few days now. Now, I just want to know if this is possible, and get help in doing so, if it is.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The problem is not so complex.

    1. Handling the selections

    - Each checkbox related to the Section selection stores a mean to identify a Section (Section Name or Section ID) is its Tag property, while its HelpContextID property is set to 1.

    - Each checkbox related to the Column selection stores a mean to identify a Column (Column Name or Column Ordinal Position) is its Tag property, while its HelpContextID property is set to 2.

    - We can use two collections, one storing a reference to every checkbox that's related to the Section selection, the other storing a reference to every checkbox that's related to the Column selection. These collection are initialized when the form is open.

    - A function receives one of the collections as argument and returns a string containing a comma-separated list of the Tag property values of the selected checkboxes in that collection.

    Here's the code, so far:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_colSections As Collection
    Private m_colColumns As Collection
    
    Private Sub Form_Open(Cancel As Integer)
    
        GatherControls
        
    End Sub
    
    Private Sub GatherControls()
    
        Dim ctl As Control
        
        Set m_colSections = New Collection
        Set m_colColumns = New Collection
        For Each ctl In Me.Controls
            Select Case ctl.HelpContextId
                Case 1: m_colSections.Add ctl
                Case 2: m_colColumns.Add ctl
            End Select
        Next ctl
        
    End Sub
    
    Private Function GetSelection(colControls As Collection) As String
    
        Dim ctl As Control
        
        For Each ctl In colControls
            If ctl.value = True Then
                If GetSelection <> "" Then GetSelection = GetSelection & ", "
                GetSelection = GetSelection & ctl.Tag
            End If
        Next ctl
    
    End Function
    2. Creating the queries

    - Creating a query for a section is very strightforward:
    Code:
    Private Function SQLSelect(SectionTableName As String) As String
    
        SQLSelect = "SELECT " & GetSelection(m_colColumns) & " FROM " & SectionTableName
        
    End Function
    - If the database was normalized, things would be very easy. In a normalized database, there would be a single table gathering all Sections, an extra column ("SectionID") identifying the Section of each row. In such a case we would be able to create a single query that would be used as the RecordSource for the form. In this case, the Tag property of a checkbox contains the numeric Section ID of its related section:
    Code:
    Private Function SQLSelectAll() As String
    
        
        SQLSelectAll = "SELECT " & GetSelection(m_colColumns) & " FROM <AllSectionsSingleTable> " & _
                       "WHERE SectionID IN ( " & GetSelection(m_colSections) & " ) " & _
                       "ORDER BY SectionName"
        
    End Function
    - As the database is not normalized and there are several tables (one for each section), we need to use a UNION query to gather the rows from different tables into a single row set. A consequence of this is that the query will be read-only. In this case, the Tag property of a checkbox contains the Name of a Section Table:
    Code:
    Private Function SQLSelectUnion() As String
    
        Dim ctl As Control
        
        For Each ctl In m_colSections
            If ctl.value = True Then
                If SQLSelectUnion <> "" Then SQLSelectUnion = SQLSelectUnion & vbNewLine & "UNION" & vbNewLine
                SQLSelectUnion = SQLSelectUnion & SQLSelect(ctl.Tag)
            End If
        Next ctl
        
    End Function
    3. The Form

    You can create a form with a TextBox control for each possible column. The Tag property of each TextBox contains the name of its related column and these textboxes are also stored into a collection.

    - If the Form is used in DataSheet view, we only need to Hide/Unhide every column (ColumnWidth property of each TextBox) based on the condition that its Tag property is the name of a field in the RecordSet of the form:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_colColumns As Collection
    
    Sub ArrangeControls()
    
        Dim ctl As Control
        Dim fld As DAO.Field
        
        For Each ctl In m_colColumns
            ctl.ControlSource = ""
            For Each fld In Me.Recordset.Fields
                If fld.Name = ctl.Tag Then
                    ctl.ControlSource = fld.Name
                    Exit For
                End If
            Next fld
            If ctl.ControlSource = "" Then 
                ctl.ColumnWidth = 0
            Else
                ctl.ColumnWidth = -1
            End If
        Next ctl
        
    End Sub
    
    Private Sub Form_Load()
    
        ArrangeControls
        
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    
        GatherControls
        
    End Sub
    
    Private Sub GatherControls()
    
        Dim ctl As Control
        
        Set m_colColumns = New Collection
        For Each ctl In Me.Controls
            If ctl.HelpContextId = 2 Then m_colColumns.Add ctl
        Next ctl
        
    End Sub
    - If the form is used in Normal view, things are more complex: we need to Hide/Unhide each Textbox and reposition it according to the position of the previous one. Here's a very simple example where all textboxes are stacked in a single column:
    Code:
    Sub ArrangeControls()
    
        Dim ctl As Control
        Dim fld As DAO.Field
        Dim lngTop As Long
        
        lngTop = 400
        For Each ctl In m_colColumns
            ctl.ControlSource = ""
            For Each fld In Me.Recordset.Fields
                If fld.Name = ctl.Tag Then
                    ctl.ControlSource = fld.Name
                    Exit For
                End If
            Next fld
            If ctl.ControlSource = "" Then
                ctl.Visible = False
            Else
                ctl.Visible = True
                ctl.Top = lngTop
                lngTop = lngTop + 400
            End If
        Next ctl
        
    End Sub
    Have a nice day!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •