Results 1 to 6 of 6

Thread: Getting values from a VBA Listbox

  1. #1
    Member
    Join Date
    Feb 2009
    Posts
    103
    Thanks
    3
    Thanked
    5 times in 5 posts
    • Leggie's system
      • Motherboard:
      • Asus M4A785TD-V EVO
      • CPU:
      • AMD Athlon X4 620
      • Memory:
      • 2 x 2GiB G.Skill Ripjaws DDR3 PC3-12800C7
      • PSU:
      • QTechnology QT-03400G 400W
      • Operating System:
      • Windows 7 Home Premium x64
      • Monitor(s):
      • Dell 2709W

    Getting values from a VBA Listbox

    Hi guys,

    In an Access 2007 database, I have a form with a ListBox that grabs the values from one of my tables. I want, when I click on a ListBox item, the value I have selected in the list to appear in a TextBox.

    I can't seem to find the right code to do. I've tried many combinations, such as something like the following:

    Code:
    txtS.Value = lstS.ItemData(lstS.ItemsSelected.Item(0))
    I've a feeling it must be easy and I can't see the wood for the trees. Should be a simple query, but I've waded through the VBA API and after working in Java it all seems much more vague and hazy.

    Thanks for your help,

    Leggie

  2. #2
    Senior Member
    Join Date
    May 2009
    Location
    Norfolk
    Posts
    472
    Thanks
    3
    Thanked
    25 times in 25 posts
    • pipTheGeek's system
      • Motherboard:
      • Asus P6T Deluxe
      • CPU:
      • Core i7 920 @ 3.6GHz
      • Memory:
      • 3 * 2Gb Corsair XMS @ DDR3 1800
      • Storage:
      • 300GB 15K SAS + 500Gb
      • Graphics card(s):
      • GTX570
      • PSU:
      • corsair 760i
      • Case:
      • Corsair 550d
      • Operating System:
      • Windows 7
      • Monitor(s):
      • Dell Alienware 23"
      • Internet:
      • VM 50Mb

    Re: Getting values from a VBA Listbox

    Sorry, just tried finding the answer for you. Then I realised/remembered how much I HATE Access 2007. I'm going out instead. I had ended up with something similar to what you had posted, but my event handler appeared to be the wrong one becuase I was getting an error becuase the list had no items selected! If you are still stuck tomorrow then I'll sort the code out to do it in Access 2 instead.

    (Access 2 is the version of Access that I used during the Access developer stage of my programming career.)

  3. #3
    Member
    Join Date
    Feb 2009
    Posts
    103
    Thanks
    3
    Thanked
    5 times in 5 posts
    • Leggie's system
      • Motherboard:
      • Asus M4A785TD-V EVO
      • CPU:
      • AMD Athlon X4 620
      • Memory:
      • 2 x 2GiB G.Skill Ripjaws DDR3 PC3-12800C7
      • PSU:
      • QTechnology QT-03400G 400W
      • Operating System:
      • Windows 7 Home Premium x64
      • Monitor(s):
      • Dell 2709W

    Re: Getting values from a VBA Listbox

    I've had no more luck, I'm afraid, so any help would be gratefully received, thanks.

    I'm also somewhat glad to see that it isn't just me that has trouble with Access!

    Thanks,

    Leggie

  4. #4
    Large Member
    Join Date
    Apr 2004
    Posts
    3,643
    Thanks
    45
    Thanked
    96 times in 61 posts

    Re: Getting values from a VBA Listbox

    I've no idea, but an educated guess would be:

    Code:
    txtS.Value = lstS.ItemsSelected.Item(0).Value
    To err is human. To really foul things up ... you need a computer.

  5. #5
    "make it so" scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Manchester
    Posts
    10,427
    Thanks
    821
    Thanked
    1,357 times in 1,171 posts
    • scaryjim's system
      • Motherboard:
      • Asus M4A785TD-M EVO
      • CPU:
      • Phenom II X4 905e
      • Memory:
      • 2x 4GB Crucial Ballistix Tactical VLP
      • Storage:
      • 750GB Seagate
      • Graphics card(s):
      • Sapphire 7750 Low Profile
      • PSU:
      • FSP 250W TFX
      • Case:
      • AOpen H360b
      • Operating System:
      • Windows 7 Professional x64
      • Monitor(s):
      • Iiyama ProLite E481S

    Re: Getting values from a VBA Listbox

    Good lord the Access 2007 help is appalling, isn't it. I've never been able to find anything in office help since 2002...

    Anyway, the code you are looking for is
    Code:
    txtS.Value = lstS.Value
    the value of the listbox is the value of the bound column in the currently selected row (at the risk of guessing, if you have a multiselect list with more than 1 item selected you probably get a comma-separated list of the selected items). If you have a multi-column listbox and want to use a value other than the bound column (although why you'd do that is beyond me ) you'd need
    Code:
    txtS.Value = lstS.List(lstS.ListIndex, columnindex)
    where columnindex is the zero-based index of the column you're interested in. That example only works for non-multiselect lists though: for multiselects you'd need something like
    Code:
    Dim result as String, i as Integer
    
    For i = 0 to lstS.ListCount - 1
        If lstS.Selected(i) result = result & lstS.List(i, columnindex) & ", "
    Next i
    
    txtS.Value = result
    But that's probably enough showing off from me for tonight

  6. Received thanks from:

    Leggie (03-02-2010)

  7. #6
    Member
    Join Date
    Feb 2009
    Posts
    103
    Thanks
    3
    Thanked
    5 times in 5 posts
    • Leggie's system
      • Motherboard:
      • Asus M4A785TD-V EVO
      • CPU:
      • AMD Athlon X4 620
      • Memory:
      • 2 x 2GiB G.Skill Ripjaws DDR3 PC3-12800C7
      • PSU:
      • QTechnology QT-03400G 400W
      • Operating System:
      • Windows 7 Home Premium x64
      • Monitor(s):
      • Dell 2709W

    Re: Getting values from a VBA Listbox

    Quote Originally Posted by scaryjim View Post
    If you have a multi-column listbox and want to use a value other than the bound column (although why you'd do that is beyond me ) you'd need
    Code:
    txtS.Value = lstS.List(lstS.ListIndex, columnindex)
    where columnindex is the zero-based index of the column you're interested in.

    But that's probably enough showing off from me for tonight
    Hi,

    Thanks for this - I finally managed to work out the problem, and using your code have been able to retrieve the value of the bound column.

    The other issue I have is that I also need to get the value of one of the non-bound columns. The second piece of code you suggest would seem to do that; unfortunately, there doesn't appear to be a List method in ListBox. Were you thinking of another method?

    Thanks again,

    Leggie

    EDIT: It seems that this will do the trick:

    Code:
    lstS.Column(2, lstS.RowSource)
    Thanks again!
    Last edited by Leggie; 03-02-2010 at 09:00 PM.

Thread Information

Users Browsing this Thread

There are currently 8 users browsing this thread. (0 members and 8 guests)

Similar Threads

  1. CS21A550 default deflection values needed.
    By tusharfrompune in forum Help! Quick Relief From Tech Headaches
    Replies: 0
    Last Post: 23-07-2009, 06:19 PM
  2. Any good VBA books?
    By stroberaver in forum Programming and Web Development
    Replies: 8
    Last Post: 21-07-2009, 09:35 AM
  3. Ram CAS latency values help please!
    By mathewdbarlow in forum PC Hardware and Components
    Replies: 2
    Last Post: 26-01-2007, 01:25 PM
  4. British Core Values?
    By BEANFro Elite in forum General Discussion
    Replies: 48
    Last Post: 17-05-2006, 12:35 PM
  5. multi boot options
    By robbiehall in forum Software
    Replies: 4
    Last Post: 27-10-2003, 07:55 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •