Want to Create a Custom Table/Query Filter

[workguy]

Hello,
I am very new to access which is why I am posting this. I have a table with about 10 columns of general information based on a specific part number, with each column specifying the weight of part, part material, etc. I want to create a simple filter tool to be able to filter these parts by material. There are about 100 parts and 15 materials. I understand there is a filter tool, but I want something more intuitive that can be used in form view ideally. All I want is a drop-down box containing the various materials, which once selected will open a query/table containing all of the information of the parts that are made of only the selected material. Is this possible and how would I go about this?


I appreciate the help in advance.

[aytee111]

In the AfterUpdate event of the combobox to select the material, filter the form based on that selection. You can use a macro or VBA for this. This will refresh the page based on the record source of the form.

[workguy]

I understand I am going to sound dumb here, but I just used access for the first time two days ago and a simpler explanation would really help. Do I have to create a subform?

[aytee111]

First - make the record source for a form. This can either be a table or a query. What do you want your form to do, is it for data entry or only display?

Once you have the record source (containing all the fields for the form), create a form using the wizard based on this. Add a combobox to the form, in the row source for this, create a query which contains only the material. Then do post # 2.

Note - if you would like this database to do more than displaying data from one table, then you are putting the cart before the horse. You first have to design the table structure for the entire system. Post # 7 https://www.accessforums.net/showthread.php?t=65906 gives some good places to start.

[workguy]

What I want is the exact functionality as the filter button on the home tab when you are looking at a query in database view. I simply want the input to be a combobox in a form, that then generates a filtered database showing only the rows that meet the combobox criteria. I can setup a form that edits itself with a combobox, but I cannot set up a form with only a combobox that edits a different database. I start to not understand the instructions once the combobox is made. How to I make my form with a combobox input, to then create the filtered database? I understand I am a novice so helping me might be frustrating but I have appreciated the help so far. Thank You.

[aytee111]

Go to form properties, go to AfterUpdate, click the three dots for a macro, apply a filter - the field name from the record source = the combobox name. I don't use macros as they are cumbersome and limited, but the syntax will be displayed there.

If you do it in VBA (Event Procedure) it will be:
Me.Filter="field name from record source = '" & Me!comboboxname & "'"
Me.FilterOn=True