Solved

Run time error 3075 from SQL Statement is Record Set is Empty

Posted on 2004-08-03
6
2,647 Views
Last Modified: 2008-01-09
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
'ISBN number.
'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
        Case "a"
            strSearchTitle = Right(Me!txtBookTitle, (Len(Me!txtBookTitle) - 2))
            strFinalTitle = strSearchTitle & ", A"
        Case "an"
            strSearchTitle = Right(Me!txtBookTitle, (Len(Me!txtBookTitle) - 3))
            strFinalTitle = strSearchTitle & ", An"
        Case "the"
            strSearchTitle = Right(Me!txtBookTitle, (Len(Me!txtBookTitle) - 4))
            strFinalTitle = strSearchTitle & ", The"
        Case Else
            strSearchTitle = Me!txtBookTitle
            strFinalTitle = strSearchTitle
        End Select
    End If
'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
    'Else, we will search by ISBN, since this is a more exact search
        boolUseISBN = True
        strWhereClause = "WHERE BookISBN = '" & Me!txtBookISBN & "'"
    End If
   
    '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
   
    'Close everything
    rs.Close
    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 & "'"
    Else
        strWhere = "WHERE tbl_BookData.BookTitle LIKE '*" & strSearchTitle & "*' "
    End If
    strOrder = "ORDER BY tbl_BookData.BookTitle"
    strSQLStatement = strSelect & strFrom & strWhere & strOrder & ";"
   
    Me.lst_SearchResults.RowSource = strSQLStatement
    Me.lst_SearchResults.Requery
   
    End If
   
End Sub

Any suggestions you could make would be appreciated.
0
Comment
Question by:Jzaltheral
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 2

Author Comment

by:Jzaltheral
ID: 11710789
Oh, I should mention - this is the part that gives me the error:

'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)    <<<<<<<<<This is where the DEBUGGER points me
   
    '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
0
 
LVL 18

Accepted Solution

by:
Data-Man earned 125 total points
ID: 11710861
do a debug.print  and then check the sQL Statement..I'll bet you will find an error in the way it is built.  You can check the immidiate window for the SQL...Post it here if you have any questions.

   'Construct the rest of the search
    strSQLStatement = "SELECT * FROM tbl_BookData " & strWhereClause
    Debug.print strSQL Statement
    Stop

'Setp 3 - Do the search and test the resulting recordset


Mike
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11710913
try this

    If boolUseISBN Then
        strWhere = "WHERE tbl_BookData.BookISBN = '" & Me!txtBookISBN & "'"
   else
   if  IsNull(strSearchTitle ) then
     strWhere=""
     strOrder=""
    else
     strWhere = "WHERE tbl_BookData.BookTitle LIKE '*" & strSearchTitle & "*' "
    End If
  end if
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 32

Assisted Solution

by:jadedata
jadedata earned 125 total points
ID: 11711161
Data-Man is only missing one tiny bit of info.....
  after debug.printing the sql ,...
  paste it into a new blank querydef sql view and try to run it.
  the jet/rushmore engine will syntax check it and may place you on or near the source of the error in the sql

  Once you know the nature and location of the error, make the correction to your coded sql builder
0
 
LVL 2

Author Comment

by:Jzaltheral
ID: 11719190
Strangely enough...it works today exactly like I expect it to??

I am going to try the debug.print idea though just to see what happens...I will post the results of what I find here, hopefully later today.

0
 
LVL 2

Author Comment

by:Jzaltheral
ID: 11719647
Ok...nailed it.  I discovered both why it would not work yesterday and why it did work today...Here's the skinny.

When I was building my SQL statement I was using the traditional apostrophes - ' - to indicate my string values.  However, if the title I was searching on had an apostrophe in it, that would break the SQL because it assumed the mid-title apostrophe ended the value, and it didn't know what the rest of that stuff was supposed to be - thus the missing operator error.  The solution - JET allows you to use quote marks to indicate string values in SQL statements, in fact this is exactly how access builds it own queries...I tested in the query builder for a title figured out how it was building it, and viola.  I am posting the modified SQL construction code here.  Thanks (and points) to Data-Man and JadeData for the two halves needed to find this most excellent solution.

Revised code:
'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 " & Chr(34) & "*" & strSearchTitle & "*" & Chr(34)
    Else
    'Else, we will search by ISBN, since this is a more exact search
        boolUseISBN = True
        strWhereClause = "WHERE BookISBN = '" & Me!txtBookISBN & "'"
    End If
   
    'Construct the rest of the search
    strSQLStatement = "SELECT * FROM tbl_BookData " & strWhereClause


'Setp 3 - Do the search and test the resulting recordset



So far so good.  Now I'm getting an invalid use of Null error from another call, but that's expected since the ISBN number is Null.  I'll need to build a catch for that, but that's easy...I'll just make it an optional parameter on the function.

Thanks everyone.

Greg
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question