Find code errors

[Guest]

I am fairly new to recordsets and have two different databases that I am
trying to get a search of tables to work. Having problem with both. I don't
know if it is kosher to ask for solution to two problems on one post or not.
Guess I will find out, huh.

The first problem occurs at the rst.open line of code & I don't know why.

Private Sub cmdFind_Click()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset

strCriteria = InputBox("Enter word to find:", "Search")

'opens recordset to support all cursor and record movements
rst.CursorType = adOpenDynamic

rst.Open "tblWords2 ORDER by tblWords2.Word;", CurrentProject.Connection

strCriteria = rst.Fields(0)
With rst
rst.MoveFirst
Do While Not .EOF
rst.Find ("s = '" & strCriteria & "'")
rst.MoveNext
If rst.EOF Then
rst.Close
Exit Sub
End If
Loop
End With

The second database gets its input from a listbox and then I want to search
the database and show all records that meet that criteria. The error occurs
at the rst.Find ("s = '" & strCriteria & "'") line of code.

Private Sub cmdFindAlbum_Click()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset

strCriteria = Me.lstTitle
rst.CursorType = adOpenDynamic 'opens recordset to support all cursor and
record movements
rst.Open "tblSongs ORDER BY tblSongs.Album;", CurrentProject.Connection
Debug.Print rst.State
Debug.Print Album
Dim s As String
s = rst.Fields(0)
With rst
rst.MoveFirst 'move to first rec
Do While Not .EOF
rst.Find ("s = '" & strCriteria & "'") 'locate correct rec
rst.MoveNext
If rst.EOF Then 'returns eof if no
match found
rst.Close
Exit Sub 'exit
End If 'else do this
Loop
End With

End Sub

Thank you all....

 

[Joseph R. Pottschmidt]

If I'm not mistaken Billy, You need to have Brackets [] around each
Field in the table that you are referencing in your Criteria Statement.

-----Original Message-----
From: Billy B [mailto:[email protected]]
Posted At: Wednesday, May 17, 2006 8:27 AM
Posted To: microsoft.public.access.macros
Conversation: Find code errors
Subject: Find code errors

I am fairly new to recordsets and have two different databases that I am

trying to get a search of tables to work. Having problem with both. I
don't
know if it is kosher to ask for solution to two problems on one post or
not.
Guess I will find out, huh.

The first problem occurs at the rst.open line of code & I don't know
why.

Private Sub cmdFind_Click()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset

strCriteria = InputBox("Enter word to find:", "Search")

'opens recordset to support all cursor and record movements
rst.CursorType = adOpenDynamic

rst.Open "tblWords2 ORDER by tblWords2.Word;", CurrentProject.Connection

strCriteria = rst.Fields(0)
With rst
rst.MoveFirst
Do While Not .EOF
rst.Find ("s = '" & strCriteria & "'")
rst.MoveNext
If rst.EOF Then
rst.Close
Exit Sub
End If
Loop
End With

The second database gets its input from a listbox and then I want to
search
the database and show all records that meet that criteria. The error
occurs
at the rst.Find ("s = '" & strCriteria & "'") line of code.

Private Sub cmdFindAlbum_Click()
Dim rst As ADODB.Recordset
Dim strCriteria As String
Set rst = New ADODB.Recordset

strCriteria = Me.lstTitle
rst.CursorType = adOpenDynamic 'opens recordset to support all cursor
and
record movements
rst.Open "tblSongs ORDER BY tblSongs.Album;", CurrentProject.Connection
Debug.Print rst.State
Debug.Print Album
Dim s As String
s = rst.Fields(0)
With rst
rst.MoveFirst 'move to first
rec
Do While Not .EOF
rst.Find ("s = '" & strCriteria & "'") 'locate correct
rec
rst.MoveNext
If rst.EOF Then 'returns eof if
no
match found
rst.Close
Exit Sub 'exit
End If 'else do this
Loop
End With

End Sub

Thank you all....