Please Help

Thanks for the first response, I still get the same error. It says "Run-Time Error "2342":
A RunSQL action requires an argument consisting of an SQL statement. Here is my code. I thought my RunSQL statement had the argument. Please tll me what I am missing and doing wrong. Thanks :
Private Sub cmdResults_Click()
'Once button is clicked, search results will be displayed

Dim strField As String 'Field Name from listbox
Dim strText As String 'Data for filter from textbox
Dim strSQL As String 'Vari. for SQl string

strField = Form_Othersfrm!listFieldNames.Value
strText = Form_Othersfrm!txtData.Value

strSQL = "SELECT * FROM Results WHERE " & strField & " LIKE '" & strText & "';"


DoCmd.RunSQL (strSQL) 'Here is where the error appears!


End Sub

Try this:

strSQL = "SELECT * FROM Results WHERE " & strField & " LIKE '" & strText & "';"

You can place a wild card to search on just the first few letters by using the '*' in the sql like below. That way you don't have to type in exactly the item you are searching on.

strSQL = "SELECT * FROM Results WHERE " & strField & " LIKE '" & strText & "*';"

lwells

Thanks for the reply but it still isn't working. I updated the question to show what the error is.

First I will apologize for the previous post...the DoCmd.RunSQL only applies to action type queries, such as an append, update, make table or delete type queries.

What you are wanting to do is make a search and then review the results of that search. You will need to modify the code slightly.

There are two ways to go about this depending on how you want to display the results of your search.

(1) if you are using one form to enter the search information and then open another form based on the search results then do the following.

First create a query of the table you are searching on. Add all the fields to the query and save the query with an appropriate name. Next set the record source of the form to be opened that will display the results to this query.

Now add the following code behind your cmdResults command button:

Private Sub cmdResults_Click()

Dim qdf As QueryDef
Dim strField As String 'Field Name from listbox
Dim strText As String 'Data for filter from textbox
Dim strSQL As String 'Vari. for SQl string

Set qdf = CurrentDb().QueryDefs("NameOfQuery")

strField = Form_Othersfrm!listFieldNames.Value
strText = Form_Othersfrm!txtData.Value

strSQL = "SELECT * FROM Results WHERE " & strField & " LIKE '" & strText & "';"

qdf.SQL = strSQL
DoCmd.OpenForm ("NameOfForm")

End Sub

Use the name of your query and form in place where the text is in Bold above.

If you are using Access 2000 or higher, you may need to modify the above and set references to the DAO Object library and then change the code slightly to look in the DAO library if you get a compile error with the above code.

Dim db As DAO.Database
Dim qdf as DAO.QueryDef

Set db = CurrentDB()
Set qdf = db.QueryDefs("NameOfQuery")

Or

(2) If you are going to use the same form to display the results that has the Othersfrm!listFieldNames list box and the Othersfrm!txtData textbox you are searching with, then bound this form to your table and use the bookmark method to display the results.

Use the following code behind your command button as shown below:

Private Sub cmdResults_Click()

Dim strField As String 'Field Name from listbox
Dim strText As String 'Data for filter from textbox
Dim strSQL As String 'Vari. for SQl string

strField = Form_Othersfrm!listFieldNames.Value
strText = Form_Othersfrm!txtData.Value

strSQL = "SELECT * FROM Results WHERE " & strField & " LIKE '" & strText & "';"

Me.RecordsetClone.FindFirst strField & " Like '" & strText & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

This will move the records to the one you were searching on.

Hope this explains how to make a search form for you. Post back if you have additional questions.

lwells

You are a genius. It works perfectly. Thank you very much. Now let me ask you a question, I don't know much about VBA is there a good book to learn from? Also please explain why you had to do this part:
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryResults2")
and
qdf.SQL = strSQL.

What does that suff do and mean. Once again thank you very much.

There are several good books on Access, but honestly the one that I found very useful was Beginning VBA for Access 2000 and sorry I don't remember the authors off hand, but if you want I will dig it up and post here.

Basically and simply, beginning with Access 2000, two Object models were available. The DAO and the ADO librarys. While both are very similar each have their own syntax and methods, however the syntax is so similar between them that Access may not know which library to look in for any given snippet of code. Therefore to explicitly state which library to look in is sometimes required. The Set qdf made reference to the query collections in the current database and which query. The qdf.SQL refered to the sql of the query and then set the sql of the query to the strSQL that was stated.

Anyway, just look for any beginners book on access and you will be able to get a better understanding of the differences between ADO and DAO.

lwells