In Access databases, I find it nice to have a listbox on each form that allows the user to quickly jump to a given record in the form’s dataset. Access2007 makes that very easy; when you drop a listbox on the form, the wizard has an option to automatically perform this operation.
However, when the user moves to another record using the navigation buttons (or simply pressing tab through all of the form’s fields), the listbox is not updated. Having this navigation listbox synchronized has been something of a holy grail for me the past 18 months, but I could never get it to work correctly. Until today…
This example assumes that the listbox is bound to the dataset’s key field, which is also displayed in textbox on the form (called ItemID in this example).
On the forms OnCurrent event, create an Event Procedure and call this new procedure:
Private Sub Synch_Listbox()
If Me.lstItems = CStr(Me.ItemID) Then
For i = 0 To lstItems.ListCount - 1
If Me.lstItems.Column(0, i) = "ItemID" Then
Dim FormID As Integer
Dim ListID As Integer
FormID = Me.ItemID.Value
ListID = Me.lstItems.Column(0, i)
If ListID = FormID Then
Me.lstItems.Selected(i) = True
End If
End If
Next
End If
End Sub
The first if statement If Me.lstItems CStr(Me.ItemID) is important; we don’t want to call this synchronization routine if the list box and form data is already synched. I have to convert the integer ItemID to string to match the strings returned by the listbox. Also, in the loop, we need to make sure we don’t try to match the column header; that’s the purpose of the if statement If Me.lstItems.Column(0, i) “ItemID”.
That’s all there is to it; it’s amazing how simple it turned out to be. 🙂
WindowsMunky 