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_Click()

Dim SearchString As String

SearchString = Me.SearchBox.Value
ComputerList.SetFocus

DoCmd.FindRecord 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

[nova_lite]

Try this, im new to access and vba too tho so no idea if it will work..

ListBox1.SelectedIndex = ListBox1.FindString("SearchString")

[allansiu823]

Try this, im new to access and vba too tho so no idea if it will work..

ListBox1.SelectedIndex = ListBox1.FindString("SearchString")

It seems okay but there is no "FindString" method.......any similar methods though?

[nova_lite]

try FindByValue

[allansiu823]

Doesnt work either.

I think the problem is the data type. It seems like the listbox does not have those function because when I type ListBox1. and search through the options in the pop up list I cannot find anything related to "find" (nor FindString and FindByValue). Would this be because a listbox simply just doesnt support these find functions? Is there another way to display the records like a listbox then?

[nova_lite]

http://msdn.microsoft.com/library/de...tringTopic.asp

there is a FindString method for listboxes, I don't know if it is for access tho.

[nova_lite]

Try this:

If the items contained within your ListBox are just strings then you could use the IndexOf() method to search the items for a specific string and then set the ListBox’s SelectedIndex property to the value gained from IndexOf() ala:

// Find the item in the list and store the index to the item.
int index = listBoxPartInfo..Items.IndexOf (searchString);
// Determine if a valid index is returned. Select the item if it is valid.
if (index != -1)
listBoxPartInfo.SelectedIndex = index;
else
MessageBox.Show("The search string did not match any items in the ListBox");

[nova_lite]

Or this lol.. sorry im just shooting you things I found from other forums..

From time to time, I am frequently asked this question – How do I search a listbox for a string? Well you need ask now more!


Code:
Option Explicit
Private Declare Function SendMessage Lib "User32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Integer, ByVal wParam As Integer, lParam As Any) As Long

'Searchs a listbox for a matching string. Returns the listindex of the matching item.

Function ListboxFindString(strSearchString As String, lHwndListbox As Long) As Long
Const LB_FINDSTRING = &H18F
ListboxFindString = SendMessage(lHwndListbox , LB_FINDSTRING, -1, ByVal strSearchString)
End Function



Code:
Private Sub Text1_Change()
Me.List1.ListIndex = ListboxFindString(Me.Text1, Me.List1.hWnd)
End Sub

Private Sub Form_Load()
With List1
.AddItem "Haresoftware"
.AddItem "LocateSoft"
.AddItem "EncodeSoft"
.AddItem "IconSoft"

End With
End Sub


Hey Presto! Search a ListBox for a string!

[nova_lite]

the second method seems to work, try it.

[nova_lite]

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

heres more on that..

[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....Tried 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_Click()

' 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.ListCount
'If the Strings match
If txtSearch = ListBox1.ItemData Then
'Record down row found
intDesiredRow = i
'End loop
i = Me.ListBox1.ListCount
End If
Next i
End If

' ListBox must have the Focus
Me.ListBox1.SetFocus
' Force ListBox to start from the top
Me.ListBox1.ListIndex = 1

' Force the Scroll offset we desire
Me.ListBox1.ListIndex = intDesiredRow + (NumRows - 1)
' Now select the row without further scrolling
Me.ListBox1.ListIndex = 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_Click()

' 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.ListCount
'If the Strings match
If txtSearch = ListBox1.ItemData Then
'Record down row found
intDesiredRow = i
'End loop
i = Me.ListBox1.ListCount
End If
Next i
End If

' ListBox must have the Focus
Me.ListBox1.SetFocus
' Force ListBox to start from the top
Me.ListBox1.ListIndex = 1

' Force the Scroll offset we desire
Me.ListBox1.ListIndex = intDesiredRow + (NumRows - 1)
' Now select the row without further scrolling
Me.ListBox1.ListIndex = 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.