Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA ACCESS 2010 Need help with setting a listbox.selected to the right position

    I have an unbound listbox on a form that is based on a table.
    The listbox is based on the records in the table (First Name Last Name) and is ordered alphabetically. The listbox runs a macro that selects the record after update.
    I have a button that allows a user to add a new record and a save button that saves the record and re-queries the list box so the new name is added into the list.
    I want to end with the new record selected in the list box. However the position in the list box will not be the same as the record number. I need help finding a way to identify the position of the new record in the list box so that I can select it using VBA.
    Last edited by just_this_guy_you_know?; 2015-03-13 at 09:49.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JTGYK,

    Welcome to the Lounge as a new poster!

    Here's some test code that will loop through the items in the control and return the position in the list of the matching item. You should be able to adapt it to your needs.
    Code:
    Private Sub Command93_Click()
    
       Dim zFind As String
       Dim zTest As String
       Dim iLstCnt As Integer
       Dim iCntr   As Integer
       
       zFind = InputBox("Enter Syntax Type")  '*** Only for testing you'll pass in the new item value ***
       
       iLstCnt = Me.lboxSyntaxTypes.ListCount   '*** Substitute your listbox name here and below ***
       
      '*** Note The item array runs from 0 to count -1 ! ***
       For iCntr = 0 To iLstCnt - 1
          If zFind = Me.lboxSyntaxTypes.ItemData(iCntr) Then
    '*** You can replace the message box with command to select the item ***
            MsgBox "Item found at position: " & Format(iCntr, "##"), _
                   vbOKOnly + vbInformation, "Location of Selected item"
          End If
       Next iCntr
       
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I'm hoping the table behind the listbox has a PrimaryKey. If so, that should be part of the rowsource of the listbox (make it a hidden column), and the listbox bound column property should be set to that column. After requerying the listbox, just set the listbox = PK of record you just added.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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