Results 1 to 6 of 6
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    40

    After Update event to filter records based on cbo selection

    Hello,
    I have a database with a table (tblAssigned) that populates a form with record information. The form will be used to audit employee work. The fields are all populated based on the table except for a couple fields that are completed by the auditor. Once an audit is completed a text box (txtCompletedBy) is populated with the userid and another text box (txtCompletedDate) is populated with the date. All is this is working properly. These fields in my table are named "AuditCompletedBy" and "AuditCompletedDate"

    My issue is I would like to give the user the option to filter the records by "Pending Review" or "Completed" which is combo box selection so that the user does not have to search through completed records to find the next one they need to audit. The below code is not working properly though. When I select "Pending Review" it opens a New blank record instead of filtering out the completed records. And when I select "Completed" - nothing happens. The combo box is "cboStatus".

    Thank you in advance for taking the time to look over the code and helping me understand what I am missing!


    [code]Private Sub cboStatus_AfterUpdate()
    Dim MyFilter

    MyFilter = ""
    Me.Filter = ""
    Me.FilterOn = False
    MsgBox (Me.cboStatus)
    Select Case Me.cboStatus.Value

    Case "Pending Review"


    If MyFilter = "" Then
    MyFilter = "AuditCompletedBy = '' "
    Else
    MyFilter = MyFilter & " AND AuditCompletedBy = '' "
    End If

    Case "Completed"
    If MyFilter = "" Then
    MyFilter = "AuditCompletedDate Is Not Null"
    Else
    MyFilter = MyFilter & " AND AuditCompletedDate Is Not Null"
    End If
    Case Else

    End Select
    MsgBox (Me.cboStatus.Value)
    MsgBox (Me.txtCompletedBy.Value)

    If Len(MyFilter) > 0 Then
    Me.Filter = MyFilter
    Me.FilterOn = True
    Else
    Me.FilterOn = False
    End If

    Me.Refresh
    End Sub [code/]

  2. #2
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,979
    Please use code tags (highlight code and click the # button)
    I've redone it here to make it readable

    Code:
    Private Sub cboStatus_AfterUpdate()
    Dim MyFilter
    MyFilter = ""
    Me.Filter = ""
    Me.FilterOn = False
    MsgBox (Me.cboStatus)
    
    Select Case Me.cboStatus.Value
        Case "Pending Review"
            If MyFilter = "" Then
                MyFilter = "AuditCompletedBy = ''"
            Else
                MyFilter = MyFilter & " AND AuditCompletedBy = ''"
            End If
        Case "Completed"
            If MyFilter = "" Then
               MyFilter = "AuditCompletedDate Is Not Null"
           Else
               MyFilter = MyFilter & " AND AuditCompletedDate Is Not Null"
           End If
        Case Else
    End Select
    
    MsgBox (Me.cboStatus.Value)
    MsgBox (Me.txtCompletedBy.Value)
    
    If Len(MyFilter) > 0 Then
         Me.Filter = MyFilter
         Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
    
    Me.Refresh
    End Sub
    have you tried testing for null rather than a zls (zero length string)?

    Code:
    Case "Pending Review"
            If MyFilter = "" Then
                MyFilter = "AuditCompletedBy is null
    or

    Code:
    Case "Pending Review"
            If MyFilter = "" Then
                MyFilter = "nz(AuditCompletedBy,'') = ''"
    and I don't think it is opening a new blank record, I'm guessing your form is set to allow additions and what is happening is you are filtering out all the records because none of them have a zls

  3. #3
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    40
    Perfect! I used this and it worked! Thank you so much!
    Code:
    Case "Pending Review"
    If MyFilter = "" Then
    MyFilter = "AuditCompletedBy is null
    Can you tell me for this piece - When I select completed it shows me the completed audits but I can scroll through them right into audits that have not been completed. The record selector at the bottom still show 1 of 4561 instead of 1 of 2 (2 being the number of current completed audits)

    Code:
    Case "Completed"
        If MyFilter = "" Then
            MyFilter = "AuditCompletedDate Is Not Null"
        Else
            MyFilter = MyFilter & " AND AuditCompletedDate Is Not Null"
        End If
    Case Else

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,979
    you need to step through your code

    for example 'If MyFilter = "" Then' - myFilter is always "" because you have set it that way at the top of the sub, so there is little point in having the else part of the if clause

    and the code will not go through the first case statement

    dates are stored as numbers, so should be blank, but if you have stored it as text, it might have a zls

  5. #5
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    40
    Thank you Ajax! You have been incredibly helpful!

  6. #6
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,979
    just click the * bottom left of one of my posts

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

Similar Threads

  1. Replies: 2
    Last Post: 01-20-2017, 10:26 AM
  2. Filter table based on form selection
    By tylerpickering in forum Forms
    Replies: 2
    Last Post: 12-09-2014, 04:02 PM
  3. filter subform based on option selection
    By trevor40 in forum Forms
    Replies: 2
    Last Post: 03-06-2014, 07:07 PM
  4. Replies: 16
    Last Post: 06-02-2012, 06:11 AM
  5. Combo Box event based on selection
    By tobydobo in forum Access
    Replies: 52
    Last Post: 01-20-2012, 07:26 PM

Tags for this Thread

Posting Permissions

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