Filter Datasheet Split Form (1 Viewer)

Alhakeem1977 · Oct 27, 2019

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 · Oct 27, 2019

Try

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

and should that be Category?

theDBguy · Oct 27, 2019

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 · Oct 27, 2019

pbaldy said:
Try

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

and should that be Category?

Thanks for your prompt response, I will try it once I reach the office.

Alhakeem1977 · Oct 27, 2019

theDBguy said:
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 · Oct 27, 2019

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

isladogs · Oct 27, 2019

Consider using an emulated split form which has similar functionality but is easier to work with.
See https://www.access-programmers.co.uk/forums/showthread.php?t=294421

Alhakeem1977 · Oct 27, 2019

arnelgp said:

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?

Filter Datasheet Split Form (1 Viewer)

Alhakeem1977

Registered User.

pbaldy

Wino Moderator

theDBguy

I’m here to help

Alhakeem1977

Registered User.

Alhakeem1977

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

isladogs

MVP / VIP

Alhakeem1977

Registered User.

Similar threads

Users who are viewing this thread