Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Unanswered: Using an entry form and SQL for generating qry

    I am trying to use an entry form to search for data, then output to a report. I have it working somewhat, but I need some help.

    I have several fields, and the goal is to be able to fill in just the fields I want to filter the results by. For example:

    Cx: Test1
    Div:
    Acct:
    Street: (Here I want to be able to search for wildcards and pull back 'like' results) I can get this to work by itself, but not as part of the code I have below
    City: Dayton
    State:
    Zip:
    Hub:

    So if I do this search with the code below, I get back all test records unter Test1, and only 'Dayton' records under Test2 Cx. Not filtered correct for sure. What am I doing wrong? The form is unbound, and the fields are linked to a qry. This qry has the following code:

    SELECT Maintbl.Customer, Maintbl.Division, Maintbl.CLLI, Maintbl.Account, Maintbl.Street1, Maintbl.Street2, Maintbl.City, Maintbl.State, Maintbl.Zip, Maintbl.hubsite, Maintbl.CircuitID, Maintbl.VLAN, Maintbl.Description, Maintbl.Email1, Maintbl.Email2, Maintbl.Email3
    FROM Maintbl
    WHERE (((Maintbl.Customer)=[Forms]![entry]![Cx])) OR (((Maintbl.Division)=[Forms]![entry]![Div])) OR (((Maintbl.Account)=[Forms]![entry]![Acct])) OR (((Maintbl.City)=[Forms]![entry]![City])) OR (((Maintbl.State)=[Forms]![entry]![State])) OR (((Maintbl.Zip)=[Forms]![entry]![Zip])) OR (((Maintbl.hubsite)=[Forms]![entry]![Hub]));

    What I really wanted to get to work was to be able to connect the entry fields on the form as a list box to the master table. then select (or multi select) different options. This didn't work at all for me, so I have free form entry instead.

    Thanks for your help!!

    Dave

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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.
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    Wow, this is quite a reply!! Thank you very much. I have tried to put the code in and play with it, and didn't have any luck. I will keep playing with it. I think I am putting things in the wrong places... as I am decent with Access, however definitely NOT an expert! ;-)

    • I created the module with your code (didn't change any text of it)
    • I created a new text field on the form
    • I changed the 'name' of the entry box to 'Text_Mask_Cx'
    • Wasn't sure what you were referring to in items 3 and 4
    • Changed the afterupdate to =UpdateMask()
    • Added a button and pasted your code in the builder for the onclick function
    • I changed the code selection = Maintbl and Order by = Cx (a field name)


    I hit the button, nothing happened. I tried to run the report, and found the report I am running is still plugged into my qry with the previously mentioned sql. Do I need to have the report pull from a different qry? I am not sure how this works exactly as written (pardon my ignorance!). How is it that you are telling the report what to show?

    Thanks!!!!
    David

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Thank you for the feeback.

    1. Be aware that the first segment of code in my answer must be located in a class module and that the name of this class module must be Cls_Mask.

    2. Once the textbox 'Text_Mask_Cx' is created select it, open the Properties window, select the 'Other' tab into it and on the line for the 'Help Context Id' property type a numeric value that can be 1 if 'Text_Mask_Cx' will be used to input a value for searching into a Boolean field or column, 8 for a Date/Time column, 10 for a Text column or 19 for a Numeric column.

    3. Simply check that the Tag property of the textbox (also in the 'Other' tab of the properties window) is empty.

    4. The 'Selection' property of the class must be a SQL SELECT Statement. If you want to retrieve all columns from a table named 'Maintbl' then the line that assign a value to the property 'Selection' must be:
    Code:
    .Selection = "SELECT * FROM MainTbl"
    Also, if you want the resulting data set sorted using the Cx column, then the line that assign a value to the property 'OrderBy' must be (mind the leading space):
    Code:
    .OrderBy = " ORDER BY Cx;"
    5. When you click on the Select command button the 'SQL' method of the class Cls_Mask returns a SQL statement corresponding to the SQL string of a query. You must then use this string to actually perform something.
    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
  •