How do you link a description text box to a Vendor list box

[Dkriezel]

I have a purchase order form that has a combo box(drop down) for a Vendor list to pick from, once you have selected a vendor I want the part numbers and description for that vendor only available in part# and description (drop down) box.


I have populated vendor tables with all available products specific to that vendor.

[SteveF]

Do I understand that you have separate vendor tables, each one only with products specific for that vendor?

What happens when you have to add a new vendor? This plan will get very messy and hard to maintain in very short order.

Better to have ONE parts table. Just add an extra column to that table to carry a Vendor ID number that matches the one from your Vendor list. Then filter the parts list to display only the parts that match the current vendor ID.

Just my $0.02
Steve

[Dkriezel]

I have an approved vendor table. I am only allowed to choose from this list (not to big) for certain products that are in contact with metal. I am the only one that can add vendors or products to this database.

I have made seperate product tables for each vendor.

This purchase order form has a vendor drop down combo box to select vendor, later down on the form you have the quantity ordered, qty recieved, Description , price etc.

I want to only be able to pick those specific products to that vendor in the description(combo box). This will keep people from ordering unapproved product from an un approved vendor.


I have to do it this way to be complient with Aerospace procedures..


Any ideas will be much apprieciated.


Dug.

[SteveF]

Please don’t take offense at this,but I think I need to turn on a big RED LIGHT with your table setup.


Let’s start back at the very beginning of things, and let me ask you two very fundamental questions:

1. Does the specification with which you must comply explicitly state that you must store each approved vendor’s approved parts list in its own separate table?
2. Or, instead, is the specification a performance requirement: you must only allow the user to select an approved vendor, and then only allow the user to select from the specific approved parts associated with that vendor?

If the answer to question 1 is Yes,then you have a big problem on your hands, because you’re being forced to design an application that is essentially unmanageable: You will need to totally rewrite the application every time you add anew vendor and their associated parts: all of your queries, all of your forms, all of your reports will need revision to even recognize the newly added resources.


If your specification is a performance requirement (number 2), then you can make life a lot easier by putting all of the approved parts into one table, with a vendor number assigned to each part that shows which vendor supplies it. If you design your tables this way, all you need to do to add a new vendor is add the vendor to the Vendors list, and their parts to the Parts list. Your queries, forms and reports will automatically recognize the new items without any need for re-design.


Queries, forms and reports are the items that, when properly designed, will shield your users from ever being able to select one of Vendor B’s parts after they’ve selected Vendor A: after the user has selected Vendor A, the only parts that will be visible to the user are Vendor A’s parts. If they haven't even selected a vendor, then no parts at all will be visible.


If this is what you want, and are willing to combine all your parts into one table, I and others on the board would be willing to assist and advise you on how to get the application to work the way it needs to. If you insist on maintaining separate parts tables for each vendor then I, at least, will bow out – I don’t see an easy, practical way to achieve a maintain-able product with this setup. It’s your call.

Steve

[Dkriezel]

#2 will work for me. So i am populating master product list, where do you assin a paticular vendor id number in that table.And how do we link this description combobox to both.

Doug.

Please don’t take offense at this,but I think I need to turn on a big RED LIGHT with your table setup.


Let’s start back at the very beginning of things, and let me ask you two very fundamental questions:

1. Does the specification with which you must comply explicitly state that you must store each approved vendor’s approved parts list in its own separate table?
2. Or, instead, is the specification a performance requirement: you must only allow the user to select an approved vendor, and then only allow the user to select from the specific approved parts associated with that vendor?

If the answer to question 1 is Yes,then you have a big problem on your hands, because you’re being forced to design an application that is essentially unmanageable: You will need to totally rewrite the application every time you add anew vendor and their associated parts: all of your queries, all of your forms, all of your reports will need revision to even recognize the newly added resources.


If your specification is a performance requirement (number 2), then you can make life a lot easier by putting all of the approved parts into one table, with a vendor number assigned to each part that shows which vendor supplies it. If you design your tables this way, all you need to do to add a new vendor is add the vendor to the Vendors list, and their parts to the Parts list. Your queries, forms and reports will automatically recognize the new items without any need for re-design.


Queries, forms and reports are the items that, when properly designed, will shield your users from ever being able to select one of Vendor B’s parts after they’ve selected Vendor A: after the user has selected Vendor A, the only parts that will be visible to the user are Vendor A’s parts. If they haven't even selected a vendor, then no parts at all will be visible.


If this is what you want, and are willing to combine all your parts into one table, I and others on the board would be willing to assist and advise you on how to get the application to work the way it needs to. If you insist on maintaining separate parts tables for each vendor then I, at least, will bow out – I don’t see an easy, practical way to achieve a maintain-able product with this setup. It’s your call.

Steve

[RayMilhon]

your product master table will have the following 2 keys

productid Primarykey
vendorid Foreignkey this is the link from product master to vendor master

[SteveF]

What Ray said.

To whatever data you need to hold for the parts, add an additional data column to hold the Vendor ID. This will be the same ID that you use in the Vendors table to uniquely identify each vendor, so it needs to be of the same basic data type as that corresponding field in the Vendors table. One heads-up: If your Vendor IDs are "auto-numbers", then make the VendorID column in the parts table a Long Integer; that's the underlying data type for Auto-Numbers.

Once you've done this, we'll talk about how to restrict things on your form.

Steve

[RayMilhon]

Thanks Steve, got a call from my boss as I was typing my previous message had to rush and left off the explanation. Planned to come back and add to it but you did that for me. thanks again