Search Record Within a ListBox

[allansiu823]

Hi I am new to MS Access and VBA and have been bothered by this problem for a looooong time. I have this listbox containing all the records (~1000) in a form and I want to be able to type something in and match it with the matching record IN the listbox. I want it to work exactly like the "Find" function in the "Edit" Menu on the Toolbar, however the Find function does not search anything within the listbox.

I tried tp create a textbox and its name is "SearchBox" with a command button called "cmdSearch" . I have the following code on the clicking event of the command button but it says Error:

Private Sub cmdGoSearch_Cli ck()

Dim SearchString As String

SearchString = Me.SearchBox.Va lue
ComputerList.Se tFocus

DoCmd.FindRecor d SearchString, , , , , acAll, True


End Sub

Is there just a syntax error or I have to use some other command or there is no way to implement a Find function like I desire in a listbox?

I have to use a listbox because the desired outlook of the interface is like viewing files in "Details" view of a normal window. I thought about using a form and aligning it nicely but it just won't give you something like a listbox. Using a subform in datasheet view is not desired either.


Please shed me some light on that. Thank you soooooooooooooo much!


allansiu823

[allansiu823]

http://www.vbcity.com/forums/topic.asp?tid=64808

heres more on that..

Hey nova_lite I have tried the second method but sadly I dont quite understand the "Send Message" thing and I tried to compile that in VBA but it is not working....Trie d your code too but there just isnt a FindString method....Do you or others have other ideas? I dont know if VB and VBA are that different.....

Thanks in advance :D

[nova_lite]

Would it be okay if you email me your source? I would like to take a closer look at it.

[nova_lite]

Actually.. rather than trying to find one function, why don't you use a for loop and loop through the list. Something like this:

Private Sub cmdListIndex_Cl ick()

' NumRows is the number of completely visible rows in the ListBox
Const NumRows = 10
' Row we want displayed on top of ListBox.
Dim intDesiredRow As Integer

If txtSearch <> "" Then
'loops through the listbox
For i = 0 To Me.ListBox1.Lis tCount
'If the Strings match
If txtSearch = ListBox1.ItemDa ta Then
'Record down row found
intDesiredRow = i
'End loop
i = Me.ListBox1.Lis tCount
End If
Next i
End If

' ListBox must have the Focus
Me.ListBox1.Set Focus
' Force ListBox to start from the top
Me.ListBox1.Lis tIndex = 1

' Force the Scroll offset we desire
Me.ListBox1.Lis tIndex = intDesiredRow + (NumRows - 1)
' Now select the row without further scrolling
Me.ListBox1.Lis tIndex = intDesiredRow

End Sub

The code above should DEFINATELY work lol.. try it out

[allansiu823]

Actually.. rather than trying to find one function, why don't you use a for loop and loop through the list. Something like this:

Private Sub cmdListIndex_Cl ick()

' NumRows is the number of completely visible rows in the ListBox
Const NumRows = 10
' Row we want displayed on top of ListBox.
Dim intDesiredRow As Integer

If txtSearch <> "" Then
'loops through the listbox
For i = 0 To Me.ListBox1.Lis tCount
'If the Strings match
If txtSearch = ListBox1.ItemDa ta Then
'Record down row found
intDesiredRow = i
'End loop
i = Me.ListBox1.Lis tCount
End If
Next i
End If

' ListBox must have the Focus
Me.ListBox1.Set Focus
' Force ListBox to start from the top
Me.ListBox1.Lis tIndex = 1

' Force the Scroll offset we desire
Me.ListBox1.Lis tIndex = intDesiredRow + (NumRows - 1)
' Now select the row without further scrolling
Me.ListBox1.Lis tIndex = intDesiredRow

End Sub

The code above should DEFINATELY work lol.. try it out

Works Perfectly!
Thank you soooo much nova!!!!

[irperera]

I would like more on the 2nd method

which is having a text box search a listbox but for vba for access.

[kealii74]

I know this reply is a little late (almost a year), but I was looking for a method to do a string search in a Access listbox. By the way, access listboxes are a little different than VB listboxes. Anyways, after unsuccessfully finding anything good on the internet, I decided to try and figure it out myself. Here's what I have. It works even if your listbox or has multiple columns. Here's the code:

=============== =============== =============
Dim strSearch As String
Dim i As Long
Dim iContinueSearch As Integer

strSearch = InputBox("Enter Search String")
strSearch = "*" & strSearch & "*"

With Me.lstMyListBox
If .ListCount > 0 Then
.Selected(0) = True
For i = 0 To .ListCount - 1
If .Column(3, i) Like strSearch Then
.Selected(i) = True
iContinueSearch = MsgBox("Do you want to continue searching?", vbQuestion + vbYesNo)
If iContinueSearch = vbNo Then
Exit For
End If
End If
Next i
End If
End With
=============== =============== ==============

The code above searches the contents of the listbox called lstMyListBox. There are 4 columns in the listbox and the code above searches the 4th column. You can change this by changing the .Column(#, i) where # is index of the column (0 is the base). The code above simply goes through the list from top to bottom and searches whatever column for a search string that you pass to it with the strSearch variable. I added a "*" at the beginning and end of the search string and used a like operator to conduct a wildcard search. This allows you to search the column data for anything that contains your search string. The code also prompts you if you want to continue searching for the next match until it reaches the end of the list and no more matches are found and the search will end.

Hope this helps.