Trying to use a combo box to apply a filter (and remove)

[ScubaBart]

Slowly working through the coding and have come up with an issue I thought would be a bit easier than it turns out to be.



Still on the Apiary / Hive / Inspection for Bees app and am working on the Log form for the Hives.

The default view is to list all the Hives in all the Apiaries and for someone with multiple Apiaries, this could obviously be cumbersome. So, I have created an unbound combo box that gets its list of items from a query, Q_Lookup_Apiary pulling from the T_Log_Apiary table. The reason for using a query is so I can filter out a fixed Apiary entry used to store In-Active hives.

I have this control set to show 2 columns for development so I can see the actual Apairy_Log_ID value next to the common name. In the final version, the bound column would be column 1 (the first column is 0, correct?).

The Drop-Down works fine and I have an even set up to execute On Change. For some reason, it simple isn't working. I've tried several types of syntax for the ApplyFilter command but it generally just keeps coming up with a pop-up asking for the value to filter by. For some reason, it won't pick up the value from the Combo-Box.

Here showing the form with the drop-down.



And here showing the code. The statement not working is the Else code.



I thought this would be the easy part. Next comes the harder part where I want to add another value in the Drop-Down list the reads something like "All Apiaries" that will trigger the first part of the If statement. And this value that is not in the table needs to be the default.

Have been researching that but it looks complicated for my skill level.

Anyway, even if I can get the first part of this addressed, at least that's a step. I can always put in another button to remove the filter though that's not optimal for aesthetics.

Any help is always appreciated.

I have uploaded the most current version of the file as well.

The best way to get to the Hive Log form is to use the F_Main_Overview form and select the 2nd option to open the Hive Log. Opening the form through the menu runs some code to set certain values in the Hive form.

[davegri]

The code should be for the After_Update event, not the On_Change event.
Examine the code below carefully for proper syntax and concatenation.

Option Compare Database
Option Explicit


Private Sub Combo_Filter_by_Apiary_AfterUpdate()
    If Me.Combo_Filter_by_Apiary.Column(1) = 0 Then  'Removes filter when 0 or Null is selected
        
        Me.Filter = 0
        DoCmd.Requery
        
    Else  'Applies filter to list only Hives for specified Apiary


        DoCmd.ApplyFilter , "Log_Apiary_ID =" & Combo_Filter_by_Apiary.Column(1)
        DoCmd.Requery
        
    End If


End Sub

[ScubaBart]

The code should be for the After_Update event, not the On_Change event.
Examine the code below carefully for proper syntax and concatenation.

Got it. Thanks. Still learning syntax.

Now on to adding the Null "All Apiaries" option.

[davegri]

Make these changes:

Private Sub Combo_Filter_by_Apiary_AfterUpdate()
    If Me.Combo_Filter_by_Apiary = "(ALL)" Then  'Removes filter when ALL is selected
        
        Me.FilterOn = False
        DoCmd.Requery
        
    Else  'Applies filter to list only Hives for specified Apiary


        DoCmd.ApplyFilter , "Apiary ='" & Combo_Filter_by_Apiary & "'"
        DoCmd.Requery
        
    End If


End Sub

[Welshgasman]

Just lost all my post AGAIN!!!


I would use the autonumber ID. I tended to use 0 for an ALL situation, using the UNION as shown.

[ScubaBart]

Maybe I'm missing something. How do I create the Union and maintain 2 columns ?

The first column is the friendly text while the second is the auto-number ID and yes, I want to use 0 for the All situation.

I can swap columns so the ID is in the first column and simple configure the combox format accordingly but either way, I don't know how to configure for two columns.

[davegri]

You don't want 2 columns, one will work perfectly well, as you would find if you tried my suggestion.

[davegri]

If you need the index value as well for some reason, you can implement the 2 column approach like this:

Option Compare Database
Option Explicit


Private Sub Combo_Filter_by_Apiary_AfterUpdate()
    If Me.Combo_Filter_by_Apiary = 0 Then  'Removes filter when 0 or Null is selected
        
        Me.FilterOn = False
        DoCmd.Requery
        
    Else  'Applies filter to list only Hives for specified Apiary


        DoCmd.ApplyFilter , "Log_Apiary_ID =" & Combo_Filter_by_Apiary
        DoCmd.Requery
        
    End If


End Sub

[ScubaBart]

If you need the index value as well for some reason, you can implement the 2 column approach like this:

AWESOME !!! Thanks. That works great !

Curious, in the Row Source query "Select Log_Apiary_ID, Apiary from Q_Lookup_Apiary Union Select 0, "(All) from q_Lookup_Apiary"

Is the q in the final string lower case for a reason or just incidental ?

Also, I found the parenthesis in that statement were not needed.

Edit: Also, The reason I want to use the Log_Apiary_ID as the defining characteristic is so I can carry the value easily to another form for sorting / filtering as needed.


Thanks Again !

[davegri]

AWESOME !!! Thanks. That works great !

Curious, in the Row Source query "Select Log_Apiary_ID, Apiary from Q_Lookup_Apiary Union Select 0, "(All) from q_Lookup_Apiary"
Is the q in the final string lower case for a reason or just incidental ?
Also, I found the parenthesis in that statement were not needed.
Thanks Again !

Upper, Lower - Access usually doesn't care.

The parens use was strictly for appearance.