Using a listbox to pinpoint "wildcard" search.

[jwrocko]

Ok, here goes.

I am creating a form to edit user information. I need to be able to type in all or part of a username. Part of this is completed already - if I type in a full username, I can go from there. On a partial name, however, I want a listbox containing all the usernames to pop up (modal), and I want the closest match to be highlighted. It is important that all values remain intact since I also need this same function for part inventory lookup.

The place that has me stuck is highlighting the closest match on the listbox.

Sorry there is no code to go by...that is the problem.

 

[CautionMP]

jwrocko,

Something like this may work for you.

I tested it by clicking [tt]Command2[/tt] using a string in [tt]Text1[/tt] but the routine could just as wasily be fired in the [tt]Open[/tt] event of a popup form using the [tt]OpenArgs[/tt] as the source of the search string.

Sub FindFirstMatch(lb As Access.ListBox, SearchString As String)
  Dim lngLowestFind As Long, lngThisFind As Long
  Dim lngItem As Long, lngReturn As Long
  
  lngLowestFind = 65536
  
  For lngItem = 0 To lb.ListCount - 1
    lngThisFind = InStr(1, lb.ItemData(lngItem), SearchString)
    If lngThisFind <> 0 Then
      If lngThisFind < lngLowestFind Then
        lngLowestFind = lngThisFind
        lngReturn = lngItem
      End If
    End If
  Next lngItem
  lb.Selected(lngReturn) = True
End Sub

Private Sub Command2_Click()
  FindFirstMatch Me.List0, Me.Text1
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[TheAceMan1]

Howe are ya jwrocko . . .

[blue]I want a listbox containing all the usernames to pop up (modal), and I want the closest match to be highlighted.[/blue]

You can do this with a combobox! With the combo's [blue]Auto Expand[/blue] property set to [blue]Yes[/blue], you get the eqivalent of [blue]find as you type[/blue]. The list automatically drops down so you can see where you are.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[jwrocko]

Well, I found some help on MSDN that fixed me up. I didn't want to go the combobox route. I needed to keep the total list in tact.

        Me.Form.Caption = "Part Numbers"
        Me.Lookup_User_Label.Caption = "Part Numbers"
        Set partsList = Me!List4
        partsSql = "SELECT Parts.Part_Number, Parts.Component FROM Parts ORDER BY Parts.Part_Number;"
        With partsList
            .RowSourceType = "Table/Query"
            .RowSource = partsSql
            .ColumnCount = 2
            .Requery
        End With

        sql = "SELECT DISTINCT Parts.Part_Number FROM Parts WHERE Parts.Part_Number LIKE '" & [Form_Insert Part Number].partNum & "*';"
        Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

If rs.RecordCount > 0 Then
        rs.MoveFirst
        List4.Value = rs!Part_Number
        rs.Close
    End If

that produces the functionality I am looking for.

The way it ends up working with the rest of the program is, I can type an entire/partial part number. If I type the entire part number, it will just pull up the part information form. If I type a partial number, I'll get "thrown" to the form with the listbox, the closest match to what I typed will be highlighted (all other part numbers viewable).