Search Box - Microsoft Access Forum - Computer Learning Zone


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Search Box

Joe Sutcliffe

2 years ago

I have a search box on a form with a Select statement. It will search on all the fields and return the desired results with the exception of "Company" and "JobTitle" which are combo boxes on the form. I am using the after-update event. In these combo boxes I show the description, and the ID is what is stored in the table. Below is my code. What am I doing wrong? Screen shots are attached. Thank you.

Details

Private Sub SearchBox_AfterUpdate()

If SearchBox <> "" Then
    Me.FilterOn = False
    Query = "SELECT * FROM Contacts " _
    & "WHERE FirstName Like '*" & Me.SearchBox & "*' " _
    & " OR LastName Like '*" & Me.SearchBox & "*'" _
    & " OR Company Like '*" & Me.SearchBox & "*'" _
    & " OR JobTitle Like '*" & Me.SearchBox & "*'" _
    & " OR EmpID Like '*" & Me.SearchBox & "*'" _
    & " OR Address Like '*" & Me.SearchBox & "*'" _
    & " OR City Like '*" & Me.SearchBox & "*' " _
    & " OR BusinessPhone Like '*" & Me.SearchBox & "*' " _
    & " OR MobilePhone Like '*" & Me.SearchBox & "*' " _
    & " OR HomePhone Like '*" & Me.SearchBox & "*' "
    
    Me.RecordSource = Query
    Me.Requery
    
Else
    Me.RecordSource = "SELECT * FROM ContactsExtended ORDER BY LastName"
    Me.Refresh
    Forms!ContactList!SearchBox.SetFocus
    
End If
End Sub

	Joe Sutcliffe OP @Reply 2 years ago

	Joe Sutcliffe OP @Reply 2 years ago

	Joe Sutcliffe OP @Reply 2 years ago

	Sami Shamma @Reply 2 years ago
Joe, What is going wrong? Do you get an error message? if so what is it? you do not need to reference controls on the from with Me.XXX. In your code the only time you need Me. is: Me.RecordSource = Query Me.Requery Also "Query" is a Reserved Word. see : Click Here

	Joe Sutcliffe OP @Reply 2 years ago
I am not getting an error message. No results are returned.

	Kevin Robertson @Reply 2 years ago
You will need to access the Column property of your Combo Boxes if the text you are searching for is in the second column (the first being the ID). `ComboBoxName.Column(1)`

	Sami Shamma @Reply 2 years ago
Are you diming your Variable ? Are you using option Explicit? in not, do it. Change the "Query" to something like "MySearch" Then msgbox "MySerch" and make sure the syntax is correct. If after this you still do not get results, then simplify the code and start with one search, make it work then add the next etc.

	Joe Sutcliffe OP @Reply 2 years ago
Hi Kevin. when I do that, I get the error message in the attached screen shots. Thank you.

	Joe Sutcliffe OP @Reply 2 years ago

	Joe Sutcliffe OP @Reply 2 years ago

	Sami Shamma @Reply 2 years ago
Is JobTitle the name of the combo box? Check its property under Name not record source. And you ae still using the reserved word "Query"

	Kevin Robertson @Reply 2 years ago
Is the Record Source based on a Table or Query (this is one of the reason Richard uses 'T' and 'Q')? If it is a Table you may be searching for an ID as a String (your SearchBox). Build a Query that brings together all your data (Short Text fields required for the search) then base your Record Source off of that.

	Joe Sutcliffe OP @Reply 2 years ago
Sami. I have used "query" in several other databases and the other fields return results using the word query. When I change it, I receive the following error message "Compile error "Variable not defined". Thank you.

	Sami Shamma @Reply 2 years ago
Joe, we are trying to help you. If you have not run into a problem with it now, you will in the future. Read my early response to you, I asked you if you are declaring your Variables? watch this video Variables

	Sami Shamma @Reply 2 years ago
to avoid problems like you are facing now, start using good development practices that Richard teaches and repeat all the time. Kevin gave you good advice on naming Tables and Queries.

	Joe Sutcliffe OP @Reply 2 years ago
Thank you, Sami. I understand about the reserved word and have changed it and declared the variable. I am trying to clean up something that I inherited. I use "tbl", "qry", and "rpt" when naming objects.

	John Davy @Reply 2 years ago
Hi Joe, Sami asked the question "Is JobTitle the name of the combo box?" Have you check the name on this combo box? John

	Joe Sutcliffe OP @Reply 2 years ago
Hi John, yes "JobTitle" is the name of the combo box. Thank you.

	Sami Shamma @Reply 2 years ago
Joe, make your programming life easier by following a good naming convention. I use Richard's naming convention. Where all combo boxes are called xxxCombo, all Buttons are called xxxBtn etc. It does not matter which convention you follow as long as you follow one.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View