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
dBforums
>
Multiple Mulit-select option boxes to create query?
01-10-12, 03:15
Multiple Mulit-select option boxes to create query?
Linear Mode
