finding a record in a list box from a text box

[MJD1]

hi, looking for some guidance. I have a form in which there are several list boxes. each list box has it's own query. List0 has several columns (store, dept, class name, sku, description, units, total, date). What I would like to have is a text box on the form where I can enter the sku and either on after update or clicking a button will find the record in List0 with the matching sku and highlight the row, if the sku isn't found, a msg box that notifies the user that the sku is not found.

I'm really not sure where to start on this one.

any help is very appreciated,

thanks
martin

 

[TheAceMan1]

How are ya MJD1 . . .

I have code prepared ... but I need the names of the folowing controls:

the combobox
the textfield

If the [blue]rowsource[/blue] of List0 is a query ... whats the [blue]query name?[/blue]

If its an SQL statement ... post the SQL!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MJD1]

hi, thanks for your help!

I don't have a combo box but do have a textfield

textfield name: find_sku

the rowsource for List0 is a query named "nvr_listing"

again, thanks for you quick response and help

martin

 

[MajP]

the following should work to search any specified field that is numeric, text, or date. Just drop the following into a standard module.

Public Sub findInList(lst As Access.ListBox, fldName As String, varSearch As Variant)
  On Error GoTo errlable
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim varItm As Variant
  Set rs = lst.Recordset
     Select Case rs.Fields(fldName).Type
       'Numeric
       Case 16, 9, 1, 2, 5, 20, 7, 21, 3, 4, 19, 6
          If IsNumeric(varSearch) Then
            rs.FindFirst fldName & " = " & varSearch
          End If
       'Date
       Case 8, 22
          If IsDate(varSearch) Then
            rs.FindFirst fldName & " = " & SQLDate(varSearch)
          End If
       'Text
       Case 18, 10
          If Not IsNumeric(varSearch) And Not IsDate(varSearch) Then
            rs.FindFirst fldName & " = " & SQLStr(varSearch)
          End If
       Case Else
          MsgBox "No a valid field type"
     End Select
  If rs.NoMatch Then
    MsgBox "Search not found"
    lst = Null
  Else
    lst.Selected(rs.AbsolutePosition) = True
  End If
  Exit Sub
errlable:
  If Err.Number = 3265 Then
    MsgBox "Field not in list box row source"
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub
Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

Public Function SQLStr(varStr As Variant) As String
  SQLStr = "'" & varStr & "'"
End Function

To call from a form in some event:
call findInList(Me.ListBoxName,"FieldToSearchName",Me.textBoxName)

 

[MJD1]

Hi Majp, thanks for the help. I copied the above in a module, however I get a compile error when I try to call it. it says "expected variable or procedure, not module"

I called it from a button's on click event.

cheers

 

[MajP]

Any chance your module is also called the same as the procedure? A module and procedure can not have the same name. Also two procedures can not have the same name if in standard modules.

http://msdn.microsoft.com/en-us/library/aa156864(office.10).aspx

 

[MajP]

I had not played with that code for a while, and noticed it had some problems. Here is an update. It will now handle a boolean field search (not that useable anyways). More importantly you need to set the focus back to the listbox or it will render strangely.

Public Sub findInList(lst As Access.ListBox, fldName As String, varSearch As Variant)
  On Error GoTo errlable
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim varItm As Variant
  If Trim(varSearch & " ") = "" Then
    MsgBox "No Search Value Passed."
    Exit Sub
  End If
  Set rs = lst.Recordset.Clone
       Select Case rs.Fields(fldName).Type
       'Boolean
       Case 1
          If varSearch = "Yes" Then varSearch = -1
          If varSearch = "No" Then varSearch = 0
          If varSearch = 0 Or varSearch = -1 Then
               rs.FindFirst fldName & " = " & varSearch
          Else
            MsgBox "Not valid Boolean Value"
            lst.SetFocus
            lst = Null
          End If
       'Numeric
       Case 16, 9, 2, 5, 20, 7, 21, 3, 4, 19, 6
          If IsNumeric(varSearch) Then
            rs.FindFirst fldName & " = " & varSearch
          End If
       'Date
       Case 8, 22
          If IsDate(varSearch) Then
            rs.FindFirst fldName & " = " & SQLDate(varSearch)
          End If
       'Text
       Case 18, 10
          If Not IsNumeric(varSearch) And Not IsDate(varSearch) Then
            rs.FindFirst fldName & " = " & SQLStr(varSearch)
          End If
       Case Else
          MsgBox "Not a valid field type"
     End Select
  lst.SetFocus
  If Not rs.NoMatch Then
    lst.Recordset.Bookmark = rs.Bookmark
    lst.Selected(rs.AbsolutePosition + 1) = True
  Else
    MsgBox "Search not found"
    lst = Null
  End If
  Set rs = Nothing
  Exit Sub
errlable:
  If Err.Number = 3265 Then
    MsgBox "Field not in list box row source"
  Else
    MsgBox Err.Number & " " & Err.Description
  End If
End Sub

Here is a demo where you can pick and search any field.

http://www.4shared.com/document/H_gCIKne/FindInListBox.html

 

[PHV]

MajP, I'd use this:

Public Function SQLStr(varStr As Variant) As String
  SQLStr = "'" & [!]Replace([/!]varStr[!], "'", "''")[/!] & "'"
End Function

 

[MajP]

Thanks PHV. I will update my code library.

 

[MJD1]

hi, thanks everyone for your help. I got the initial code to work for me perfectly.

cheers
Martin