none
search on a form by check boxes RRS feed

  • Question

  • Dear Everyone,

    I am trying to put together a code which let's me to search records on a table by typing a keyword and applying a filter by the help of check boxes.

    The 'JobSearchField' where the keyword can be typed, the source table is StronRMainT and the field that I use for filtering is BrandName. If I don't type anything in the search field, it brings the message that I need to type reference number, but as soon as there is a character in it, it gives me the "data type mismatch in criteria expression" Run-time error '3464' debug.

    I tried to figure out, but I don't have too much luck.

    The code:

    Private Sub ShowJob_Click()

     

    Dim strText As String

    Dim strSearch As String

    Dim Omega As Integer

    Dim Rado As Integer

    Dim Longines As Integer

    Dim Tissot As Integer

     

     

    If IsNull(Me.JobSearchField) Or Me.JobSearchField = "" Then

        MsgBox "Please enter reference number.", vbOKOnly, "Job Number needed"

        Me.JobSearchField.BackColor = vbWhite

        Me.JobSearchField.SetFocus

    Else

     

        If Me.chckomega = True Then

            Omega = 1

        End If

       

        If Me.chckrado = True Then

            Rado = 2

        End If

       

        If Me.chcklongines = True Then

            Longines = 3

        End If

       

        If Me.chckTissot = True Then

            Tissot = 4

        End If

       

        strText = Nz(Me.JobSearchField.Value, "")

        strSearch = "SELECT * FROM StronRMainT WHERE ((([BrandName] = " _

            & Omega & ") Or ([BrandName] = " _

            & Rado & ") Or ([BrandName] = " _

            & Longines & ") Or ([BrandName] = " _

            & Tissot & ")) Or ([JobNumber] like ""*" & strText & "*""))"

        Me.RecordSource = strSearch

     

    End If

     

    End Sub

    Tuesday, May 23, 2017 10:52 PM

Answers

  • Using a fixed number of check boxes is restrictive as it does not allow for any changes in the data, i.e. the addition of one or more further brands.  A multi-select list box, on the other hand, is data driven, so will reflect any changes in the data.  the code to filter the form would then be along these lines:

        Dim varItem As Variant
        Dim strBrandIDList As String
        Dim strCriteria As String
        Dim ctrl As Control

        If Len(Me.JobSearchField & "") > 0 Then
            strCriteria = "JobNumber = " & Me.JobSearchField
        End If

        Set ctrl = Me.lstBrands

        If ctrl.ItemsSelected.Count > 0 Then

            strCriteria = strCriteria & " Or "

            For Each varItem In ctrl.ItemsSelected
                strBrandIDList = strBrandIDList & "," & ctrl.ItemData(varItem)
            Next varItem

            ' remove leading comma
            strBrandIDList = Mid(strBrandIDList, 2)

            strCriteria = strCriteria & "BrandID In(" & strBrandIDList & ")"

        End If

        Me.Filter = strCriteria
        Me.FilterOn = True

    It may be that the foreign key in your table is named BrandName rather than BrandID or similar if you have unwisely used the 'lookup field' wizard* when defining the data type of the column in the table design.

    The multi-select list box would be set up along these lines:

       RowSource:  SELECT BrandID, BrandName  FROM Brands ORDER BY BrandName;
       BoundColumn:    1
       ColumnCount:     2
       ColumnWidths     0

    It is assumed that BrandID and JobNumber are in each case numeric data types.

    The RecordSource property of the form would simply be:

        SELECT * FROM StronRMainT;

    though normally an ORDER BY clause would be added to return the rows in a logical order.

    If you wish a 'Show All' button could be added to the form, with the following code in its Click event procedure:

        Dim n As Integer

        With Me.lstBrands
            For n = 0 To .ListCount - 1
                .Selected(n) = False
            Next n
        End With

       JobSearchField = Null

        Me.FilterOn = False

    This will deselect all current selections in the list box, empty the JobSearchField  control and turn off the filter so that all rows are returned.

    * For reasons why the 'lookup field' wizard should be avoided like the plague see:

        http://www.mvps.org/access/lookupfields.htm

                                           


    Ken Sheridan, Stafford, England

    • Marked as answer by Milan90 Friday, May 26, 2017 4:13 PM
    Wednesday, May 24, 2017 11:26 PM

All replies

  • Hello,

    You need more quotes if BrandName field is text data type. Please try

    strSearch = "SELECT * FROM StronRMainT WHERE ((([BrandName] = """ _
            & Omega & """) Or ([BrandName] = """ _
            & Rado & """) Or ([BrandName] = """ _
            & Longines & """) Or ([BrandName] = """ _
            & Tissot & """)) Or ([JobNumber] like ""*" & strText & "*""))"
    

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 24, 2017 6:32 AM
    Moderator
  • The main question here is what datatype is Brandname and JobNumber?

    I would, also, be more inclined to use an IN clause then a string of ORs.


    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

    Wednesday, May 24, 2017 7:34 PM
  • Using a fixed number of check boxes is restrictive as it does not allow for any changes in the data, i.e. the addition of one or more further brands.  A multi-select list box, on the other hand, is data driven, so will reflect any changes in the data.  the code to filter the form would then be along these lines:

        Dim varItem As Variant
        Dim strBrandIDList As String
        Dim strCriteria As String
        Dim ctrl As Control

        If Len(Me.JobSearchField & "") > 0 Then
            strCriteria = "JobNumber = " & Me.JobSearchField
        End If

        Set ctrl = Me.lstBrands

        If ctrl.ItemsSelected.Count > 0 Then

            strCriteria = strCriteria & " Or "

            For Each varItem In ctrl.ItemsSelected
                strBrandIDList = strBrandIDList & "," & ctrl.ItemData(varItem)
            Next varItem

            ' remove leading comma
            strBrandIDList = Mid(strBrandIDList, 2)

            strCriteria = strCriteria & "BrandID In(" & strBrandIDList & ")"

        End If

        Me.Filter = strCriteria
        Me.FilterOn = True

    It may be that the foreign key in your table is named BrandName rather than BrandID or similar if you have unwisely used the 'lookup field' wizard* when defining the data type of the column in the table design.

    The multi-select list box would be set up along these lines:

       RowSource:  SELECT BrandID, BrandName  FROM Brands ORDER BY BrandName;
       BoundColumn:    1
       ColumnCount:     2
       ColumnWidths     0

    It is assumed that BrandID and JobNumber are in each case numeric data types.

    The RecordSource property of the form would simply be:

        SELECT * FROM StronRMainT;

    though normally an ORDER BY clause would be added to return the rows in a logical order.

    If you wish a 'Show All' button could be added to the form, with the following code in its Click event procedure:

        Dim n As Integer

        With Me.lstBrands
            For n = 0 To .ListCount - 1
                .Selected(n) = False
            Next n
        End With

       JobSearchField = Null

        Me.FilterOn = False

    This will deselect all current selections in the list box, empty the JobSearchField  control and turn off the filter so that all rows are returned.

    * For reasons why the 'lookup field' wizard should be avoided like the plague see:

        http://www.mvps.org/access/lookupfields.htm

                                           


    Ken Sheridan, Stafford, England

    • Marked as answer by Milan90 Friday, May 26, 2017 4:13 PM
    Wednesday, May 24, 2017 11:26 PM
  • Thank you for the suggestion. The field is short text data type, but it gave me syntax error.
    Thursday, May 25, 2017 6:08 AM
  • BrandName is short text, JobNumber is number. Thank for the suggestion.
    Thursday, May 25, 2017 6:09 AM
  • BrandName is short text................
    That contradicts your original post in which the BrandName values equate with the integers 1, 2, 3 and 4.  If the data type is text, then you can delimit each value with literal quotes characters, as has been suggested earlier.  However, using text values as surrogate numeric keys is unusual and inefficient.  Normally a column of long integer type would be used as the foreign key in the referencing table, and an autonumber column would be the primary key of the referenced table.  So the table definitions would, in broad outline, be like this:

    Brands
    ….BrandID  (autonumber PK)
    ….BrandName  (text)

    StronRMainT
    ….StronRMainID  (autonumber PK)
    ….BrandID  (long integer FK)
    ….etc

    The tables would be related on BrandID, with referential integrity enforced.

    In a form based on StronRMainT the foreign key BrandID column's control would be a combo box set up as follows:

    ControlSource:   BrandID

    RowSource:     SELECT BrandID, BrandName FROM Brands ORDER BYBrandName;

    BoundColumn:   1
    ColumnCount:    2
    ColumnWidths:  0cm

    If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.  Note that the numeric key values are arbitrary and never exposed to the user or hard coded in VBA as you have attempted.

    To search for rows in the table on the basis of the job number or brands, without locking the form into an inflexible  design using check boxes, would then be done via an unbound combo box for the job number and an unbound multi select list box as I described earlier.

    You might like to take a look at MultiSelect.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates a number of ways of restricting rows returned, including the use of multi-select list boxes.  The demo outputs the results as a report, but exactly the same methodology  could be used to filter a bound form or restrict its recordset to selected rows.



    Ken Sheridan, Stafford, England

    Thursday, May 25, 2017 10:55 AM
  • As Ken said, you are converting the checkboxes to an integer value and comparing that. If your datatype is shorttext then they won't match up. 

    Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

    Thursday, May 25, 2017 11:26 AM
  • Finally I found where my datas got mixed up. I didn't use lookup field wizard but still managed to get confused. I am very new to this vba coding. 

    But I must admit the idea of using multi-select list box is a way lot better and I can deal with it in the future much easier.

    This one worked. Thank you!

    Friday, May 26, 2017 4:13 PM