Listbox help

[Chrisopia]

A list box uses the query "Query1" to list data filtered through a search bar, below it is a group of text boxes with the same fields as the query, they are also sourced from the same query.

The idea is to click on the list box when the search has been filtered and it will fill in the boxes below it.


I've tried one method and it failed, this method relies on a companyName being present, which isn't always the case.

Private Sub QuickSearch_DblClick(Cancel As Integer)
DoCmd.Requery
Me.RecordsetClone.FindFirst "CompanyName = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
   Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If
End Sub

I have tried to apply an If statment, and various methods of trying to ask first if CompanyName is present or not but it doesn't seem to work.

 

[wiklendt]

are simply trying to navigate to a certain record from a listbox? generally, i base the listbox on a different query, second, i use this simple code (yours was slightly incorrect) in the on_click event of the listbox (you shouldn't need a "record not found" because if it's in the listbox, then it exists):

Private Sub lstSpecimens_Click()
    
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SpecimenID] = " & Str(Nz(Me![lstSpecimens], 1))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

where [SpecimenID] is the PK used in the form, and [Me.lstSpecimens] is the item chosen in the listbox.

i also filter my listbox using a "search" textbox. this is basically just a text control which sends it's value to a hidden textbox (on_chagne event of textbox - so it gives real-time filtering of the listbox), which can then be used in the listboxes criteria for one or more fields in a "like" argument to filter the listbox.

Private Sub txtSearch_Change()

    Dim vStrSearch As String
    
    vStrSearch = txtSearch.Text
    txtSearchVal.Value = vStrSearch
    
    Me.lstSpecimens.Requery

End Sub

where txtSearch is the search textbox, and txtSearchVal is the hidden textbox that you use as the criteria in your query field(s)

edit: have a look at the attached image to see what it looks like