Results 1 to 8 of 8
  1. #1
    matechik4 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6

    Applying Filter to Form by Defeault

    Hi,



    I have a table with work orders. I am creating a from so users can update the status of the work order. To make it easier to locate records, I created a button that filters the records so incomplete work orders are shown. Completion is determined by a field named Status that has the following choices: Submitted, Materials Ordered, In Progress, and Complete. There will rarely be an instance when somebody needs to update a work order that was already marked as complete. Is there anything I can add to my code so the filter is on by default and then the user can click the button if they wish to see all of the records? Thanks for the help.

    Private Sub btnfilter_Click()

    If Me.Form.FilterOn = True Then
    Me.Form.Filter = [Status] <> "Complete"
    Me.Form.FilterOn = False
    Me!btnfilter.Caption = "View Only Incomplete Work Orders"
    Else:
    Me.Form.Filter = "[Status]=No"
    Me.Form.FilterOn = True
    Me!btnfilter.Caption = "View all Work Orders"
    End If

    End Sub

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    On the Recordsource of the form, open the query design and add the fields and put criteria of <> "Complete". This way when the form is opened it will show all records not Complete. You can also have them right click inside the status field and they can choose other options for the status field to filter records or not. Or once form is up showing all records but Cosed, if you want to see Closed also, on OnClick on ALL Records button, update the RecordSource of the form accordingly

    Forms!MyForm.Recordsource = "Select * from MyTable Where Statsu <> Complete"
    Forms!MyForm.Requery

  3. #3
    matechik4 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6
    Bulzie,

    Thanks for taking the time to reply. I appreciate your time but perhaps I didn't explain myself well. My goal is to have a toggle button so end users can quickly switch between all records and those that are not complete. If I change the record source to <> "Complete" won't that only show the records that are not complete? I wouldn't be able to toggle back and forth. So far I have been able to successfully make the toggle button and it works, but the default status is the filter is off. I would like to make it so the filter is on by default. That way when the form is opened it shows all records that are not marked as complete but an end user with limited knowledge of access can just push the button to view all records. Any thoughts?

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I did not read the entire thread. But, I will offer this information.

    You can use VBA to manipulate the Filter property of a form. Something like
    Me.Filteron = false
    Me.Filter = "[TheField] =" & lngMyDeclaredVariable
    Me.Filteron = true

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    You do realize that you can turn a filter off by simply clicking on the Filter Icon at the bottom of the Form, right? It's right next to the 'Search' box in the native Navigation Bar.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I agree with you guys on the filter options but users usually need a straight forward way to do things, they will not know about some built in tools. I would suggest a combo box at the top of form that has "Open Orders" and "All Orders" and in the AfterUpdate of that combo field, set the RecordSource of the form.

    If me.cboSelect -= "Open Orders" then
    Forms!MyForm.Recordsource = "Select * from MyTable Where Status <> Complete"
    Else

    Forms!MyForm.Recordsource = "Select * from MyTable"
    End IF


    Forms!MyForm.Requery

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It is important for the developer to distinguish the Filter property from the Recordsource property. I prefer to manipulate the Recordsource property unless I am creating a tool that the user interacts with and the user understands the results are a filtered recordset. Otherwise, a User could unwittingly remove a Filter from a form and proceed to destroy the integrity of relations.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am creating a from so users can update the status of the work order. To make it easier to locate records, I created a button that filters the records so incomplete work orders are shown.
    Instead of just showing all records with Incompleted Status, is the process such that the user would have to also fill in or select a specific name or account? Seems there may be less opportunity for user to modify filter...

    If the user had to select the account for which status would change, then some unique identifier could be used to filter or reset the recordsource followed by a form requery.

    Just some thoughts for consideration.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Applying a filter in a form
    By 2410Davie in forum Forms
    Replies: 1
    Last Post: 03-07-2016, 03:35 PM
  2. Applying a filter to a report via a button
    By MowsMek in forum Reports
    Replies: 6
    Last Post: 03-16-2015, 01:04 AM
  3. Replies: 4
    Last Post: 04-22-2013, 06:45 AM
  4. Form empty by defeault
    By miicker in forum Forms
    Replies: 1
    Last Post: 02-26-2013, 10:04 AM
  5. Applying a filter to a combo box
    By bugchaser in forum Programming
    Replies: 1
    Last Post: 02-20-2009, 02:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums