Solved

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

Posted on 2004-08-03
6
2,634 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
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 119

Expert Comment

by:Rey Obrero
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now