I often build forms with a combo box to select an item (by item I mean whatever the form's about- a project, a person, a product...) and subforms that show the related record(s). In a form I did recently I wanted to be able to browse from product to product. So I decided to add "Next" and "Previous" buttons.
It turns out that moving to the next or previous item in a combo box or list box is as simple as adding one to, or subtracting one from, the ListIndex of the combo box or list box.There are only a few things to watch out for:
- you have to SetFocus to the list box or combo box before you change it's ListIndex
- if you try to move past the end or beginning of the list you'll get an error
- when you want to check whether or not you're at the end of the list, remember that the ListIndex is zero-based; this means you're at the end when the ListIndex is equal to one less than the ListCount (the ListCount is the number of items in the list
The code below makes the Next and Previous buttons work. The basic code is pretty simple. There are a few niceties added, such as moving to the first item if you click the button and nothing is selected.
Then there's some fancy stuff I did for fun. The buttons have their tool tip changed to the list text for the item they'll move to- it's a kind of preview. To do that I had to write a routine to find the text for the next (or previous) item- it includes as many of the columns from the list box as are shown in the list. It does that by reading through the column widths to figure out which are not zero.
One more thing I found- when you change the ListIndex the control's AfterUpdate will fire. This works out fine, I think, it's just different than what would have happened if you had assigned a value to the control
Here's the code- give it a try!
Private Sub cmbProduct_AfterUpdate() SetToolTips ' update product preview tool tips End Sub Private Sub cmdNextItem_Click() ' advance to next item in combo box With Me.cmbProduct .SetFocus ' the combobox has to have the focus to set the ListIndex If IsNull(.Value) Then ' if combo box is null set it to the first item .ListIndex = 0 ElseIf .ListIndex < .ListCount - 1 Then ' make sure it's not at the last item already .ListIndex = .ListIndex + 1 ' select the next item End If End With End Sub Private Sub cmdPreviousItem_Click() ' move to the previous item in the combo box With Me.cmbProduct .SetFocus If IsNull(.Value) Then ' if combo box is null set it to the first item .ListIndex = 0 ElseIf .ListIndex > 0 Then ' make sure we're not already at the first item .ListIndex = .ListIndex - 1 ' select the previous item End If End With End Sub Private Function SetToolTips() ' this function sets the tool tips for the next and previous buttons to a ' preview of the product each will move the list to With Me.cmbProduct ' next item button If .ListIndex < .ListCount - 1 Then ' if not at the end of the list Me.cmdNextItem.ControlTipText = ListItemText(Me.cmbProduct, .ListIndex + 1) Else Me.cmdNextItem.ControlTipText = "(At end of list)" End If ' previous product button If .ListIndex > 0 Then ' if not now at the beginning of the list Me.cmdPreviousItem.ControlTipText = ListItemText(Me.cmbProduct, .ListIndex - 1) Else Me.cmdPreviousItem.ControlTipText = "(At beginning of list)" End If End With End Function Private Function ListItemText(ByVal ctlList As Control, ByVal lngRowNumber As Long) As String ' this function returns a string made up of all the visible columns for the ' selected row of a listbox or column box Dim varItems As Variant ' to hold array of column widths Dim lngCounter As Long ' counter for loop varItems = Split(ctlList.ColumnWidths, ";") ' split the list of column widths for reading For lngCounter = LBound(varItems) To UBound(varItems) ' read through the list of column widths If Val(CStr(varItems(lngCounter))) > 0 Then ' if columns is shown (width >0) If ListItemText <> vbNullString Then ' if list not empty add a separator ListItemText = ListItemText & " | " End If ListItemText = ListItemText & ctlList.Column(lngCounter, lngRowNumber) End If Next lngCounter End Function Private Sub Form_Load() SetToolTips ' inititalize product preview tool tips End Sub