-
I do not understand SQL so i have turned to the InStr method of searching a field for text containing text that a user has entered. I need help because i created the loop and the program crashes every time due to my incorrect code structure. i know i have to use the following code:-
Private Sub cmdSearchBook_Click()
Dim SearchString As String
Dim result As String
SearchString = txtSearchBook.Text
Data1.Recordset.movefirst
Do
result = InStr(1, book, SearchString)
If result <> 0 Then Exit Do
Data1.Recordset.MoveNext
If Data1.Recordset.EOF Then Exit Do
Loop
End Sub
-
You don¡¯t have to use SQL. But there seem to be some problems with your code:
1. The function InStr() returns a variant (Long). So when you declare it you should use a code similar to Dim result (As Variant) or Dim result As Long. Even if it were a string, your comparison statement If result <> 0 Then Exit Do should have been If result <> ¡°0¡± Then Exit Do.
2. What does book in the InStr() function stand for? You mean the whole book? If so, I don¡¯t think you can search for a string in that way. If you want to search the whole book for a string, you have to search cell by cell through the book (and the sheets, if any). But in practice we often don¡¯t need to search all the cells. For example, a non-date string never appears in a date field. So we usually search a specific field (or fields) for a string. If so, you have to state the field. You can use a code similar to this:
Code:
--------------------------------------
¡®rst is a recordset.
¡®.Fields(0) is the first field in your book. And .Fields(1) the second, and so on.
With rst
.MoveFirst
Do Until .Eof
If .Fields(0) = SearchString Then Exit Do
.MoveNext
Loop
If .Eof Then MsgBox ¡°No Match!¡±
End With
--------------------------------------
-
You are right, the code snippet you posted will not work.
Rather than generating a recordset and then trying to loop thru it looking for a particular string, a very simple SQL call will make the whole thing a lot easier.
Assuming that you have a field called [Book] in your table.
To return the field [Book] in a table where [Book] is equal to your variable txtSearchBook.text.
Code:
Dim SQLStatement as string
Dim rcsTest as recordset
SQLStatement = "Select [Book] from [YourTable] where [Book] = '" & txtSearchBook.text & "'"
Set rcsTest = YourDB.OpenRecordset(SQLStatement)
with rcsTest
if (not .BOF) and (not .EOF) then .MoveFirst
Do while not .EOF
debug.print ![Book]
loop
.close
end with
You can also use DAO properties like FindFirst but the statement for a Findfirst operation is very similar to an SQL Statement. If you want to develop with DataBases then some knowledge of SQL is a must.
Cheers
Adrian
