Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    72

    Unanswered: Using message box on form when query finds no results

    I developed a search form originally using Allen Browne's Search tips. I have changed it somewhat but it is still filtering the results to the same page. I've watched people using it and if they enter something that doesn't exist the blank form is not enough to indicate to them that a record doesn't exist, they are more likely to think that the db isn't working.
    My efforts at adding an Else statement and a MsgBox haven't quite worked and I'm wondering if anyone's got a tip. The only examples I can find are too different from my existing method to wrap my head around.

    Code:
    Private Sub Gobutton_Click()
    Me.Filter = "(False)"
    Me.FilterOn = True
    
    Dim strSql As String
    
    
    Const strcTail = "ORDER BY [Publish Date] DESC ;"
    
    strSql = "SELECT Library_table.ID, Library_table.Title, Library_table.Author, Library_table.Publisher, Library_table.[Publish Date], Library_table.Subject, Library_table.Summary, Library_table.[ISBN:], Library_table.[Document Description:], Library_table.Class_No FROM Library_table WHERE (1=1)"
    
    
    If Not IsNull(Me.freetext) Then
    strSql = strSql & " And(Library_table.Title Like '*" & Me.freetext & "*' Or Library_table.Author Like '*" & Me.freetext & "*' Or Library_table.Publisher Like '*" & Me.freetext & "*' Or Library_table.[Publish Date] Like '*" & Me.freetext & "*' Or Library_table.Subject Like '*" & Me.freetext & "*' Or Library_table.Summary Like '*" & Me.freetext & "*' Or Library_table.[ISBN:] Like '*" & Me.freetext & "*' Or Library_table.[Document Description:] Like '*" & Me.freetext & "*' Or Library_table.Class_No Like '*" & Me.freetext & "*')"
    
    End If
        
    strSql = strSql & strcTail
    
    'Assign the query string.
    Me.RecordSource = strSql
    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Open a recordset on strSql and test for EOF. If true, give the user a message box, otherwise reset the RecordSource.
    Paul

  3. #3
    Join Date
    Jul 2007
    Posts
    72
    "test for EOF"? From the rest of your post I'm thinking you mean TOF as in true or false. Is this right?
    Sorry if this sounds dumb but I'm not that familiar with record sets and there are too many acronyms in the world to assume anything

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no EOF = end of file its a property of the recordset that indicates if you are at the bottom of the recordset

    its a way of testing to see ifthe recordset has reached the end of the rows.

    strictly speaking you should test to see if eof and bof are true.. if so no records returned
    when iterating through a recordset testing for EOF will tell you when you have reached the end of the values.

    help files do have their uses y'now
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2007
    Posts
    72
    Ahh thanks healdem,
    My heads spinning with a mixture of Access and php mySQL at the moment and I needed something more to go on. Leaping into help files head first can be quite distressing you know

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •