Results 1 to 9 of 9
  1. #1
    raffi is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88

    Search as you type, by using 2 textbox's to search 1 column

    Hello,


    i feel like i'v gotten close, but just not quiet there yet, would be much appreciated if any suggestions, or help, could be provided, and thank you in advance.


    i have a customer table with customer field in it,
    i have a query exactly based on the customer table,
    i have a form named form1


    i got 2 textbox's in Form1, named TxtCustomer1 and TxtCustomer2


    now in the query, within the criteria of the customer field, i have the following code indicated below


    Code:
    Like "*" & [forms]![Form1]![TxtCustomer1].[Text] & "*" And Like "*" & [forms]![Form1]![txtCustomer2].[Text] & "*"

    so u see where im going with this, i have placed the query in "form1" as a subform, named Sub1


    and the 2 textboxes "TxtCustomer1 and TxtCustomer2" have an "on change" event, which requery's the Sub1


    ok so now, the 2 textboxes are operational, and are able to filter the "Sub1" subform in "form1"


    but here's the problem, im not being able to filter this 1 column by using 2 textBoxes together, its filtering but separately.
    or in other words, after filtering "Sub1" using TxtCustomer1 , when i switch to TxtCustomer2 and start typing, its restarting the sort in Sub1, and starting over, and vice versa.

    alright, now i'v actually been able to sort one column with 2 textboxes ,BUT, had to make 2 changes, which are indicated below.


    change 1) using "After Update" event, instead of "on change" event
    change 2) using the same exact code which i'v posted above, but without the [Text] Control property, which looks like what is indicated below

    Code:
    Like "*" & [forms]![Form1]![TxtCustomer1] & "*" And Like "*" & [forms]![Form1]![TxtCustomer2] & "*"
    again any help would be much appriciated, thank you in advance

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This filter parameter won't work. How can any record match the criteria of AND operator? A value in the field cannot match both parameters. Perhaps should be OR operator?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    raffi is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88
    hello June7 thanks responding

    well i was thinking, since the 2 TxtBoxes are operational, but however canceling each other out, or in other words, not working together to search one column,
    maybe that one column could be re-query'ed by "TxtCustomer1 & TxtCustomer2 both at once" while typing only in "TxtCustomer2" or only in "TxtCustomer1"

    in other words, i would be re-querying a subform, by 2 textboxes at all times, by typing into 1 of the textbox's at all times.

    but this means i would have to re-apply a filter without change happening in one of the textboxes hmmm .....

    so a function would be responsible for re-querying the subform using the 2 textboxes

    and that function would be run everytime change happen in one of the textboxes

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Doesn't alter what I said about using AND operator.

    I've never used Change event, only AfterUpdate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    sstiebinger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2015
    Posts
    105
    What is the purpose of 2 text boxes for the same field?
    What are you trying to accomplish?

  6. #6
    raffi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88
    well i have a field name called "ItemDescription" it has 3 categories in it,

    1) the name of the item
    2) the Size of the item
    3) the color of the item

    so im using 3 textboxes to filter that one column, or in other words, to narrow down data as i type in each textbox, its working, but only with the after update event
    im trying to achieve the same result but with the "on_change" event, so it would be search as you type kind of filter.

  7. #7
    sstiebinger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2015
    Posts
    105
    so to understand correctly, your item description is a single field with data like "Hoodie Large Gray" ? Wouldn't it make more sense to have 3 fields for that and then filter each field independently?

  8. #8
    raffi is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Location
    Inside a Sub Procedure
    Posts
    88
    it would, but the database which the company is dependent on unfortunately done it that way

  9. #9
    sstiebinger is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2015
    Posts
    105
    Then assuming the text is always in the same order (Name-Size-Color)
    Code:
    LIKE "*" & [txt1] & "*" & [txt2] & "*"
    .
    If the order is arbitrary (Name-Color-Size, Color-Size-Name) then
    Code:
    LIKE "*" & [txt1] & "*" & [txt2] & "*" OR LIKE "*" & [txt2] & "*" & [txt1] & "*"
    And using the afterupdate.

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

Similar Threads

  1. Search (While You Type) in a ComboBox
    By aonsu209 in forum Access
    Replies: 20
    Last Post: 04-27-2021, 12:34 PM
  2. Search TextBox with option group search group.
    By jhallcb in forum Programming
    Replies: 4
    Last Post: 03-23-2015, 07:40 PM
  3. Textbox Search
    By MCCDOM in forum Forms
    Replies: 12
    Last Post: 01-06-2015, 11:01 AM
  4. Search as you type
    By Farnarkle in forum Programming
    Replies: 3
    Last Post: 11-17-2014, 06:03 AM
  5. Search as you type
    By CaptainKen in forum Programming
    Replies: 19
    Last Post: 04-25-2012, 12:55 PM

Posting Permissions

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