Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Question Unanswered: Search Not Finding Matching Record

    I have a search screen, but if no matching record is found, I haven't found out how to show a message box stating it wasn't found. I am missing something?

    I do have this line, but it doesn't work:

    If Search_Results.Form.RecordsetClone.NoMatch = True Then MsgBox "No Records Found"


    Private Sub Search_Click()
    On Error GoTo Err_Search_Click

    Dim strSql As String
    Dim strWhere As String
    Me.Search_Results.Visible = True

    strSql = "Select * from FORMS where "

    If IsNull(Me.SCounty) = False Then
    strWhere = "COUNTY = " & "'" & Me.SCounty & "'"
    End If

    If IsNull(Me.SEvent) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "EVENT_TYPE = " & "'" & Me.SEvent & "'"
    End If

    If IsNull(Me.SMAILED) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "DATE_MAILED = " & "#" & Me.SMAILED & "#"
    End If

    If IsNull(Me.SRECD) = False Then
    If strWhere <> "" Then strWhere = strWhere & " and "
    strWhere = strWhere & "DATE_RECD = " & "#" & Me.SRECD & "#"
    End If


    If strWhere <> "" Then
    strSql = strSql & strWhere
    'Debug.Print strSql
    'MsgBox strSql
    Me.Search_Results.Form.RecordSource = strSql
    Else:
    Me.Search_Results.Visible = False
    MsgBox "You Must Enter Some Search Criteria.", vbOKOnly, "Invalid Search"

    If Search_Results.Form.RecordsetClone.NoMatch = True Then MsgBox "No Records Found"

    End If
    Exit_Search_Click:
    Exit Sub

    Err_Search_Click:
    MsgBox Err.Description
    Resume Exit_Search_Click

    End Sub

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I see that you assign the query to the form's recordsource but where's the Requery? SQL statements don't just spontaneously run by themselves you know ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2006
    Posts
    110
    Im fairly new to this, learning as I go, Can you explain further?

  4. #4
    Join Date
    May 2005
    Posts
    150
    I think you are missing an 'END IF'

    If strWhere <> "" Then --->IF#1
    strSql = strSql & strWhere
    'Debug.Print strSql
    'MsgBox strSql
    Me.Search_Results.Form.RecordSource = strSql
    Else:
    Me.Search_Results.Visible = False
    MsgBox "You Must Enter Some Search Criteria.", vbOKOnly, "Invalid Search"

    --->PUT ANOTHER 'END IF' HERE

    If Search_Results.Form.RecordsetClone.NoMatch = True Then MsgBox "No Records Found" -->IF#2

    End If -->END IF#1

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Don't have time for step-by-step like last nite, but use DCount to see if there'll be a match. Something like:

    Code:
    SearchHit = dcount("YourSearchTerm","YourTableorQueryName")
       If SearchHit < 1 Then
         MsgBox "No Records Found"
      Else 'Do your search here
      End If
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by cmays637
    Im fairly new to this, learning as I go, Can you explain further?
    How about placing this in the Report(s) to be showing the data if available:
    This goes in the report's "OnNoData" on the EventTab. Give it a shot. Should just as easily work for a form as well.

    Code:
    Private Sub Report_NoData(Cancel As Integer)
    
        DoCmd.CancelEvent
        MsgBox "There are no tools checked out to report on.", vbOKOnly, "No data to report on."
        Cancel = True
            
         
    End Sub
    have a nice one,
    BUD

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Bud
    How about placing this in the Report(s) to be showing the data if available:
    This goes in the report's "OnNoData" on the EventTab. Give it a shot. Should just as easily work for a form as well.

    Code:
    Private Sub Report_NoData(Cancel As Integer)
    
        DoCmd.CancelEvent
        MsgBox "There are no tools checked out to report on.", vbOKOnly, "No data to report on."
        Cancel = True
            
         
    End Sub
    have a nice one,
    BUD
    Bud,

    If they have no idea as to where to put a Requery (which is needed) ... How do you expect them to know where (or even how) to invoke the NoData event of a report????
    Back to Access ... ADO is not the way to go for speed ...

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You have a very valid point, M Owen! We all have to be mindful of the skill level of posters, and this kid always readily admits to being a newbie, although unlike a lot of them he really does want to learn. My other question about Bud's post was that's there absolutlely no reference to any "report(s)" that I can find in the code or text posted by 637! And I fairly sure that there's no "OnNoData" option for forms!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    If his original question was just
    "Why doesn't this:
    If Search_Results.Form.RecordsetClone.NoMatch = True Then ...
    work?"

    Isn't the answer:
    If Search_Results.form.recordsetclone.recordcount=0 then ....
    Inspiration Through Fermentation

  10. #10
    Join Date
    Oct 2006
    Posts
    110

    Smile

    If Search_Results.Form.RecordsetClone.RecordCount = 0 Then MsgBox "No Records Found"

    Thanks guys.. this worked.. I was only off alittle.. and thanks Missinglinq for the kind words. I am learning and trying things as I go. I admit I have learned alot in about 3 months.. since I knew nothing about vba before I started this project. And thanks to everyone here who has helped me, I am now using the db at work as of this week.

    Chris

  11. #11
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by M Owen
    Bud,

    If they have no idea as to where to put a Requery (which is needed) ... How do you expect them to know where (or even how) to invoke the NoData event of a report????
    Hi Mike,

    Sorry but thought I had it made out simple, being as I am a very simple kinda guy too. That was placed inside the report that was being pulled automatically from the form. Being as that was the form to be pulled it didn't need the reference and actually works quite well when there is NoData to be pulled. My apologies if I tended to have made it not understood as I try my best to make it simple and easy as only I know how. Actually, I got that code a long while back right here on this forum to be exact. Hmmm, can't remember who though. I will make sure to do a better job in the future.

    Great New Year to you all,
    BUD

Posting Permissions

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