Solved Not equal to filter in VBA function

[JamieRhysEdwards]

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:

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]

you may try:

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]

you may try:

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]

probably Moved your code to the AfterUpdate event of the checkbox, not the Click event.

 

[JamieRhysEdwards]

Hi @arnelgp, I've moved it to AfterUpdate() and it's having the same effect.

 

[Eugene-LS]

and it's having the same effect.

Please specify the format of the [AssetCondition] field
Is it exactly textual?

 

[JamieRhysEdwards]

@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]

@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]

Try

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

 

[JamieRhysEdwards]

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:

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