dynamic search box possible in VBA? (1 Viewer)

[ncimike3]

Some time ago I took a class in Visual Basic Studio and had to construct a simple form which was linked to a database. I had a search box on the form that would filter my search results as I typed. In other words for every key stroke I made I could watch the filter working away filtering my results. This was really cool because it would give me instant feed back as I typed. If I accidentaly hit the wrong key I could would catch it immediately due to the visual feedback of seeing my filtered results go to hell. I would then back space once and then keep typing until my relsults matched what I wanted. VBA is a different story. I isn't built into VBA like it was in VB Studio. The way my form is currently set up, I have a search box that after I type in search words I have to hit enter to see the results. the search results are shown in a list box called search. I can then click on one of the items in the list and it will take me to that record and hide the list box. I want to make the search work like it did in Visual Basic Studio. As I type I want to see the list dynamically updating. Then when I see the results are sufficient, I then click on the record in the list. Way cooler and easier for the user. Here is the code I currently have. I have also attached a copy of the form and what it looks like when I enter my search. Any help would be greatly appreciated. Thanks.

Private Sub txtSearchField_AfterUpdate()
Me.Filter = "customer like '*" & Me.txtSearchField & "*'"
Me.Filter = Me.Filter & "OR desc like '*" & Me.txtSearchField & "*'"
Me.Filter = Me.Filter & "OR [partnum] like '*" & Me.txtSearchField & "*'"
Me.txtSearchField = ""
Me.blanksearch = ""
'Show the search results
Search.RowSource = "SELECT [TblOpSheetData].[ID], [TblOpSheetData].[DESC], [TblOpSheetData].[PartNum] As [PART #], [TblOpSheetData].[CUSTOMER] As [COMPANY] FROM [TblOpSheetData] " & _
"WHERE " & Me.Filter
Search.Requery
If Search.ListCount = 2 Then 'ListCount includes header row
Search.Selected(1) = True
DoCmd.GoToRecord acDataForm, Me.name, acGoTo, Search.ListIndex + 1
txtSearchField.SetFocus
ElseIf Not Search.Visible Then
Search.Visible = True
CloseSearch.Visible = True
End If
'DoEvents
End Sub



Private Sub Search_AfterUpdate()
If Search.ListIndex > -1 Then
DoCmd.GoToRecord acDataForm, Me.name, acGoTo, Search.ListIndex + 1
txtSearchField.SetFocus
Search.Visible = False
CloseSearch.Visible = False
End If
End Sub

 

[John Big Booty]

Perhaps the Sample here does the sort of thing you are looking for.

 

[estentz]

I am new to the forums, and I signed up just because of this post. I realize it is old, but I have been searching for a way to do this same thing for months and felt like I should share what I found since I am very happy with it and it seamed simple. And maybe some of the real pros here can make it even better.

You can do it two ways. One with a sub form or with a split form.

First make a query for the fields that you want in your form.
Add separate fields with this in the field blank for each field you want to search.(you can search dynamically multiple fields)
Expr1:Nz(InStr([Field you want to search],[TempVars]![SearchText]),1)
Expr2:Nz(InStr([Field you want to search],[TempVars]![SearchText]),1)
etc.
Then in the criteria section put this "<>0" for each one going down one line with each iteration.

Then create a form based on this query( with a subform datasheet or split form) and put design it how you want the layout.
Next create an unbound text box. go to the on change event and enter this code

TempVars!SearchText = [name of text box].Text

Forms![the form you created].Requery

[name of text box].SetFocus

Me.[name of text box].SelStart = Nz(Len(Me.Searchbox), 0)

Me.[name of text box].SelLength = Nz(Len(Me.Searchbox), 0)

what it does is call on the tempvars function and creates a temporary variable that you can use and then easily reassign. The query gets updated with that info and only outputs fields with that info. Then the rest just keeps the courser in the correct spot.

Let me know what you think or if you have any questions.

 

[AccessBlaster]

Can you attach a small working copy? You may have to place it in a zip file, because of your post count.

 

[estentz]

Here is the database in access 2013

Tt has one table one querry and one form, but it works and has all the info in it.

 

[AccessBlaster]

Thank you.

 

[estentz]

No Problem.

Let me know how it works for you if you get a chance.

 

[Accessna]

Me.Filter = "customer like '*" & Me.txtSearchField & "*'"
Me.Filter = Me.Filter & "OR desc like '*" & Me.txtSearchField & "*'"
Me.Filter = Me.Filter & "OR [partnum] like '*" & Me.txtSearchField & "*'"

Try to keep space before "OR" to be:

Me.Filter = "customer like '*" & Me.txtSearchField & "*'" & _
" OR desc like '*" & Me.txtSearchField & "*'" & _
" OR [partnum] like '*" & Me.txtSearchField & "*'"