wildcard search: error in null values

[poppadoc75]

I have a form that uses a wildcard search, the results of the search are places in a subform. But when Input a word into the search that isn't in the table, the search comes up blank. Heres the code

Private Sub refresh_Click()
Dim x As String

If IsNull(Me.find) Then
MsgBox "Please enter search criteria.", , "Natural Catalog"
Me.find.SetFocus
Exit Sub
End If
x = "SELECT * FROM Program_Name WHERE Program_Name.Description LIKE "
x = findLibSQL + "'*" + Me.find + "*'"

Me.RecordSource = findLibSQL

If IsNull(x) = True Then
MsgBox "No records matching the criteria", vbExclamation, " Database -Library Search'"
Me.find.SetFocus

End If

End Sub

Please help me with the error, thank you

 

[johnwm]

You've set x to be a string:
x = "SELECT * FROM Program_Name WHERE Program_Name.Description LIKE "
x = findLibSQL + "'*" + Me.find + "*'"
You then test to see if it's NULL? - it won't be!

You haven't specified what program you're using, but the usual way of doing this would be to construct your query (using the concatenation operator '&' rather than the addition operator '+'), then open a recordset (using conn as your db connection)

Set myRST = conn.Execute(x)
If myRST.EOF Then
MsgBox "No records matching the criteria", vbExclamation, " Database -Library Search'"
Else
Set myControl.Recordsource = myRST
End If

You should also look at using a stored procedure (if you're using a SQL database) or a parameter query (in Access) to avoid the potential dangers of a SQL injection attack, See:

http://search.microsoft.com/search/...njection+attack"&View=msdn&st=b&c=4&s=1&swc=4

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk