Results 1 to 6 of 6
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Listbox (lstSearch.ItemsSelected.Count) results in VBA error


    Experts:

    I need some assisting with tweaking an existing *WORKING* keyword search form. I will describe the existing process and change requirements in bullet format below.

    To better follow along w/ the process, I recommend viewing the attached JPG which illustrates the current/envisioned functionality.

    Process:
    - Upon opening the DB, open form "frm_KeywordSearch".
    - In the top left of the form, I have a drop-down menu (1) as well as an unbound textbox (2).

    Process when selecting the combo/drop-down box:
    - Once you select a value from the combo, the unbound orange textbox (3) in the top right will show that value. This orange textbox (normally set to invisible) will function as the search criteria for the query.
    - Based on this query search criteria, the large listbox (0) -- showing "Job Title" and "Category" -- will be populated.

    Process when using the unbound textbox (underneath the combo):
    - I can type in any keywords of either the job title (e.g., "analyst") or category (e.g., "grizzly bear").
    - Just like with the combo, the keyword search value is passed into the orange textbox and update the query (thus update the large listbox).

    ... the above works great and does NOT require any modification.

    Now, here's what's new and where I need some help with:
    - I added another listbox (4) that currently shows 2 fields, where the 1st record reads: "Business Analyst" | "Business Analyst (7)" etc.
    - I copied the VBA code from function cboSearch_Change() and replaced the references for "cboSearch" with "lstSearch".
    - Now, the data is bound to the 1st column (containing only the job title).
    - Just like the combo (1) or text box (2), I want to be able to select a value (e.g., "Business Analyst (7)") so that "Business Analyst" is being passed into the orange textbox (3).
    - Once that is working, I more than likely will replace the combo with the new listbox.

    Current issue:
    - When selecting any value from the new listbox, I get a VBA error highlighting the line "vSearchString = lstSearch.ItemsSelected.Count".
    - I also tried "lstSearch.ItemsSelected" but I get the same error.

    My question:
    - How do I need to modify the sub lstSearch_Click() so that I can use the new listbox and pass the selected value into the orange textbox/query criteria?

    Thank you,
    Tom
    Attached Thumbnails Attached Thumbnails Process.jpg  
    Attached Files Attached Files

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I don't understand why you would refer to the Count() of selected items?

    The value of the selected item in the list box if it's the bound column is simply Me.lstSearch exactly the same as a combo.
    Column 2 would Me.
    lstSearch.Column.(1)

    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
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Minty:

    Well, that's probably then the underlying issue. Replacing the reference with the below value, however, results in the line marked as "red" (compile error).

    vSearchString = Me.lstSearch.Column.(1)

    I'm sure I missed something in my synatx. Right now, it appears it does like the "(1)". What am I missing?
    Attached Thumbnails Attached Thumbnails Screenshot 2022-01-12 095912.jpg  

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Minty -- never mind my last post.

    Correct syntax (and column reference) is as follows: vSearchString = Me.lstSearch.Column(0)

    It works great now!!

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Oops typing of the top of my head, added a period that shouldn't have been there.

    You don't need to reference .Column(x) if x is set as the bound column.
    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
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Thank you, Minty... appreciate the help.

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

Similar Threads

  1. Writing listbox results to table
    By PinkLady50 in forum Forms
    Replies: 4
    Last Post: 05-11-2017, 03:34 PM
  2. Replies: 2
    Last Post: 04-15-2017, 11:09 AM
  3. Cannot Get ListBox .ItemsSelected, help!
    By johnywhy in forum Forms
    Replies: 4
    Last Post: 09-03-2013, 09:33 AM
  4. Display RESULTS of Query into LISTBOX?
    By taimysho0 in forum Programming
    Replies: 6
    Last Post: 11-22-2011, 10:05 PM
  5. Replies: 8
    Last Post: 05-24-2011, 03:41 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