Ok, here's the deal. I have a database that tracks books - I have a quite a lot of them and I get tired of buying duplicates because I can't remember what I have and I what I need. So viola, I whipped up a database.
I have a form, called frm_CheckBook that I use to check if a book already exists in the database. It takes two values, the Book title (txtBookTitle), and the ISBN number (if it has one)(txtBookISBN). It performs a search of tbl_BookData and if it finds something, displays the results in a listbox (lst_SearchResults).
If it doesn't find a match, it opens a new form where I can enter details about the supposedly new book. Fine and dandy - George Carlin not withstanding.
If I enter both the title and the ISBN number, the default behavior is to build the SQL query off the ISBN number, as this is the more accurate search. If I enter only the title, it should search off the title, and this is where the problem lies.
If the title already exists, nothing untoward happens - it all works like it should. If the title does not exist, I get the following error...
Runtime error 3075; SYNTAX ERROR (missing operator) in the query expression 'BookTitle LIKE '*[the book I'm searching for]*'
Now I could simply insert error handling to trap for this error and move on, but I'd rather not do that, if I can get away from it. Here's the code behind the button that does this...
'When the user clicks this button, the database must search for the title and/or
'Step 1 Check the title to make sure the first word isn't A, An, or The - if it
' is, ignore it.
'Step 2 Construct the search strings
'Step 3 Search for existing matches - if there are any display them in the results
' form - but activate the enter new button on that form
'Step 4 If there are no existing matches, open the new book entry form, and populate
' the corrected title, as well as the ISBN Number.
'Step 1 - Check the title for proper first words
Dim strTitleWords() As String
Dim strFinalTitle As String 'The actual title of the book
Dim strSearchTitle As String 'The partial title to use for searching
'Split up the title if one was entered and phrase the title properly
If (Not (IsNull(Me!txtBookTitle))) And (Not (Me!txtBookTitle = "")) Then
strTitleWords() = Split(Me!txtBookTitle, " ")
'Determine the first word
Select Case LCase(strTitleWords(0))
'We do not want out titles preceeded by A, An or The
strSearchTitle = Right(Me!txtBookTitle, (Len(Me!txtBookTitle) - 2))
strFinalTitle = strSearchTitle & ", A"
strSearchTitle = Right(Me!txtBookTitle, (Len(Me!txtBookTitle) - 3))
strFinalTitle = strSearchTitle & ", An"
strSearchTitle = Right(Me!txtBookTitle, (Len(Me!txtBookTitle) - 4))
strFinalTitle = strSearchTitle & ", The"
strSearchTitle = Me!txtBookTitle
strFinalTitle = strSearchTitle
'Step 2 - Construct the search strings
Dim strWhereClause As String
Dim strSQLStatement As String
Dim boolUseISBN As Boolean
'If the ISBN number is not listed, we will search only by title
If Me!txtBookISBN = "" Or IsNull(Me!txtBookISBN) Then
strWhereClause = "WHERE BookTitle LIKE '*" & strSearchTitle & "*'"
'Else, we will search by ISBN, since this is a more exact search
boolUseISBN = True
strWhereClause = "WHERE BookISBN = '" & Me!txtBookISBN & "'"
'Construct the rest of the search
strSQLStatement = "SELECT * FROM tbl_BookData " & strWhereClause
'Setp 3 - Do the search and test the resulting recordset
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim boolShowBookForm As Boolean
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQLStatement)
'Count the number of records in the record set
'If the BOF property is true, then there are no records, and we want to show the
'enter new book form.
boolShowBookForm = rs.BOF
btnAddACopy.Enabled = Not rs.BOF
btnDisplayBookData.Enabled = Not rs.BOF
btnAddBook.Enabled = Not rs.BOF
Set rs = Nothing
'Step 4 If there was no recordset, open the book entry form
If (boolShowBookForm) Then
Call AddNewBook(strFinalTitle, Me!txtBookISBN)
Else 'Populate the list box and have the user decide what to do.
Dim strSelect As String
Dim strFrom As String
Dim strWhere As String
Dim strOrder As String
Me!txtBookTitle = strFinalTitle
strSelect = "SELECT tbl_BookData.BookID, tbl_BookData.BookTitle, tbl_BookData.BookISBN, tbl_BookData.BookCopyrightYear, tbl_BookData.BookCopies "
strFrom = "FROM tbl_BookData "
If boolUseISBN Then
strWhere = "WHERE tbl_BookData.BookISBN = '" & Me!txtBookISBN & "'"
strWhere = "WHERE tbl_BookData.BookTitle LIKE '*" & strSearchTitle & "*' "
strOrder = "ORDER BY tbl_BookData.BookTitle"
strSQLStatement = strSelect & strFrom & strWhere & strOrder & ";"
Me.lst_SearchResults.RowSource = strSQLStatement
Any suggestions you could make would be appreciated.