Results 1 to 7 of 7

How to bound multiple Search Boxes to each other

  1. #1
    ijaz8883 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    64

    How to bound multiple Search Boxes to each other

    Hi experts!
    I am facing problems


    1. Let me know how to bound these search boxes with each other so that I my filter the result as required.
    There is a problem if I search in one box its ok but whenever, I try to search other box its clear the previously searched result
    Let me know what to do more to bound these boxes so that I my filter multiple data.
    Thank a lot.
    The sample file is attached here
    Experiment Search Ijaz.zip


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,550
    build the 'where' clause by cycling thru all the controls....
    it executes after a find button CLICK event
    if null, ignore.
    if not, apply.
    Code:
    '----------------
    sub btnFilter_click()
    '----------------
    dim sWhere as string 
    sWhere = "1=1"
    if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
    if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
    if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"
    If sWhere = "1=1" Then
      Me.FilterOn = False
    Else
      Me.Filter = sWhere
      Me.FilterOn = True
    End If
    end sub

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,216
    Welcome to the forum.

    There are several problems in your dB.

    1) In table "Surety_Bonds", there is an apostrophe in a field name and a hash sign (#) in a field name.
    Object names should be letters and numbers.
    Do not begin an object name with a number.
    NO spaces, punctuation or special characters (exception is the underscore) in object names


    2) You are using the change event of text boxes. This is the wrong event to use in this case. You should be using the after update event. The change event fires after every keypress. If you enter 5 characters, the change event fires 5 times.

    3) In the query "SearchEN", the criteria is using the TextBox.Text Property. Looking at HELP, it states"
    "TextBox.Text Property
    While the control has the focus, the Text property contains the text data currently in the control; the Value property contains the last saved data for the control. When you move the focus to another control, the control's data is updated, and the Value property is set to this new value. The Text property setting is then unavailable until the control gets the focus again"

    Better would be criteria like [Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null See http://www.theaccessweb.com/queries/qry0001.htm

    Even better would be to build and set a filter. Allen Browne has an example Search criteria
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    ijaz8883 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    64
    Thanks ranman256 but I don't want to use button function as mentioned below ssanfu I have already made that but I want to make multiple search as you type function. Help me if you can do something in this matter that I may use some code to bound these search boxes to each other and the result shown as such I want.
    I have seen a form of a person in which he has bounded two columns but that requires entry first (column1) then (column) otherwise don't show result.
    Thanks


  5. #5
    ijaz8883 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    64
    Quote Originally Posted by ssanfu View Post
    Welcome to the forum.

    There are several problems in your dB.

    1) In table "Surety_Bonds", there is an apostrophe in a field name and a hash sign (#) in a field name.
    Object names should be letters and numbers.
    Do not begin an object name with a number.
    NO spaces, punctuation or special characters (exception is the underscore) in object names


    2) You are using the change event of text boxes. This is the wrong event to use in this case. You should be using the after update event. The change event fires after every keypress. If you enter 5 characters, the change event fires 5 times.

    3) In the query "SearchEN", the criteria is using the TextBox.Text Property. Looking at HELP, it states"
    "TextBox.Text Property
    While the control has the focus, the Text property contains the text data currently in the control; the Value property contains the last saved data for the control. When you move the focus to another control, the control's data is updated, and the Value property is set to this new value. The Text property setting is then unavailable until the control gets the focus again"

    Better would be criteria like [Forms]![myForm]![myControl] OR [Forms]![myForm]![myControl] Is Null See http://www.theaccessweb.com/queries/qry0001.htm

    Even better would be to build and set a filter. Allen Browne has an example Search criteria
    Thanks for your good attention: That's no matter I may remove these above said # and apostrophe(') and number and spaces but after doing this may I found the requisite result as I required.
    I have used already Allen Browne example but I don't want to use the button criteria I just want a "search as you type" type multi search format

    I have already using After Update function but its work after clicking on other box or pressing tab and enter but not search the result as shown in this form
    Hope you will help me to do this task.
    Thanks

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,216
    Let me put it another way:

    On form "SearchForm" you have 6 unbound text boxes and an unbound combo box.
    Lets say you are entering characters in the text box named "txtname" (Surety Name).
    The subform "SearchEN subform" (shouldn't use spaces in object names) has query "SearchEN" as the record source.
    The field "Name_of_Surety" has a criteria of
    Code:
    Like "*" & [forms]![SearchForm]![txtname].[Text] & "*"
    Because you are using the Change event, you get the "search as you type" action (you are referencing the TEXT property of the text box).
    BUT, because each of the other field criteria are also using the TEXT property, the search is only searching on the field (Name_of_Surety) that currently has the focus.

    Since the other unbound text boxes do not have the focus, there is NOTHING to search on.
    Because you reference the TEXT property of a control, you will only be able to search on ONE field - which is based on the control that has the focus!!



    It *might* be possible to create "search as you type" using code, but it would take LOTS of code (maybe a couple hundred lines of code) to do it.
    Sooo much easier to use the after update events and call Allen Browne's code.



    Good luck with your project......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    ijaz8883 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    64
    Quote Originally Posted by ssanfu View Post
    Let me put it another way:

    On form "SearchForm" you have 6 unbound text boxes and an unbound combo box.
    Lets say you are entering characters in the text box named "txtname" (Surety Name).
    The subform "SearchEN subform" (shouldn't use spaces in object names) has query "SearchEN" as the record source.
    The field "Name_of_Surety" has a criteria of
    Code:
    Like "*" & [forms]![SearchForm]![txtname].[Text] & "*"
    Because you are using the Change event, you get the "search as you type" action (you are referencing the TEXT property of the text box).
    BUT, because each of the other field criteria are also using the TEXT property, the search is only searching on the field (Name_of_Surety) that currently has the focus.

    Since the other unbound text boxes do not have the focus, there is NOTHING to search on.
    Because you reference the TEXT property of a control, you will only be able to search on ONE field - which is based on the control that has the focus!!



    It *might* be possible to create "search as you type" using code, but it would take LOTS of code (maybe a couple hundred lines of code) to do it.
    Sooo much easier to use the after update events and call Allen Browne's code.



    Good luck with your project......
    Ok Thanks a lot. I understand that in this matter its not possible to complete the task and the columns could not be focused
    However, Thanks
    Now need some more help.
    Plz check the export button its not working properly
    I want to use this button in proper action but its not working properly.
    Let it solve and also mention if I do not use any query and use allen code then what code should use to export the searched data into excel sheet.
    Means: in current scenario what to do and in case just search on "SearchForm" (without query subform) what to do
    and one thing more I have seen a file and its export the excel file directly on desktop but when I give the location I have to mention the user name otherwise its give error.
    Lets solve plz.
    Look at this file form namely "frmCustomerSearch" Its working fine but its locked
    . The file is below
    Export Function and result on the same page.zip
    Last edited by ijaz8883; 01-12-2019 at 03:37 AM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 05-24-2017, 08:06 AM
  2. Replies: 1
    Last Post: 03-20-2015, 03:19 PM
  3. Replies: 6
    Last Post: 02-26-2014, 05:06 PM
  4. Replies: 6
    Last Post: 01-30-2014, 05:57 PM
  5. Multiple Search Boxes
    By Hamm in forum Queries
    Replies: 6
    Last Post: 12-03-2012, 11:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums