Want to match text with a related field and supplier an auto fill combo box

[moto485]

Hi, I have many suppliers on one a Suppliers table. I want to have a table with all the suppliers in the top row and the products in the coresponding coloumns below call it the Products table.



When someone fills out the PurchaseOrderForm they type in the product and in the combo box I would like that to auto fill if the exact same product is matched with in the Product table and if not they just select the the supplier from the combo box.

At the moment I just have the suppliers on a Suppliers table and they use the combo box to select the supplier on the PurchaseOrderForm. I am thinking of another table filled with products and suppliers.

I have attached a file to have a look at we have many more suppliers it is just for testing purposes. Hope I can be pointed in the right direction I am just learning. Thanks.

[hertfordkc]

I believe it has tables and forms covering your situation.

[rpeare]

What is your table All_PurchaseOrders supposed to represent? if it's items you ordered from a supplier you are going to have a TON of problems with this database.

First You have a SUPPLIERS table, but there's no unique identifier. You must have a unique identifier that is NOT related to the data in your table. Let's say Company A changes there name to Company AA any data in your subsequent tables will have a bunch of orphaned records if you change the name. If you store a unique identifier (autonumber is the easiest) instead you can change the vendor name as much as you want and all of your data will still be valid.

Second, you need a table that lists all products that you may possibly order, again, with a unique identifier.

Now, once you have those two set up you are going to want a purchase order table AND a purchase order detail table. The way you have it set up you can only order one item from a vendor per purchase order which does not happen in any business I've ever seen so you'd be unique. What you want to have is a method to say

Ok I'm going to order five products form supplier A and attach it to this purchase order number

So you'd need a purchase order table that has your basic information that is specific to the purchase order (purchase date, order date, pay date, Invoice Number, supplierID (autonumber FK to the supplier table)) this table must also have a unique identifier, again autonumber is the easiest.

Then your purchase order detail would carry the details of the purchase order

The item ID (autonumber FK from the items table) the quantity ordered, the price paid, etc. Again this table must have a unique key field (autonumber).

These are the four basic tables that you're going to need.

Lastly, you do not need to have a query for each individual vendor you can ask for an identifier when you run the query. You can create a form that has the supplierID and the supplier name in a combo box and we name the combo box SupplierID, make sure the supplierID (autonumber) field is the bound column. Let's call this form frmReportSetup

In the criteria of your query you'd have [forms]![frmReportSetup]![supplierID] in your criteria the query will adopt whichever vendor you've selected in your combo box.

[moto485]

Hi thank for the detailed reply. I will fix the suppliers table I didn't think of that.

With the purchase orders the invoice is just the customers order so when that particular item comes in we know that it has been ordered for that invoice. We do order many things in one order but I have grouped them all together so once we receive an invoice from a supplier item and qty should match and it is ticked of then disaperas if not it means we have not received that item from that supplier and we could chase it up.

We at the moment do have individual purchase orders on a piece of paper and when that purchase order comes in that sheet of paper should be ticked of and discarded.
A actual purchase order number though we dont have but it is food for thought.

I have though about having a combo box in the header with the suppliers and the spreadsheet below on one form but haven't figured that out yet not sure how to have a form and a datasheet view at once. I'll try get the auto fill of the supplier first I think.