enhanced search 1

[Moxy1]

i have a list box with 100's of rows. I'd like to create an unbound field that as a user types it will begin to filter down the list. Has anybody attempted this and would you be able to point me to some examples?

Here is basically what i've tried:

Private Sub txtSearch_Change()

Dim strSearch As String
Dim strValue As String

strValue = Me.txtSearch

strSearch = "SELECT DISTINCT tblObject.obj_location FROM tblObject WHERE (((tblObject.ojb_type)=Forms!frmMainObject!txtType)) AND ((tblObject.ojb_location) Like '" & strValue & "*'; "

Me.lstBox.RowSource = strSearch

End Sub

The problem that i run in to is that when the search field is changed the new value is not being picked up.

 

[PHV]

Replace this:
strValue = Me.txtSearch
with this:
strValue = Me!txtSearch.Text

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JBinQLD]

G'day,

And add line as below to requery the list....

...

Me.lstBox.RowSource = strSearch
[red]me!lstbox.requery[/red]

Maybe consider the Afterupdate event, (or even an invisible button with Default set to true) rather than on change so that the code only runs once the user has typed something and hit enter rather than on every keypress?

Good luck!

JB

 

[PHV]

JB, the Requery is automatically triggered when the RowSource is changed.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JBinQLD]

Thanks PHV! I didn't know that, is it true of every version of Access? I'm sure I used to need it and these habits stick. But then, so does sh!t......!

Cheers for the heads up mate, will remove my redundant lines on Monday.

JB