CODEWITHSUNDEEP
vbams-access
Fil
Fil

Reputation: 471

Using a ListBox to Navigate between records

I have a ListBox in a form which has textBoxes bound to a table. The form is used for editing records. I am not sure how to use the ListBox items to navigate between records. The action would be similar to how a split form works but in this case, instead of using a datasheet I am using a ListBox.

The following is my code:

Private Sub Form_Load()
Dim sqlstr As String
sqlStr = "SELECT CustomerStreet, CustomerName, Address FROM tblCustomers ORDER BY CustomerName"
Me.SearchListBox.RowSource = sqlStr
End Sub

Private Sub SearchListBox_AfterUpdate()
Dim str As DAO.Recordset
Dim strRecord As Integer

Set rs = Me.RecordsetClone
strRecord = rs.Fields("ID").Value

DoCmd.GoToRecord acDataForm, "frmEdit", acGoTo, strRecord

End Sub

Upvotes: 1

Views: 243

Answers (1)

June7
June7

Reputation: 21370

RecordsetClone is only useful if you use FindFirst and Bookmark. Your code just uses first record ID. This will not correctly apply Offset argument.

The listbox needs ID field. That column can be hidden with 0 width. Typing first letter of an item will advance through listbox items. As is, user must type first letter of customer street not customer name. Since listbox RowSource is not dynamically filtered, could just include it in listbox design instead of setting with Load event. SELECT ID, CustomerStreet, CustomerName, Address FROM tblCustomers ORDER BY CustomerName;

If you want to go to a record as opposed to filtering the form, something like:

Private Sub SearchList_AfterUpdate()
With Me.RecordsetClone
   .FindFirst "ID=" & Me.SearchListBox
   If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

Upvotes: 1

Related Questions