Compile Error

[KerryDee]

Hi I am getting a Compile Error with the following code with the highlight strsearch = The code is below:

Option Compare Database
Option Explicit

Private Sub btnSearchCarlsbad_Click()
Dim strString As String
Dim strText As String
strText = Me.txtSearch.Value
strsearch = "Select* from Carlsbad where ((LineItemCode like ""*" & strText & "*"") or(FirstName like ""*" & strText & "*"")or(ServiceAddress like ""*" & strText & "*"") or(Location like ""*" & strText & "*"") or (LastName like ""*" & strText & "*""))"
Me.RecordSource = strsearch
End Sub

Any help to get rid of the error would be gratefully appreciated. Thank you very much

 

[MarkK]

Is there an error number or message?

 

[KerryDee]

Hi the error does not give me a number, it says the following:

Compile error:

Variable not defined


Thank you.

 

[MarkK]

The variable strsearch is not defined. You need to "Dim" it as you've done with strString and strText.

 

[KerryDee]

Thank you very much. I am a novice at VBA what but exactly should the line code say? I got this code off the internet.


Thanks again.

 

[MarkK]

See the stuff in blue...

Private Sub btnSearchCarlsbad_Click()
   Dim [COLOR="Blue"]strString[/COLOR] As String
   Dim strText As String

   strText = Me.txtSearch.Value
   [COLOR="Blue"]strString[/COLOR] = "Select* from Carlsbad where ((LineItemCode like ""*" & strText & "*"") or(FirstName like ""*" & strText & "*"")or(ServiceAddress like ""*" & strText & "*"") or(Location like ""*" & strText & "*"") or (LastName like ""*" & strText & "*""))"
   Me.RecordSource = [COLOR="Blue"]strString[/COLOR]
End Sub

 

[Beetle]

See the example below. The line in red is what you need to add to your code.

Private Sub btnSearchCarlsbad_Click()
Dim strString As String
Dim strText As String
[COLOR="Red"]Dim strSearch As String[/COLOR]
strText = Me.txtSearch.Value
strsearch = "Select* from Carlsbad where ((LineItemCode like ""*" & strText & "*"") or(FirstName like ""*" & strText & "*"")or(ServiceAddress like ""*" & strText & "*"") or(Location like ""*" & strText & "*"") or (LastName like ""*" & strText & "*""))"
Me.RecordSource = strsearch
End Sub

 

[Beetle]

Sorry, the slightly different answers above might just confuse the issue for you, do what MarkK suggested.

 

[KerryDee]

Thanks MarkK and Beetle for your help. It works perfectly. I am also going to start a new thread on figuring out how to attach VBA to a Ribbon. Thanks again for your tremendous help.