Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If form filter produces no records, give message (A2k)

    I have a toggle button on one of my forms that sets the form's filter, alternating between Is Null and Is Not Null. What I want to do is trap the Is Not Null filter, so that when applied, if there are no records displayed, it displays a message box saying "No Records Found", and sets the filter back to Is Null. The shortened version of the code is below:

    <pre>Private Sub tglView_Click()
    If Me.tglView = True Then
    Forms![frms1]![sfrms1_awards].Form.FilterOn = True
    Forms![frms1]![sfrms1_awards].Form.Filter = ((Is Not Null filter here))
    Else
    Forms![frms1]![sfrms1_awards].Form.FilterOn = True
    Forms![frms1]![sfrms1_awards].Form.Filter = ((Is Null filter here))
    End If
    Me!lstView.Requery
    End Sub</pre>

    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If form filter produces no records, give message (A2k)

    You could test:

    If Me.Recordset.RecordCount = 0 Then
    ...
    End If

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If form filter produces no records, give message (A2k)

    Hans,
    Thank you. I should know this but don't....how would I use it, if I've already got an If statement in place?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If form filter produces no records, give message (A2k)

    You can nest If statements:

    Private Sub tglView_Click()
    If Me.tglView = True Then
    Forms![frms1]![sfrms1_awards].Form.FilterOn = True
    Forms![frms1]![sfrms1_awards].Form.Filter = ((Is Not Null filter here))
    If Me.Recordset.RecordCount = 0 Then
    Me.tglView = False
    tglView_Click
    End If
    Else
    Forms![frms1]![sfrms1_awards].Form.FilterOn = True
    Forms![frms1]![sfrms1_awards].Form.Filter = ((Is Null filter here))
    End If
    Me!lstView.Requery
    End Sub

    You could also test before applying the filter.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If form filter produces no records, give message (A2k)

    Thanks Hans, that works fine. I didn't know I was able to add an If statement within an If statement. I'm guessing it just has to close before the 'Else' statement of the parent If?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: If form filter produces no records, give message (A2k)

    Whenever you nest one "block" statement (If ... End If, For ... Next, Do ... Loop, Select ... End Select), you must nest a complete block, never a partial block.

    Good <img src=/S/smile.gif border=0 alt=smile width=15 height=15>:

    For i = 1 To 10
    If IsNull(Me.Controls("Text" & i)) Then
    MsgBox "You stupid fool!"
    End If
    Next i

    Bad <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>:

    For i = 1 To 10
    If IsNull(Me.Controls("Text" & i)) Then
    MsgBox "You stupid fool!"
    Next i
    End If

    Using indentation consistently helps - you will see quickly that corresponding elements aren't aligned properly.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If form filter produces no records, give message (A2k)

    To make this easier there is a free add in for VBA called Smart Indenter that you can download at http://www.oaltd.co.uk/Indenter/IndentPage.asp . It automatically indents all the code to make this easier to see.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If form filter produces no records, give message (A2k)

    Thanks for the tip <img src=/S/wink.gif border=0 alt=wink width=15 height=15> <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    I won't forget that...I just wasn't sure how to use it.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: If form filter produces no records, give message (A2k)

    Thanks for posting this. This seems to be a terrific Add-in.
    Regards
    John



  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: If form filter produces no records, give message (A2k)

    Since we're on the subject of addins - if you haven't discovered mzTools yet you might want to visit http://www.mztools.com. Their addin for VBA has a number of very useful features if you write any significant amount of VBA code.
    Wendell

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If form filter produces no records, give message (A2k)

    Can we (i.e. you) make a star post of addins for VBA?

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If form filter produces no records, give message (A2k)

    Here here. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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