Run time error 2580: what does it mean?

[peggy]

I haven't done a lot of programming in access in a few years and I need to develop a small publications database using Access 2016. Part of the database is an author search form using a subform that is linked to a query. I have done the programming that should do the search and need to understand how to fix the run time error message that I am receiving when I try to do a search. The error message reads: run time error 2580-- the record source "Select tbl_CYFN_Library.[Author_Creator], tbl_CYFN_Library.[Title_Subtitle], ... , specified on this form or report does not exist ...". When I hit debug the the code indicates that the record source doesn't exist...

Option Compare Database
Option Explicit


Private Sub btnSearch_Click()
Dim SQL As String


SQL = "SELECT tbl_CYFN_Library.[Author_Creator], tbl_CYFN_Library.[Title_Subtitle], tbl_CYFN_Library.[Month_Date_Year], tbl_CYFN_Library.[Box_location]" & _
"FROM tbl_CYFN_Library " & _
"WHERE [Author_Creator] LIKE '*' AND Me.txtKeywords & '*' " & _
"ORDER BY tbl_CYFN_Library.[Author_Creator] "

Me.subauthorlist.Form.RecordSource = SQL


Me.subauthorlist.Form.Requery

End Sub


'Code below copied from the subform query "sfrmauthorlist", child was named "subauthorlist", main form was named "frmauthor"
'SELECT tbl_CYFN_Library.Author_Creator, tbl_CYFN_Library.Title_Subtitle, tbl_CYFN_Library.Month_Date_Year, tbl_CYFN_Library.Box_location
'FROM tbl_CYFN_Library
'ORDER BY tbl_CYFN_Library.Author_Creator

I have checked the names indicated above and they are correct to the best of my knowledge. Can anyone look at the code and have any ideas that I can try? I am going cross eyed over this. Thanks in advance

Peggy

[ranman256]

stop using code and use queries.
if the query is assigned to the form you wont get VB errors.

[June7]

Why code setting RecordSource? Set unfiltered RecordSource in form design then use code to apply filter. Review http://allenbrowne.com/ser-62.html

Errors in concatenation. Need a space: Box_location] " & _

And WHERE clause should be:

"WHERE [Author_Creator] LIKE '*" & Me.txtKeywords & "*' "

[CJ_London]

strongly recommend if you are going this route to add a debug.print SQL line before assigning the code - you can then copy and paste this from the immediate window into a new query to see if it runs OK. ONce happy you have resolved all the bugs, you can comment it out or delete it

Bit concerned about your control Me.txtKeywords - the name implies multiple values, in which case your WHERE clause won't work even after June's fix

[Micron]

Also, there is no comparison operator for the AND condition (the right side is not equal to, less than, greater than, LIKE anything, etc.). Or is that what was meant by multiple values? Not sure, because multiple or single value, the AND portion needs some sort of comparison operator to some value or field.

From what I've read on the error, a sql that can't be evaluated will give this somewhat misleading error message.