I'm using Access 2010 on a Win7 Pro box.
I have a search routine (event) on a form which resets the RecordSource based on the search string to show all records containing that string. This construct works well. However when the string isn't found, the form shows a blank record such as a one gets for a new input since the recordset is empty.
So I decided I want to capture the recordset if it's empty, and display a message to that effect instead of showing a blank record. I copied the SQL string and set a dimensioned recordset (recSet) equal to it. This code COMPILES, but when I try executing the event, I get a Type mismatch error which points to the entire Set recSet statement . (Me.txtTtl.Value is the string being searched for.) The following is a code snippet of the event:
Code:
Dim strFind as String
Dim recSet as Recordset
Dim db as Database
strFind = "[Title] Like '*" & Me.txtTtl.Value & "*'"
Set db = CurrentDB
Set recSet = db.OpenRecordset("SELECT Books_Read.*, Authors.AKA AS AKA1, Authors_1.AKA AS AKA2, Authors.[Last Name] " & _
"FROM Authors AS Authors_1 RIGHT JOIN (Books_Read LEFT JOIN Authors ON Books_Read.Author1 = Authors.AuthorIDX) ON Authors_1.AuthorIDX = Books_Read.Author2 " & _
" WHERE " & strFind & " ORDER BY Authors.[Last Name], Authors.[First Name], Books_Read.[Copyright Year], Books_Read.Title;", dbOpenDynaset, dbSeeChanges)
If recSet.EOF = True And recSet.BOF = True Then
MsgBox "No records found.", vbOKOnly, "No Records"
btnRefresh_Click
Exit Sub
End If
Me.RecordSource = "SELECT Books_Read.*, Authors.AKA AS AKA1, Authors_1.AKA AS AKA2, Authors.[Last Name] " & _
"FROM Authors AS Authors_1 RIGHT JOIN (Books_Read LEFT JOIN Authors ON Books_Read.Author1 = Authors.AuthorIDX) ON Authors_1.AuthorIDX = Books_Read.Author2 " & _
" WHERE " & strFind & " ORDER BY Authors.[Last Name], Authors.[First Name], Books_Read.[Copyright Year], Books_Read.Title;"
Note that if I remove all the Green code, the event compiles without error and works. The SQL statement for the Me.RecordSource is IDENTICAL to the SQL statement for the recordset (recSet) since I just copied it from the Me.RecordSource statement.
I then decided to approach this differently. I changed the code according to the following snippet and got a Type mismatch for the ampersand (&) in the SQL statement for the recSet recordset:
Code:
Dim strFind AS String
Dim recSet AS Recordset
strFind = "[Title] Like '*" & Me.txtTtl.Value & "*'"
Set recSet = ("SELECT Books_Read.*, Authors.AKA AS AKA1, Authors_1.AKA AS AKA2, Authors.[Last Name] " & _
"FROM Authors AS Authors_1 RIGHT JOIN (Books_Read LEFT JOIN Authors ON Books_Read.Author1 = Authors.AuthorIDX) ON Authors_1.AuthorIDX = Books_Read.Author2 " & _
" WHERE " & strFind & " ORDER BY Authors.[Last Name], Authors.[First Name], Books_Read.[Copyright Year], Books_Read.Title;"
If recSet.EOF = True And recSet.BOF = True Then
MsgBox "No records found.", vbOKOnly, "No Records"
btnRefresh_Click
Exit Sub
End If
Me.RecordSource = "SELECT Books_Read.*, Authors.AKA AS AKA1, Authors_1.AKA AS AKA2, Authors.[Last Name] " & _
"FROM Authors AS Authors_1 RIGHT JOIN (Books_Read LEFT JOIN Authors ON Books_Read.Author1 = Authors.AuthorIDX) ON Authors_1.AuthorIDX = Books_Read.Author2 " & _
" WHERE " & strFind & " ORDER BY Authors.[Last Name], Authors.[First Name], Books_Read.[Copyright Year], Books_Read.Title;"
DoCmd.Requery
This has got me head scratching.
Vic
PS I Decompiled and tried recompiling getting the same error for both versions.