Problem with SQL & VBA

[gopher_by_fende]

Hi,

I am trying to make a search form for a new database, but i am running into problems. When i enter something into the search text box and hit the search button, i get the error -

Runtime Error 2001

You cancelled the previous operation

and it highlights a section of code that at fault -

Option Compare Database

Private Sub btnSearch_Click()

Dim strSearchType As String
Dim strSearch As String
Dim strSQL As String

strSearchType = Me.txtSearchtype
strSearch = Me.txtSearch

'Update the record source
Me.frmsubjobinfo.Form.RecordSource = "SELECT * FROM jobs_query WHERE " & strSearchType & strSearch

'Requery the subform
Me.frmsubjobinfo.Requery

End Sub

Private Sub optSearch_AfterUpdate()


Select Case optSearch.Value
Case 1
txtSearchtype.Value = "Jobno = "

Case 2
txtSearchtype.Value = "quoteno = "

Case 3
txtSearchtype.Value = "custno = "

Case 4
txtSearchtype.Value = "name = "

Case 5
txtSearchtype.Value = "sitename = "

End Select

End Sub

I'm new to VBA so there is probably something obvious to you all that i can't see, but i just can't figure it out.

Any help would be appriciated

Thanks

 

[gopher_by_fende]

*BUMP*

Anyone??!? HELP!

 

[Sergeant]

I totally misunderstood your problem at first...
Your criteria is a string, right?

Me.frmsubjobinfo.Form.RecordSource = "SELECT * FROM jobs_query WHERE " & strSearchType & " ' " & strSearch & " ' "

(Spaces are inserted between quote marks for clarity...remove them.)

 

[gopher_by_fende]

Thanks,

that seems to make it work ok for the most part, however, when i choose
"quote no" as the search option, it still comes up with the "you cancelled the previous operation" error, and when i choose "custno" as the search option, it opens a dialog box asking me for the customer number instead of using the textbox. any idea why this is happening? it works fine for jobno, name & sitename though. Also, at the moment, you have to enter the full customer name for it to find anything. how would i change the code so i could find all results that start with whatever is in the search box, ie - if i put in "mc" i would like it to find all results that start with that like McDonalds. I know it has something to do with changing the = to LIKE in the sql and possibly adding a wildcard * to it, but i am still having trouble getting my head around where all the quotation marks need to go.

Thanks for the help so far.

 

[Sergeant]

If it's a string-type field (text, memo...), you need to wrap the criteria in single quotes. So you'll need to work out some logic to build the appropriate query syntax.

 

[gopher_by_fende]

thanks, i've sorted the LIKE part of my problem, but i still cannot see why when i search using quote number that it still comes up with the same error. the quote number is an autonumber field, could this have anything to do with it?

 

[Sergeant]

If it's an autonumber field, you don't want the single quotes.

 

[gopher_by_fende]

Thanks for the help, ive got it all working now, you do not know how grateful i am!!! i have been trying to get this search form right for ages, and now i have, that is a big part of my DB sorted!! YOU THE MAN!

Thanks