search by keyword (1 Viewer)

[mohamedmatter]

Good evening to all members
I Attached is a simple database
The target form "SearchFrm"
I want to search in three fields when writing a letter. Note I do not want to use a search button. I tried to use the vba code under the search button. but i failed

 

[June7]

Why isn't the ID field an autonumber?

What does 'failed' mean - error message, wrong result, nothing happens?

I prefer to set form Filter and FilterOn properties. Review http://allenbrowne.com/ser-62.html

 

[moke123]

if your trying to do "Search as you type" I believe you need to use the .text property and not the default .value.

try

Private Sub Stxt_Change()

    Dim SQL As String
    
    SQL = "SELECT Emptbl.ID, Emptbl.[Emp-No], Emptbl.[National-Id], Emptbl.FullName " _
          & "FROM Emptbl " _
          & "WHERE [Emp-No]LIKE '*" & Me.Stxt.Text & "*' " _
          & "OR [FullName]LIKE '*" & Me.Stxt.Text & "*' " _
          & "OR [National-Id]LIKE '*" & Me.Stxt.Text & "*' " _
          & "ORDER BY Emptbl.ID "
          
    Me.SEmpList.Form.RecordSource = SQL
    
    Me.SEmpList.Form.Requery
    
End Sub

 

[bsacheri]

If you are still looking for a solution, I have contributed to a VBA Class that can be attached to any combo box and provide find-as-you-type capabilities. This is something that I had looked for for years so I feel I should share it on this site since this is a recent inquiry.

Some features of this class:

• It's fast. It keeps the list in memory and doesn't requery the database with each keystroke. I have used this class with a combobox containing over 12,000 records
• It lets you search multiple columns. For example, First Name and Last Name can be in separate columns and the list will be reduced to show a match in either column.
• It's a class. It can be attached to existing combo boxes without adding any event handler code behind existing combo boxes.
• Converting an existing combo box is as easy as adding 2 lines of code! (after adding the class module)
• It automatically cleans up after itself when the form closes.

It can be intimidating if you haven't used a class before but it is not that hard. It comes down to a few steps.

• Add the class module to your VBA project.
• Declare a class variable at the top of your form code module.
• Add a line of code in Form_Load() to initialize the class variable and bind it to an existing combo box.
• Viola! Your combo box now has find-as-you-type behavior.

I created a downloadable demo database so you can see the class in action.

Ugh! I'm too new to this forum to be able to post links to the code and sample database. :banghead:

If you go to tek-tips.com and search for "Find as you type combobox class that allows requery" you may find the Class source code. The message may be continued into multiple threads.