Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2011
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Access 2010- Display message when no matching records

    How can I display a message instead of a blank form when there is an input to a text box followed by a button click requesting certain records but no records match the input

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    There are a couple of approaches you can take.
    You can use the dcount function to count the number of records, and display a message if Dcount is 0, and open the form if the count is >0
    Something like this, but it depends a bit on just how your current code works.
    Code:
    Dim strQuery as string
    Dim strdocname as string
    strquery = "qryName"
    strDocname = "frmName"
    
    If dcount("*", strQuery) >0
    docmd.openform  strDocname
    else
    msgbox "No records match the criteria.",vbInformation
    end if
    An alternative is to use the OnOpen event of the form.
    Code:
        If Me.RecordsetClone.RecordCount = 0 Then
            MsgBox "No records meet the criteria", vbInformation
            Cancel = True
        End If
    But this will generate an error message from the code that opens the form. This can be removed by having an error handler, error 2501

    Code:
    if err.number = 2501 then
    ' do nothing
    else
    msgbox err.description
    end if
    Regards
    John



Posting Permissions

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