Building a Update form based on multiple tables with filters

...and I win a prize for the longest title

Issue:

I am making a product price update form. My Database is mySQL Server, and I want to select products based on catagory and supplier.

My Products Table has productID, productname, CatagoryID, SupplierID, and price.
My Catagories Table has CatagoryID, CatagoryName
My Suppliers Table has SupplierID, CompanyName

I have almost 10000 Product records.

I have made it work with a single table for either Catagory or Supplier and pull up the proper products, but I want to filter the Products based on both Catagory and Supplier. I cannot see how to do this. I would gladly accept any recommendations.

Suggestion:

Ok this may be a bit of a 'band-aid' solution but here goes....

Start a form from scratch.

Drag in a Table Control [The grid at the right hand end of the Form Objects Toolbar].

A wizard will start which asks you to select the data source.

Point it towards your source and Product table.

Choose your Product table, then hit 'Next'

Pick the fields to display as Product_name and Price, then hit 'Create'

Drag in a listbox.

A wizard will start asking for a table.

Select the Suppliers table, then hit 'Next'

Choose the field to display as Supplier_name, then hit 'Next'

Chose the Supplier_ID field in the left hand pane, and the Supplier_ID in the right-hand pane, then hit 'Create'

In the table control, insert a new column, make it a list box.

Copy over the list box properties from the Supplier list box created in the previous step...[[so the Data tab for it will have the Data field as 'Supplier_ID', Bound field = 1, Type of list contents: 'SQL', List Contect: 'SELECT `Supplier_name`, `Supplier_ID` FROM `copy of test`.`suppliers`"]] NB your SQL will be different to mine due to the different datasource names.

Rename the new column in the Table control as 'Supplier'

Drag in another list box

A wizard will start asking for a table.

Select the Category table, then hit 'Next'

Choose the field to display as Categoty, then hit 'Next'

Chose the Categoryr_ID field in the left hand pane, and the Category_ID in the right-hand pane, then hit 'Create'

Copy over the list box properties from the Category list box created in the previous step...[[so the Data tab for it will have the Data field as 'Categoryr_ID', Bound field = 1, Type of list contents: 'SQL', List Contect: 'SELECT `Category`, `Category_ID` FROM `copy of test`.`category`"]] NB your SQL will be different to mine due to the different datasource names.

Rename the new column in the Table control as 'Category'

Delete both the initial list boxes dragged onto the form - not needed anymore [list boxes are now in the Table Control]

In the General properties tab forboth of the new columns, set 'Enabled...' to 'No'. This will prevent modification of the product / supplier / category relationships. Maybe also do the same for the Product_namel so that field can't be edited.
You may also like to change the property of the whole form to not allow additions.

Save everything and run the form.

At the bottom of the screen will be the filter controls - click on the control for 'Form based filers'. [funnel with the blue grid behind it]

Use the drop down list box for either the Supplier and / or Category to select the criteria

Then click on the 'Apply form-based filter' button. You can select either the Supplier and / or Category....

This leaves just the 'Cheese' products listed.

Use the 'Remove filter' to go back and remove the filter...

Once you have this working you can record some macros and put some buttons on the form to drive the filters.