Manipulating ListBoxes in MS Access/VBA
-
I'm working with Microsoft Access, using VBA, and I want to be able to click on an item in a ListBox, then use the AfterUpdate() Sub-routine for that ListBox control, and be able to store that value into a String variable. My loop is as follows: ***START*** Private Sub Descriptions_lst_AfterUpdate() Dim iterator As Integer Dim descriptionName As String 'ListBox name is simply Descriptions_lst For iterator = 0 to Descriptions_lst.ListCount - 1 If Descriptions_lst.Selected(iterator) Then descriptionName = Descriptions_lst.Column(0, iterator) End if Next iterator ***END*** The loop is working correctly, however, the 'if' statement is not evaluating to true, so I therefore cannot test to see if the code inside the 'if' statement is even valid in retrieving the String equivalent for the selected item. Any ideas?
-
I'm working with Microsoft Access, using VBA, and I want to be able to click on an item in a ListBox, then use the AfterUpdate() Sub-routine for that ListBox control, and be able to store that value into a String variable. My loop is as follows: ***START*** Private Sub Descriptions_lst_AfterUpdate() Dim iterator As Integer Dim descriptionName As String 'ListBox name is simply Descriptions_lst For iterator = 0 to Descriptions_lst.ListCount - 1 If Descriptions_lst.Selected(iterator) Then descriptionName = Descriptions_lst.Column(0, iterator) End if Next iterator ***END*** The loop is working correctly, however, the 'if' statement is not evaluating to true, so I therefore cannot test to see if the code inside the 'if' statement is even valid in retrieving the String equivalent for the selected item. Any ideas?
I think you want to use the Click event, not AfterUpdate. AfterUpdate only fires after any change in the controls data. Selecting items in the ListBox does not fire the AfterUpdate event.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
I think you want to use the Click event, not AfterUpdate. AfterUpdate only fires after any change in the controls data. Selecting items in the ListBox does not fire the AfterUpdate event.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Its weird though, If I put Message Boxes throughout the code, everytime I click, it actually does fire the AfterUpdate() event. For example, I placed a MsgBox inside the loop but outside the if statement to check to see if the loop is working: MsgBox "loop works". Then I placed a MsgBox inside the if statement: MsgBox "if works" Then I placed a MsgBox inside the event, but after the loop executes: MsgBox "post loop works" I get "loop works" and "post loop works" to come up, but not "if works". So its having troubles verifying my selected item. There are two Selected properties (one in Access, and one in MSForms). The default one when I write my code seems to come up with the Access Selected version. The thing I think is the issue here is, this returns a Long value...you would think Boolean. However, the MSForms version of Selected under a ListBox returns a Boolean...I just can't figure out how to call that version of it. It seems to always call the Access version. With this extra information, do you have any ideas? Thanks again Dave.
-
Its weird though, If I put Message Boxes throughout the code, everytime I click, it actually does fire the AfterUpdate() event. For example, I placed a MsgBox inside the loop but outside the if statement to check to see if the loop is working: MsgBox "loop works". Then I placed a MsgBox inside the if statement: MsgBox "if works" Then I placed a MsgBox inside the event, but after the loop executes: MsgBox "post loop works" I get "loop works" and "post loop works" to come up, but not "if works". So its having troubles verifying my selected item. There are two Selected properties (one in Access, and one in MSForms). The default one when I write my code seems to come up with the Access Selected version. The thing I think is the issue here is, this returns a Long value...you would think Boolean. However, the MSForms version of Selected under a ListBox returns a Boolean...I just can't figure out how to call that version of it. It seems to always call the Access version. With this extra information, do you have any ideas? Thanks again Dave.
If it's an Access Form ListBox, the it will call the Access version. Period. You cannot change that. According to the docs, ListBox.Selected(index) takes a Long (32-bit signed integer) as an arugment and returns a Boolean if that item is selected or not. Does the selection highlight disappear after your AfterUpdate code runs?? If it does, then AfterUpdate fires AFTER the data is changed and the selections are removed. In which case, yes, all of your "is selected?" compares will return false.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
If it's an Access Form ListBox, the it will call the Access version. Period. You cannot change that. According to the docs, ListBox.Selected(index) takes a Long (32-bit signed integer) as an arugment and returns a Boolean if that item is selected or not. Does the selection highlight disappear after your AfterUpdate code runs?? If it does, then AfterUpdate fires AFTER the data is changed and the selections are removed. In which case, yes, all of your "is selected?" compares will return false.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007No, it stays highlighted...I even tried changing it to a Click Event Routine. Didn't help. My if statement is: If ListBox.Selected(iterator) Then ' iterator is defined as a Long, but I have also tried Integer End If Nothing inside the if block is executing...which makes me think there is something wrong with the ListBox.Selected(iterator). This just seems too bizarre.
-
If it's an Access Form ListBox, the it will call the Access version. Period. You cannot change that. According to the docs, ListBox.Selected(index) takes a Long (32-bit signed integer) as an arugment and returns a Boolean if that item is selected or not. Does the selection highlight disappear after your AfterUpdate code runs?? If it does, then AfterUpdate fires AFTER the data is changed and the selections are removed. In which case, yes, all of your "is selected?" compares will return false.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Wait, could it be because my original ListBox's Row Source Type is Table/Query rather than Value List? Right now I'm using an Access Query to populate the list...would it make a difference if I were to set up the SQL query in the VB Editor and populate it as a Value List?
-
Wait, could it be because my original ListBox's Row Source Type is Table/Query rather than Value List? Right now I'm using an Access Query to populate the list...would it make a difference if I were to set up the SQL query in the VB Editor and populate it as a Value List?
OK. I knew I gave up on Access development for a reason... actually, LOTS of reasons... If the ListBox's MultiSelect property is "None", you can get the selected item index number from the ListBox.ListIndex property. If the ListBox's MultiSelect property is NOT "None", you can get the selected items index numbers from the ListBox.ItemsSelected collection.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
OK. I knew I gave up on Access development for a reason... actually, LOTS of reasons... If the ListBox's MultiSelect property is "None", you can get the selected item index number from the ListBox.ListIndex property. If the ListBox's MultiSelect property is NOT "None", you can get the selected items index numbers from the ListBox.ItemsSelected collection.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007