Filter Datasheet Split Form (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 08:01
Joined
Jun 24, 2017
Messages
308
Hi,

How can I filter a split form with datasheet type depends on the department login to the application?
The department login ID is returned correctly but the filter is not working at all.
I think I missed something in my code.

Your kind assistance would be highly appreciated.
Code:
Private Sub Form_Load()
On Error Resume Next

Dim Department As String
 '''''' Department Privilege '''''''
 Department = DLookup("Department", "Employees", "[UserLogin] = '" & Environ("UserName") & "'")
 
    If Department < 3 Then
Me.AllowAdditions = True
Me.cmdNewCase.Enabled = True

  Else
Me.AllowAdditions = False
Me.cmdNewCase.Enabled = False

'Copmliance Dept
If Department = 4 Then Me.cboFilterFavorites.Value = 1
Me.Filter = "Status = 'Submitted'" And "Categoty='High Risk'"
Me.Filter = "Status = 'Re-Submitted'" And "Categoty='High Risk'"
Me.FilterOn = True
    Me.Requery
Me.Refresh

'Top Management
 If Department = 6 Then Me.cboFilterFavorites.Value = 4
Me.Filter = "Status = 'Submitted'" And "Categoty='High Risk'"
Me.Filter = "Status = 'Re-Submitted'" And "Categoty='High Risk'"
Me.Filter = "CDAction = 'Reviewed'"
Me.FilterOn = True
    Me.Requery
Me.Refresh

'Retail Ops
 If Department = 5 Then Me.cboFilterFavorites.Value = 6
Me.Filter = "CDAction = 'Reviewed'"
Me.Filter = "TMAction = 'Approved'"
Me.FilterOn = True
    Me.Requery
Me.Refresh
 
    End If

End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:01
Joined
Aug 30, 2003
Messages
35,709
Try

Me.Filter = "Status = 'Submitted' And Categoty='High Risk'"

and should that be Category?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:01
Joined
Oct 29, 2018
Messages
18,962
Hi. What does "filter is not working" mean? You shouldn't need to do Requery and Refresh at all. Also, you could use Select Case instead of If/Then.
 

Alhakeem1977

Registered User.
Local time
Today, 08:01
Joined
Jun 24, 2017
Messages
308
Hi. What does "filter is not working" mean? You shouldn't need to do Requery and Refresh at all. Also, you could use Select Case instead of If/Then.

Thank you for your reply, unfortunately, given my little knowledge of code, please help me to define my code as select case.


I am not insisting, if you have enough time to do so.

Thanks in advance!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:01
Joined
May 7, 2009
Messages
16,393
Code:
Private Sub Form_Load()
    On Error Resume Next

    Dim Department As Integer
    '''''' Department Privilege '''''''
    Department = Val(DLookup("Department", "Employees", "[UserLogin] = '" & Environ("UserName") & "'") & "")
 
    Select Case Department
        Case 6
            'Top Management
            Me.cboFilterFavorites.value = 4
            Me.Filter = "Status = '(Submitted'" And "Categoty='High Risk' Or " & _
                "Status = 'Re-Submitted'" And "Categoty='High Risk') And " & _
                "CDAction = 'Reviewed'"
            Me.FilterOn = True
            Me.Requery
            Me.Refresh
    
        Case 5
            'Retail Ops
            Me.cboFilterFavorites.value = 6
            Me.Filter = "CDAction = 'Reviewed' And TMAction = 'Approved'"
            Me.FilterOn = True
            Me.Requery
            Me.Refresh

        Case 4
            'Copmliance Dept
            Me.cboFilterFavorites.value = 1
            Me.Filter = "Status = 'Submitted'" And "Categoty='High Risk' Or " & _
                "Status = 'Re-Submitted'" And "Categoty='High Risk'"
            Me.FilterOn = True
            Me.Requery
            Me.Refresh

        Case Is > 1
            Me.AllowAdditions = True
            Me.cmdNewCase.Enabled = True
            Me.FilterOn = False

        Case Else
            Me.AllowAdditions = False
            Me.cmdNewCase.Enabled = False
            Me.Filter = False

    End Select

End Sub
 

Alhakeem1977

Registered User.
Local time
Today, 08:01
Joined
Jun 24, 2017
Messages
308
Code:
Private Sub Form_Load()
    On Error Resume Next

    Dim Department As Integer
    '''''' Department Privilege '''''''
    Department = Val(DLookup("Department", "Employees", "[UserLogin] = '" & Environ("UserName") & "'") & "")
 
    Select Case Department
        Case 6
            'Top Management
            Me.cboFilterFavorites.value = 4
            Me.Filter = "Status = '(Submitted'" And "Categoty='High Risk' Or " & _
                "Status = 'Re-Submitted'" And "Categoty='High Risk') And " & _
                "CDAction = 'Reviewed'"
            Me.FilterOn = True
            Me.Requery
            Me.Refresh
    
        Case 5
            'Retail Ops
            Me.cboFilterFavorites.value = 6
            Me.Filter = "CDAction = 'Reviewed' And TMAction = 'Approved'"
            Me.FilterOn = True
            Me.Requery
            Me.Refresh

        Case 4
            'Copmliance Dept
            Me.cboFilterFavorites.value = 1
            Me.Filter = "Status = 'Submitted'" And "Categoty='High Risk' Or " & _
                "Status = 'Re-Submitted'" And "Categoty='High Risk'"
            Me.FilterOn = True
            Me.Requery
            Me.Refresh

        Case Is > 1
            Me.AllowAdditions = True
            Me.cmdNewCase.Enabled = True
            Me.FilterOn = False

        Case Else
            Me.AllowAdditions = False
            Me.cmdNewCase.Enabled = False
            Me.Filter = False

    End Select

End Sub

Sorry for the delay, it does not filter anything!
May I missed something in the form?
 
Last edited:

Users who are viewing this thread

Top Bottom