SearchBox NoComboBox NoWizardSearchCmd

[Kelly R]

I am trying to put a text box on my form where I can just type in what I am searching for (I want to be able to type in information in the search box that may be on any field on the form; I want this text box to have the ability to search any field on the form without me using a combo box where I have to select the field then type in what I am searching for and I don't want to use the Wizard to create a search command that is basically CTRL+F). I am specific to what I want. So if you could help that would be awesome...Here are my field names it would be so helpful if I could just get the code and copy and paste it into Microsoft Visual Basic. Mind you I am working with Access 2003 if that makes a difference.


Search Command is called: cmdSearch
Unbound Search Field is called: txtSearchString

Field Names are as follows:
PhysicianName
PrimaryHospital
Address1
Address2
Address3
CityStateZipCode

Please help only if you can do what I am asking I already know how to do the combo box search and wizard search and don't like either. Plus if I see I have a response I will get excited and then be bummed when realizing that 2 years of trying to figure this out continues:(

Thank you soooooooo much if you can help me with this I wouldn't even know how to explain how awesome it is that I could do this!!!

[TheSmileyCoder]

It would look something like this I guess:

Private Sub tb_Search_KeyUp(KeyCode As Integer, Shift As Integer)
    Dim strCrit As String
    strCrit = Me.tb_Search.Text
    Dim strFilter As String
    Dim rsDao As DAO.Recordset
    Set rsDao = CurrentDb.OpenRecordset(Me.RecordSource)
    Dim myF As Field
    For Each myF In rsDao.Fields
        'You can expand the code by type if you want.
        'Debug.Print myF.Name & ":" & myF.Type
        strFilter = strFilter & "([" & myF.Name & "]='" & strCrit & "') OR "
    Next
    'Strip last " OR "
    strFilter = Left(strFilter, Len(strFilter) - Len(" OR "))
    Debug.Print strFilter
    Set rsDao = Nothing
    Me.Filter = strFilter
    Me.FilterOn = True
End Sub

[Kelly R]

The name of my table is USPhysicianDirectory and the name of the form that I want the search button on is USPhysicianDirectoryFM...where would I copy and paste this information you gave me? Would I go to the search button and select On Click:[Event Procedure] and then copy this in the MVB or would I go to the unbound text boxPOn Click:[Event Procedure] and then copy this in the MVB? Sorry don't really understand...also do I need to change anything in the code to fit my form or is it all set and ready to be copied? Thank you so much for helping me!!!

[TheSmileyCoder]

The intent is that the code runs each time a key is released in the textbox tb_Search. This is also shown in the code, if you read the first line.

[Kelly R]

I'm sorry this is new to me so I don't really understand where to paste this code and if I can just paste it directly somewhere or if I need to change some of the names so that it will recognize the fields in my database.

[Kelly R]

Okay so when I put this in VB it worked but made me ad on Line 18. Me.FilterOn=True=True
So I did that then it wanted me to chage Line 17.and add extra coding so here is what we have...the problem with this is that when I go to type in a letter it only allows me to type in one letter and then says it has filtered the information however the form is then blank...any suggestions?

Private Sub tb_Search_KeyUp(KeyCode As Integer, Shift As Integer)
Dim strCrit As String
strCrit = Me.tb_Search.Text
Dim strFilter As String
Dim rsDao As DAO.Recordset
Set rsDao = CurrentDb.OpenRecordset(Me.RecordSource)
Dim myF As Field
For Each myF In rsDao.Fields
'You can expand the code by type if you want.
'Debug.Print myF.Name & ":" & myF.Type
strFilter = strFilter & "([" & myF.Name & "]='" & strCrit & "') OR "
Next
'Strip last " OR "
strFilter = Left(strFilter, Len(strFilter) - Len(" OR "))
Debug.Print strFilter
Set rsDao = Nothing
Me.Filter = "([PhysiciansName]='*') OR ([PrimaryHospital]='*')OR ([Address1]='*')OR ([Address2]='*')OR ([Address1]='*')"
Me.FilterOn = True = True
Me.FilterOn = True = True
Me.FilterOn = True = True[/b]

End Sub

[TheSmileyCoder]

Im sorry, I presumed from your original question that you hade some code experience since you claimed to know all about the other methods of filtering a form.

A few notes:

Me.FilterOn = True = True

Is equal to:

Me.FilterOn = (True = True)

Basicly the code will first evaluate whether true is equal to true, which it is, which in turn will return true, which is equal to :

Me.FilterOn = True

Your just messing up the code by adding the extra =true.

By adding the

Me.Filter = "([PhysiciansName]='*') OR ([PrimaryHospital]='*')OR ([Address1]='*')OR ([Address2]='*')OR ([Address1]='*')"

Your overruling all the previous code intended to build the filter, by replacing it with a hardcoded filter.


The code is something I threw together as an example. Not something I have extensively tested and added error handling too. For instance I forgot to add the asterisk * to the search filter. I have tested the code and tweaked it a bit.

Private Sub tb_Search_KeyUp(KeyCode As Integer, Shift As Integer)
    Dim strCrit As String
    Dim lngSelStart As Long
    lngSelStart = Me.tb_Search.SelStart
    strCrit = Me.tb_Search.Text
    Dim strFilter As String
    Dim rsDao As DAO.Recordset
    Set rsDao = CurrentDb.OpenRecordset(Me.RecordSource)
    Dim myF As Field
    For Each myF In rsDao.Fields
        'You can expand the code by type if you want,
        '  to have special code for numeric combobox
        '  fields.
        'Debug.Print myF.Name & ":" & myF.Type
        strFilter = strFilter & "([" & myF.Name & "] LIKE '*" & strCrit & "*') OR "
    Next
    'Strip last " OR "
    strFilter = Left(strFilter, Len(strFilter) - Len(" OR "))
    Debug.Print strFilter
    Set rsDao = Nothing
 
    Me.Filter = strFilter
    Me.FilterOn = True
 
    'The filter causes the textbox to loose focus,
    '  and when focus is returned automatically
    '  the form looses the cursor position and
    '  selectes all of the field. Next keypress will
    '  then replace the current text. Return focus in
    '  a more gracefull way, returning the cursor to
    '  its last known position
 
    Me.tb_Search.SetFocus
    Me.tb_Search.SelStart = lngSelStart
 
End Sub
 

[Kelly R]

You might be the greatest person in the world!!! This code works and it is awesome! Thank you sooosoooosoooooooooo much!!!

[Kelly R]

Okay I have one more question. I want to place a label over the unbound text box that says search and when the label is clicked on I want it to become invisible so that I can type in the text I am searching for or is there a code where I can set the background of the unbound text box to say search until a key is entered in it?

[Kelly R]

Actually I was able to figure it out so thanks again!

Private Sub SearchLBL_Click()
' Sets focus on tb_search.
Me.[tb_Search].SetFocus
SearchLBL.Visible = False
End Sub

[TheSmileyCoder]

Your welcome.
TheSmileyCoder