Results 1 to 14 of 14
  1. #1
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12

    Code to Search List Box with an Input Box and have Message Box to continue a new search

    Hello,


    I am stuck on trying to code an input box to search a list box and then once searched, a message box would pop up to ask if you want to continue searching and a new input box would pop up to start another search.

    Thank you!

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I puzzled by the need for a input box, assuming the listbox doesn't have 10000 rows?
    You can type the first letter in a list box and it will go to any matching record that starts with that value.

    Alternatively use a combo with the same row source as the list box and use that to select only valid entries.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12
    Minty,
    The listbox contains thousands of Account Names. I have it set for multi select to then view all contracts associated with all the accounts selected. I want to be able to search the list box for accounts without having to scroll thru the thousands. I have it set up initially, but I need a message box to appear to verify if they want to continue to search for another account and then have the input box pop up to start a new search.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Post your code and please use code tags - (# on posting toolbar) with proper indentation. I have to wonder why not just use whatever user action that triggers the input box to perform another search. People complain when you produce unwanted prompts, especially when they can't turn them off.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay - it is thousands of entries. Are there many similar entries?
    Can I suggest a couple of alternative solution. Rather than type into a pop input box, which (as Micron has suggested) will get really tedious, use a unbound combo that lists the available entries alphabetically.
    There is no point an end user having to guess what a valid entry is, when you have all the available data ready to display to them.
    When the combo value is correct they click a button which adds the selected value to a listbox displaying all the selected entries.

    This has the advantage of them being able to see all the selections they have already made.
    You can make this cleverer by removing the selected entry from the combo so they can't select it again.

    Another option would be to use a filter as you type entry to populate the source listbox.
    e.g. The end user types "Spec" into the search box and the main list box is automatically only displays records containing Spec in the text, they then make the selection, again - I would "move" this into a second listbox displaying the already selected items, prior to doing what ever the next step in the process is. This makes for a nice obvious and intuitive user experience.

    Something like
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    What Minty is refering to is commonly called a "PickList"

    Here's a demo of the one I occasionally use.
    Attached Files Attached Files
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  7. #7
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12
    Thank you Minty and moke123! I can use a combo box to add multiple selections to an empty list box then click search to open up my form containing all those records? That would be much better. I hope I can figure out how to do that. Thanks for the example moke123. I should be able to use that with some tweaks to get what I need done. (fingers crossed)

  8. #8
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12
    ok moke123, I am looking at the example and am a little confused. When I look at the event procedure for double clicking in the box, there is no code. What am I missing?

  9. #9
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12
    I am having a heck of a time with this. My brain is not in it today. Can anyone help guide me? I have my combo box with the rowsource: SELECT [tblAccounts].[ID], [tblAccounts].[Account Name] FROM tblAccounts ORDER BY [Account Name];
    I know I need to have a rowsource for my list box. But not sure do I need to build a query for this? Also what type of function will need to be put in to populate the list box with the combo box selection and will there be able to be multiple selections from the combo box to populate the list box without it replacing the previous selection? And how would i get this to all reset?

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Why use a single select combo to populate a listbox when you can use 2 multiselect listboxes?

    Click image for larger version. 

Name:	2lists.jpg 
Views:	18 
Size:	25.1 KB 
ID:	46374
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12
    Using 2 Multi Select List Boxes would still be tedious. There are over 4,000 account names to scroll thru.

    However, I've figured it out! I got the combo box to populate the list box and then I added a button to open another form to show only those selected in the listbox! Yay!! I now have to crack the code on how to send automated emails based on a record name to specific contacts. I will start a new thread for that!

    Thank you all for your help!

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad you got your solution, although I fail to see how a combobox is any better dealing with thousands of list rows. As noted, a listbox will filter as you type, although I'd rather do that in conjunction with a textbox to provide the filtering. A listbox can be wonky when you start typing extra characters for the filter value.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    kristiszabo is offline Novice
    Windows 10 Office 365
    Join Date
    Oct 2021
    Posts
    12
    Yes, that's the problem with the listbox option. It only filters to the first letter and when there's a ton of names to search thru, scrolling thru the listbox becomes cumbersome. I wanted to make it more user friendly. So the combo box will filter to the exact name as you type more letters. I'm thrilled that I finally got it to work! thanks again for your help!

  14. #14
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    ok moke123, I am looking at the example and am a little confused. When I look at the event procedure for double clicking in the box, there is no code. What am I missing?
    Sorry to leave you hanging all day. Should have mentioned it was a custom class.
    Most of the relevant code is contained in the class module.

    The whole purpose of the class is to make it portable. I can use it in any project with only a couple lines of code, the class does all the heavy lifting.
    Basically you put a listbox on your form with whatever rowsource you need. You then put a second listbox without any rowsource or settings.

    you instantiate the class with
    Code:
        Set clsPL = New clsPickList  ' late binding used just for demo
    
        clsPL.InitMyClass Me.lstSource, Me.lstDestination  'initiate the instance
    The class configures the second listbox and also handles all the events such as double clicking. No need to write any complicated code.
    To get the return values of the selected items you call a function - clsPL.ReturnSelectedColumn

    The function has several arguments such as intColumn for which column to return, what delimiter to use, what to seperate values with, and whether to reset the listboxes.

    Code:
    Public Function ReturnSelectedColumn(Optional intColumn As Integer = 0, Optional delimiter As String = "", Optional separator As String = ",", Optional AutoReset As Boolean = True) As String
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Replies: 12
    Last Post: 02-07-2019, 05:19 AM
  2. Error message for Form Search Code
    By Topflite66 in forum Forms
    Replies: 6
    Last Post: 01-24-2019, 08:22 AM
  3. Replies: 6
    Last Post: 09-22-2015, 03:30 PM
  4. Replies: 3
    Last Post: 01-04-2015, 06:09 PM
  5. Replies: 3
    Last Post: 09-02-2013, 04:33 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