Here's a solution I use quite often.
1. Create an independant class module named Cls_Mask with this code:
Code:
Option Compare Database
Option Explicit
Private m_colMask As Collection
Private m_frmMask As Form
Private m_strSelect As String
Private m_strOrderBy As String
Private m_strLinkOperator As String
Private Function GatherMaskControls() As Long
Dim ctl As Control
Set m_colMask = New Collection
For Each ctl In m_frmMask.Controls
If InStr(ctl.Name, "_Mask_") > 0 Then m_colMask.Add ctl
Next ctl
GatherMaskControls = m_colMask.Count
End Function
Public Function Hook(LinkedForm As Form)
Set m_frmMask = LinkedForm
GatherMaskControls
ResetMask
End Function
Public Property Get LinkOperator() As String
LinkOperator = m_strLinkOperator
End Property
Public Property Let LinkOperator(Operator As String)
m_strLinkOperator = Operator
End Property
Public Property Get OrderBy() As String
OrderBy = m_strOrderBy
End Property
Public Property Let OrderBy(OrderBy As String)
m_strOrderBy = OrderBy
End Property
Public Function ParseMaskControl(ctl As Control) As String
If Len(Nz(ctl.Value, "")) > 0 Then
Select Case ctl.HelpContextId
Case dbBoolean: ctl.Tag = Mid(ctl.Name, InStr(ctl.Name, "_Mask_") + 6) & " = " & CBool(ctl.Value)
Case dbDate: ctl.Tag = Mid(ctl.Name, 11) & " = #" & Format(CDate(ctl.Value), "mm/dd/yyyy") & "#"
Case dbNumeric: ctl.Tag = Mid(ctl.Name, 11) & " = " & Val(ctl.Value)
Case dbText: ctl.Tag = Mid(ctl.Name, 11) & " Like '" & CStr(ctl.Value) & "'"
Case Else: ' TO DO: Handle case (usually a missing or mistyped data type value).
End Select
Else
ctl.Tag = ""
End If
ParseMaskControl = ctl.Tag
End Function
Public Function ResetMask()
Dim ctl As Control
For Each ctl In m_colMask
ctl.Value = Null
ctl.Tag = ""
Next ctl
End Function
Public Property Get Selection() As String
Selection = m_strSelect
End Property
Public Property Let Selection(SQLSelect As String)
m_strSelect = SQLSelect
End Property
Public Function SQL() As String
Dim strSQL As String
Dim ctl As Control
For Each ctl In m_colMask
If Len(ctl.Tag) > 0 Then
If Len(strSQL) > 0 Then strSQL = strSQL & m_strLinkOperator
strSQL = strSQL & "(" & ctl.Tag & ")"
End If
Next ctl
If Len(strSQL) > 0 Then strSQL = " WHERE " & strSQL
SQL = m_strSelect & strSQL & m_strOrderBy
End Function
2. In the form, each control used for creating the filter has a name such as: 'Text_Mask_FieldName' where 'FieldName' is the name of the related field or column. The important feature is that this name must be immediately preceded by '_Mask_'.
3. For each control used for creating the filter, we use its HelpContextID property (which is almost never used otherwise) to store the data type of the related field or column, like this:
Code:
dbBoolean = 1
dbDate = 8
dbText = 10
dbNumeric = 19
You can modify the ParseMaskControl() function of the class Cls_Mask if you need to handle more specific data type (e.g. dbLong, dbCurrency instead of dbNumeric) but this division is usually accurate enough.
4. The tag property of each control used for creating the filter must be left empty: it will be used by the class Cls_Mask to store the formated data of each control value.
5. The AfterUpdate property of each control used for creating the filter is set to '=UpdateMask()'.
6. In my example, the form has 2 command buttons. The first 'Command_Filter' is used to get the SQL query defined by the mask controls, the second resets (empties) all mask controls.
Here's the code used into the Form class module. You'll have to change the initialization of the Selection, LinkOperator and OrderBy properties of the class Cls_Mask to match your needs.
Code:
Option Compare Database
Option Explicit
Private m_clsMask As Cls_Mask
Private Sub Command_Filter_Click()
MsgBox m_clsMask.SQL
End Sub
Private Sub Command_Reset_Click()
m_clsMask.ResetMask
End Sub
Private Sub Form_Load()
Set m_clsMask = New Cls_Mask
With m_clsMask
.Hook Me
.Selection = "SELECT * FROM Tbl_LCF_Data"
.LinkOperator = " AND "
.OrderBy = " ORDER BY ART_ID;"
End With
End Sub
Private Function UpdateMask()
m_clsMask.ParseMaskControl Me.ActiveControl
End Function
One of the advantages of this technique is that the class Cls_Mask is re-usable with any form.
dBforums
>
Using an entry form and SQL for generating qry
04-29-11, 09:16
Linear Mode
