Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160

    Search on any word in a drop down list

    Hi guys,

    I am working on a simple billing software. I have all my products listed in tblItem with ItemID_PK as primary key & Item description. On a Sales form under SalesDetails I have a combo box where user can choose between items by typing and in ItemID_FK the value is being stored. Now I was wondering how I can make the search being done on Item description to show results even if the text is some where in between of the complete description. Like for example:

    Item Description
    Polyester semi dull Maharani weft weave

    Now Access only searches the above when polyester is typed and the followed by semi and so on. I want it to search on any word or combination of words, like let us say Maharani OR Maharani Weave OR Maharani Semi.

    Hope I have been able to explain my question.



    Looking forward to your reply.
    Thanks and Regards
    Deepak Gupta

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not sure Allen's code will do what OP wants which is to find 'Maharani semi" within "Polyester semi dull Maharani weft weave". That would require parsing user input and searching each word.
    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.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    June
    Reading the search requirements properly, you may well be correct.
    In fact getting any search to manage disconnected words will probably be unwieldy
    If it works at all, I expect it will be very slow
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Hi Ridders52 & June,

    Thanks for pointing me out to a really cool utility. Thanks, I am sure, I will use it some where.

    But sadly enough, my problem is not solved. Though it did gave me an Idea, can we filter results in a combobox based on another combobox or unbound text box. This has to happen inside a subform (SalesDetailsSubform). This way atleast we will be able to filter the combo box drop down list, matching to single word, which user can input.

    I tried doing it with unbound text box, but as I added the text box to subform, was unable to get focus on the text box. I even set the text box right at the starting and even used tab order to set it to 0 (Right at top of the list but unable to find why it is not getting the focus. Will try again with trying to create both forms from scratch, may be that helps.

    I have your (Ridders52) code of limiting records to Less than 15 on this subform.

    Awaiting your Help and guidance.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That is called cascading or dependent combobox (or listbox) - a common topic.
    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.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Haven't used them myself, but maybe you can get wanted result with unbound multi-select list box (in case there is an option to enter list values into listbox like with single-column combo box). When there is no way to enter list values directly, then remains tho option of prefilled search values table, from where the user selects words used for search. Or the listbox displays all entries from searched field, and user marks entries to be displayed (applicable when the number of rows in table is not too big).

  8. #8
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Hi Guys,

    I am able to get the cascading combo box working when tblSaleDetailSubform is opened as main form. The moment I open the tblSale and tblSaleDetailSubform is opened as subform in the main form it keeps giving me errors, asking for the value which needs to be parsed for the query to find matching results.

    I am attaching the access file for your reference please help. Please do take a note the frmsale is the format in which the form is finally to be used (with all codes working as of now).

    Thanks and Regards
    Deepak Gupta
    Attached Files Attached Files

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Deepak
    I'm answering on my phone and haven't looked at your attachment

    On a similar subject, are you aware that a couple of days ago I posted an example database for your null to zero report issue
    See https://www.accessforums.net/showthr...387#post401387
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Yes I am Aware and trying the same. Will reply on that post today evening.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  12. #12
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Dear Orange,

    Thanks for your reply and guiding me to nice database. But It creates a form or report based on search parameters. Where as I am looking limit the number of records in a combo box based on word in a text box entered in the same record on the fly. Cascading solves my problem to an extent, but unable to make it to work as per my post#8. When the form is opened as main form everything good but when opened as a subform in side a form, it does not work. Please refer to the data base example attached.

    Looking forward to your help.
    Thanks and Regards
    Deepak Gupta

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I wasn't suggesting that the database met your needs. I was suggesting that some of the code/logic my be useful either or directly or as a concept.
    Underlying logic is to find those record identifiers that contain the "words/string(s)" that were being searched for.
    The output could be a form, report or some control --output wouldn't necessarily be a form/report.

    In your set up, please define Product vs Item--an example would help.

    I'm confused with Product description and item description

    There are 2 subforms with very similar names???

  14. #14
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Please refer to tblSale & tblSaleDetail. Leave frmSale & frmSaleDetail. New DB attached for ease.

    As for example
    Product - AP APCO GLS ENML (Product ID - 0001)
    Item - AP APCO GLS ENML FR BLUE 1 LT (item ID - 00010112210)

    Thanks and Regards
    Deepak GuptaCascading Combo Box.zip

  15. #15
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Hi Guys,

    First of all I have been able to filter combo box based on a combo box, in sub form datasheet view also now. In query I just added for the second combo box just added Main Form Name to point the query to the control source.

    But, now another problem is happening, in datasheet view whenever filter object is updated and combo box search query is re-run, the display for the other records for the Item is lost. The value is saved in the table but on screen shows as null. I feel I am unable to explain Please refer to the new attached DB. also Attaching pictures for immediate reference. Please refer how display changes for Item field with change in Product Desc field (product desc field has an after update event to re-query, query on which Item field is based).

    1. When Form Opens
    Click image for larger version. 

Name:	img1.JPG 
Views:	28 
Size:	61.3 KB 
ID:	34537

    2. When 2nd record of product desc field is updated to matching Records, check how all items show up having products of same type.
    Click image for larger version. 

Name:	img2.JPG 
Views:	28 
Size:	52.9 KB 
ID:	34538

    3. When 3rd record of product desc field is changed to new type , all item display goes blank as no matching item is stored in the records.
    Click image for larger version. 

Name:	img3.JPG 
Views:	26 
Size:	45.7 KB 
ID:	34539


    Any Help is most appreciated.
    Thanks and Regards
    Deepak Gupta
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Drop Down Box, Word Recognition
    By Runlynch in forum Macros
    Replies: 1
    Last Post: 01-31-2016, 11:08 AM
  2. Replies: 15
    Last Post: 04-17-2014, 02:21 PM
  3. Replies: 2
    Last Post: 04-17-2013, 02:12 PM
  4. Replies: 3
    Last Post: 03-10-2012, 06:15 PM
  5. Replies: 4
    Last Post: 06-16-2011, 09:30 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