Help with a selection in a list box

[Eric]

Hello everyone,

I have a listbox that is associated with a query. What I want to do is
select the record in the listbox, and transfer the contents of two fields
into two text boxes called txtSerialNumber and txtProductDescription. I am
having a hard time trying to figure out what record I am on in the listbox.
If anyone could help me out, this would be greatly appreciated.

The code below doesn't seem to work very well, if at all:

Private Sub lstResults_AfterUpdate()
Dim rsCurrentRecord As Recordset, dbCurrent As Database
Dim strCriteria As String

On Error GoTo ErrHandler

Set dbCurrent = CurrentDb
strCriteria = Me.txtQuery.Value & Me.lstResults.Column(1)
MsgBox strCriteria

Set rsCurrentRecord = dbCurrent.OpenRecordset(strCriteria)

With rsCurrentRecord
Me.txtSerialNumber.Value = !SerialNumber
Me.txtProductDescription.Value = !ProductDescription
End With

ErrHandler_Exit:
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ErrHandler_Exit

End Sub

This does work:

Private Sub btnApplyFilter_Click()
Dim strWhere As String, strSQL As String
'Build the WHERE clause
strSQL = "SELECT SerialNumber, ProductDescription, User, IsObsolete" _
& " FROM tblProducts WHERE "

If Me.txtDescription.Value = "" And Me.cboItemSearch.Value <> "Obsolete"
Then
MsgBox "Please provide a description.", vbInformation + vbOKOnly
Me.txtDescription.SetFocus
Exit Sub
End If

Select Case Me.cboItemSearch.Value
'"Serial Number","Product Description","User","Obsolete"
Case "Serial Number"
strWhere = "tblProducts.SerialNumber Like '*" &
Me.txtDescription.Value & "*'"
Case "Product Description"
strWhere = "tblProducts.ProductDescription Like '*" &
Me.txtDescription.Value & "*'"
Case "User"
strWhere = "tblProducts.User Like '*" & Me.txtDescription.Value
& "*'"
Case "Obsolete"
If Me.chkObsolete.Value = -1 Then
strWhere = "tblProducts.IsObsolete Is Not Null"
Else
strWhere = "tblProducts.IsObsolete Is Null"
End If
Case Else
MsgBox "A error occured in your search clause.", vbExclamation
Exit Sub
End Select
Me.lstResults.RowSourceType = "Table/Query"
Me.lstResults.RowSource = strSQL + strWhere
Me.txtListCount.Value = Me.lstResults.ListCount
Me.txtQuery.Value = strSQL + strWhere

End Sub

 

[Klatuu]

Look in VBA Help for the ListIndex property. It will show you how to know
what row you are on.