Results 1 to 3 of 3
2015-03-13, 09:45 #1
- Join Date
- Mar 2015
- 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.
2015-03-13, 11:51 #2
- Join Date
- Mar 2004
- Manning, South Carolina
- Thanked 1,457 Times in 1,326 Posts
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.
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
2015-03-15, 10:45 #3
- Join Date
- Jun 2001
- Crystal Beach, FL, Florida, USA
- 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.