Solved Not equal to filter in VBA function (1 Viewer)

JamieRhysEdwards

New member
Local time
Today, 01:40
Joined
Mar 26, 2022
Messages
27
Hi All,

I have a check box labelled "Show Retired" which when ticked, I want it to show assets labelled "Retired" and when it's unchecked, to hide them. I cannot quite figure out how this is done however.

This is what I've done so far:

Code:
Private Sub chkShowRetired_Click()
    If chkShowRetired.Value = True Then
        Me.FilterOn = False
    Else
        Me.Filter = "[AssetCondition] <> 'Retired'"
        Me.FilterOn = True
    End If
End Sub

If I apply the filter manually by tapping the down arrow on the column and then deselecting Retired, it works as expected. If I then check Show Retired, it removes the filter as expected.

However, if I then disable (uncheck) Show Retired, I then get a 3464 error which states it's a Data type mismatch in criteria expression.

If I then tap debug, it shows Me.FilterOn = False is the culprit?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:40
Joined
May 7, 2009
Messages
19,169
you may try:
Code:
Private Sub chkShowRetired_Click()
    If chkShowRetired.Value = True Then
        Me.FilterOn = False
    Else
        Me.Filter = "Nz([AssetCondition], '@') <> 'Retired'"
        Me.FilterOn = True
    End If
End Sub
 

JamieRhysEdwards

New member
Local time
Today, 01:40
Joined
Mar 26, 2022
Messages
27
you may try:
Code:
Private Sub chkShowRetired_Click()
    If chkShowRetired.Value = True Then
        Me.FilterOn = False
    Else
        Me.Filter = "Nz([AssetCondition], '@') <> 'Retired'"
        Me.FilterOn = True
    End If
End Sub
This doesn't seem to do anything... It's fixed the mismatch error but nothing is actually applied :(
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:40
Joined
May 7, 2009
Messages
19,169
probably Moved your code to the AfterUpdate event of the checkbox, not the Click event.
 

JamieRhysEdwards

New member
Local time
Today, 01:40
Joined
Mar 26, 2022
Messages
27
@Eugene-LS Sorry for the very late reply. It's coming from a lookup table if this helps, so it can either be grabbed textually, or if preferred, an ID value of 4
 

GPGeorge

Grover Park George
Local time
Yesterday, 18:40
Joined
Nov 25, 2004
Messages
1,776
@Eugene-LS Sorry for the very late reply. It's coming from a lookup table if this helps, so it can either be grabbed textually, or if preferred, an ID value of 4
No, it's not an either or choice. It's going to be the ID, or Primary Key value, that matters.
 

Cronk

Registered User.
Local time
Today, 12:40
Joined
Jul 4, 2013
Messages
2,770
Try
Code:
Private Sub chkShowRetired_Click()
   If chkShowRetired.Value = True Then 
       Me.filter = "[AssetCondition] = 'Retired'"      ' <<<<Missing
       Me.FilterOn = True
  Else
      Me.Filter = "[AssetCondition] <> 'Retired'"
      Me.FilterOn = True
  End If
End Sub
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:40
Joined
Feb 19, 2002
Messages
42,981
If the AssetCondition is a table level lookup, you need to use the ID in this if statement. OR, you need to modify the RecordSource query to join to the lookup table to bring in the text value. Jamie did say it was a table level lookup.
 

JamieRhysEdwards

New member
Local time
Today, 01:40
Joined
Mar 26, 2022
Messages
27
Thanks for the support all! I think I've figured it out (might not be the most efficient way of doing it and I'll soon see). This is how I'm doing it at present:

Code:
Private Sub Form_Load()
    ...
    TempVars("AssetList_ShowRetired") = False
    ...
End Sub

Private Sub chkShowRetired_Click()
    If (Form!chkShowRetired) Then
        TempVars("AssetList_ShowRetired") = True
        ToggleFilters
    Else
        TempVars("AssetList_ShowRetired") = False
        ToggleFilters
    End If
End Sub

Private Function ToggleFilters()
    DoCmd.ShowAllRecords
    
    If (Not TempVars("AssetList_ShowRetired")) Then
        ' Filter out Retired assets if checkbox is cleared
        DoCmd.ApplyFilter "", Eval("""RetiredDate is null or RetiredDate > date()"""), ""
    End If
End Function
 

Users who are viewing this thread

Top Bottom