Help with Order form design (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:30
Joined
Feb 19, 2002
Messages
42,973
In addition to the other advice:

Every table should have a single primary key and that should probably be an autonumber. If you need to enforce business rules such as making the combination of VendorID + PartID unique, do that by using compound unique indexes. You will need to make multi-column indexes using the indexes dialog. You CANNOT make them by using the indexed property on a field.

Once you have a single unique iD for the combination of PartID + VendorID, you will be able to use a combo box to select an item. Comboboxes do not work when the table they are based on has a multi-column primary key. I would sort the combo by PartNumber and then VendorName since the user will know the part number but might not know the vendor. That will filter the list so you can pick what you want.
 

age0004

Registered User.
Local time
Today, 14:30
Joined
Feb 19, 2019
Messages
37
In addition to the other advice:

Every table should have a single primary key and that should probably be an autonumber. If you need to enforce business rules such as making the combination of VendorID + PartID unique, do that by using compound unique indexes. You will need to make multi-column indexes using the indexes dialog. You CANNOT make them by using the indexed property on a field.

Once you have a single unique iD for the combination of PartID + VendorID, you will be able to use a combo box to select an item. Comboboxes do not work when the table they are based on has a multi-column primary key. I would sort the combo by PartNumber and then VendorName since the user will know the part number but might not know the vendor. That will filter the list so you can pick what you want.


Hi Pat!
I just now saw what you said. I actually did just this and set it up with the combo box this way and it works great. Right now all I have though is a table that has part descriptions, part numbers, vendors, prices, etc all in the same table. So if there is a part description that has 3 different vendors/partnumbers/prices, then there are three different records all with the same part description.... the problem comes now that I am trying to create a form so that a user can add in additional vendors to an already existing part description... Im not sure how to go about doing this so that the part description is not changed. (This works when there are two separate tables, because I do this currently with my customers set up).... but I'm not sure how to do this while its all in the same table... (I assumed through a query, but I havent been able to make it work quite yet)

Any ideas??
Thanks so much for your response!
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:30
Joined
Feb 19, 2002
Messages
42,973
You need to break this into at least two tables. The parent table will be YOUR part number and description and whatever else is static regardless of the vendor. The child table contains the vendorID and whatever changes based on which vendor supplies the part. This might be price or the vendor's PartNumber (which you need to use when ordering additional stock), etc.

The vendor specific data would be in a subform.
 

Users who are viewing this thread

Top Bottom