VBA Coding to filter a form based on a search Combo Box

[M3opa]

Hi all,

I am very new to access and just dipping my toes into VBA coding. I am trying to code a combo box to filter my form on the after update event. Based on my searches in forums and online, I currently have the following code though it is not working for me.

Private Sub OrderStatusSearchBar_AfterUpdate()

If IsNull(Me.OrderStatusSearchBar) Then
Me.Filter = False
Me.Filter = ""
Else
Me.Filter = "OrderStatus=" & Me.OrderStatusSearchBar
Me.Filter = True

End If

End Sub

When opening the form, I get no errors in my form however, all records are now showing as "#Name?" as per the below. Changing the drop down options my my search box does nothing at all.

Cannot stress enough just how new I am so any help at all is greatly appreciated.

Thanks,
M3opa

 

[bastanu]

Try Me.FilterOn=False to turn it off and Me.FilterOn=True to turn it on.
EDIT: here is your modified sub:

Private Sub OrderStatusSearchBar_AfterUpdate()

If IsNull(Me.OrderStatusSearchBar) Then
    Me.FilterOn = False
    Me.Filter = ""
Else
    Me.Filter = "OrderStatus='" & Me.OrderStatusSearchBar & "'" 'assumes order status is text; if number use your original line
    Me.FilterOn = True
End If

End Sub

Cheers,

 

[M3opa]

Thank you for your reply. I think we are almost there. I no longer get the "#Name?" all over my record boxes however, when using the search filter box, no records appear at all.

I have tried various tweaks of the code you gave above to no avail.

Could this be because of the " ' " line of code in the "Else" statement?

 

[cheekybuddha]

What is the RowSource and ControlSource of your combobox OrderStatusSearchBar?

 

[theDBguy]

Hi. Welcome to AWF!

I would also suggest assigning the filter to a variable, so you can examine what filter you're trying to apply to the form.

 

[bastanu]

Are you using lookup fields at the table level by any chance. If yes that might be the cause of the issue.

 

[M3opa]

@cheekybuddha, the control source box is empty. I tried changing this to the field "OrderStatus" and it would filter the entire form to "Order ID 1" and then any change of the search box would only change the status of Order ID 1 and not amend the filter.

@theDBguy, I am afraid I do not understand. My knowledge of Access only dates back by about 2 to 3 weeks at this point. Sorry!

@bastanu, the "OrderStatus" field in my original table "ItemsOrderedT" is picked from a drop down list of order statuses which exist in a separate table "OrderStatusT". I did it this was as videos online told me to stay away from the LookupWizard field option and I was unsure how else to make the options be from a drop down list. Hopefully that makes sense, though I would not be surprised to learn I have done it a back to front way somehow!

 

[theDBguy]

@theDBguy, I am afraid I do not understand. My knowledge of Access only dates back by about 2 to 3 weeks at this point. Sorry!

Here's an example:

Dim strFilter As String
strFilter = "OrderStatus=" & Me.OrderStatusSearchBar
MsgBox strFilter

Hope that helps...

 

[bastanu]

So both the unbound search combo and the bound one you have in the form only have one field - the Order Status (text)?

 

[M3opa]

So both the unbound search combo and the bound one you have in the form only have one field - the Order Status (text)?

I do not think so. The unbound combo box was created using the Wizard. It pulled through the the entire "OrderStatusT" and the hid the primary key field ("OrderStatusID") so I think that still counts as 2 fields in the unbound combo box though.

 

[bastanu]

That is your problem then...The combo returns the numeric ID and you want to use that to filter the textual value. You need to include the OrderStatusID field in the form record source and modify the Else clause to use that instead of the OrderStatus:

Me.Filter="OrderStatusID=" & Me.OrderStatusSearchBar

 

[cheekybuddha]

Apologies, I amended my post to ask for the Combo's RowSource as well.

Keep the ControlSource empty.

 

[M3opa]

So, I left it overnight as i was getting a little annoyed with it all. I have come back to it this morning, deleted my old ItemStatusF form and remade it after I removed the autonumber field from my table "OrderStatusT". This meant that both the combo box and the Form records now only had 1 field each.

Long story short, the code is now filtering correctly. @bastanu, if you're interested, it was your original code that was provided in your first post to this thread that worked in the end!

Thank you to all who have helped.

 

[bastanu]

Glad to hear you got it working! Usually it is better for the lookup tables such as your OrderStatus to have unique StatusID or Code and a description; this setup allows you to refine\change the description without having to run updates on the actual data table(s) to replace the old with the new (because the ID or code remains the same). But this might not apply to you. The trick to using combos with filters is to make sure that you are actually applying the right value based on what the bound column of the combo is (the bound column is usually hidden, having a width =0).

Good luck with your project!
Cheers,