Filter subform using combo box THEN search within subform after filter

[age0004]

This may sound confusing, however, I will try to make it as clear as possible.

I am creating an inventory database system. I have an inventory table made with all of the information we need about each part, including the "type" that it falls under. (the type being "inventory type") There are roughly 9 different "types" that we buy. To make things easier for the user I would like to have a form where the user can select the "type" of inventory by a combobox where then a subform filters and only shows the inventory falling under that category. I would then like them to be able to type in a search bar to search within those filtered results. There is a LOT of inventory so this will make tracking down a part easier for them.

Also, I would like to make it so that if there is nothing in the combo box it simply just shows ALL inventory. The user should be able to still search through the inventory unfiltered by the type.

I have seen many forums on how to do these two things separately, however, is there any way at all to do them both within the same subform??

I know how to set up a working search bar and how to set up a working combo box filter, however, I do not know where to start on how to make both of these things happen within the same subform.





Anything is appreciated. Thanks so much!!
Abby

 

[isladogs]

Hi Abby
Welcome to AWF
As a starting point, have a look at these examples of search forms at Allen Browne's website:
http://allenbrowne.com/ser-62.html , http://allenbrowne.com/AppFindAsUType.html

You may also find cascading comboboxes useful. If so, look at http://www.mendipdatasystems.co.uk/cascading-combo-boxes/4594455723

If none of those offer what you want, feel free to get back to us

 

[age0004]

Hey Colin,

Thanks for those links. The layout is exactly what I am looking for.

The only small issue with this is that I would like to do everything without a search or filter button.

I would like to have the combo box event to be "after update" and the search bar event to be "on change". That way nobody has to hit a button. I now have everything set up exactly the way I want it. Now, I am trying to modify his code to make this situation happen.

I set the "after update" event on the combo box to

Private Sub combotype_AfterUpdate()
Dim strWhere As String


If Not IsNull(Me.combotype) Then
strWhere = strWhere & "([Inventory Type] = " & Me.combotype & ")"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub



with the filter on the form being...
([Inventory Type]=False)

and the forms record source is set to my inventory table.

Of course this isn't working, so I am playing with it.


The search bar code I had at one point and need to find again, because his code doesn't allow for "search while you type" functionality. I'll try this before I ask.

Thanks

 

[isladogs]

Hi
The 2nd link is search while you type
Good luck