Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2015
    Posts
    4

    Unanswered: Apply a filter via button to match records

    I have a form with two subforms (3 tables, total).

    Tables: PI (patient information)
    Referrals
    Reports

    The form displays PI at the top and then Referrals and Reports as subforms (see screenshot below)

    Click image for larger version. 

Name:	Kr1m1.jpg 
Views:	2 
Size:	57.2 KB 
ID:	16839

    In the current view, the form is displaying all referrals for patient #1 (1 referral available) and ALL reports regardless of whether it's tied to a referral or not. I'd like to create a button so that when clicked, it applies a filter to show ONLY reports that match the currently displayed ReferralID.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so set a filter. think of a filter as limit switch on what data is displayed

    filters apply to the current recordset (the data the form or report 'sees')

    say you wanted to limit data to patients whose referrer is szczupak

    Code:
    me.filter = 'referrer = "szczupak"'
    me.filteron = true
    in this case we are assumign that you havea column called referrer int he current table and its alphanumeric so the literal value of szczupak has to be delimited (numbers dont)


    to remove a filter
    Code:
    me.filteron = false
    but its always a smart call to actually clear the filter as well
    Code:
    me.filteron = false
    me.filter = NULL
    OK so that the basic idea
    'all' that you need to do now is implement it. this is where it can get tricky, some of that trickiness is down to the detail of the design you implement

    as a first pass
    you have a form called frmpatients and a form called frmreports
    what you coudl do ius each time you change a row in frmpatients automtically limit data in frmreports
    essentially its applying a filter to the frmreports form. its the sme as above, instead of referrign to me. (the current form) it allow you to set the the filter elsewhere

    Code:
    on error resume next ''use a crude error erro handler to effectivley ignore errors. not the smartest call but it will do for now
    forms!frmreports!filteron = vbfalse 'turn the filter off if its currently enabled
    forms!frmreports!filter = 'referrerid = ' & avalue ' set the filter to the value in a control or variable called avalue
    forms!frmreports!filteron = vbtrue 'tunf on the filter
    on error goto 0 'turn off the error handler
    ok so now you need a home for that fragment of code
    stuff it into a function (open a VBA code window)

    Code:
    option explicit
    public sub setReportsFilter(avalue as integer)
    on error resume next ''use a crude error erro handler to effectivley ignore errors. not the smartest call but it will do for now
    forms!frmreports!filteron = vbfalse 'turn the filter off if its currently enabled
    forms!frmreports!filter = 'referrerid = ' & avalue ' set the filter to the value in a control or variable called avalue
    forms!frmreports!filteron = vbtrue 'tunf on the filter
    on error goto 0 'turn off the error handler
    end sub
    now whenever you want to trigger that call the function setReportsFilter with the approriate value for the parameter avalue
    eg
    say your desired referralid is from a combo box called cmbrefid
    in the combo box's on click event
    setReportsFilter(cmbrefid.value)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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