Form help

[spalmateer]

I'm attempting to create a database with various product costs for all our vendors and their many products. I've created the following structure:

tblProducts- (one to many with tblProductCost)
ProductID (primarykey)
ProductName
ProductDesc
ect.

tblProductCost-(many to one with tblProducts & tblVendor)
ProductID (primarykey)
VendorID (primarykey)
Cost

tblVendor (one to many with tblProductCost)
VendorID (primarykey)
VendorName
ect.

This, as I'm sure your well aware, creates a many to many relationship between product and vendor. I have two questions:

1. On my product form I have a subform that shows the vendors and their product costs. I would like a textbox on the form that shows the lowest cost vendor name and cost (in the subform). This prevents the user from having to scroll through all the records in the subform to find out the lowest price vendor.

2. I have created a continuous form (with no datasource) that the user selects the product we need to order. Once the user selects the products (combobox) I want to be able to push a button and have the dbase create purchase orders based off the lowest vendor price. The structure for the PO is the same as N-winds (tblProducts-|-->tblPODetails<--|-tblPO<--|-tblVendor). Anyone have even a general idea how this can be done?? Thanks in advance for the help!
Scott

 

[RichMorrison]

You wrote
<<
1. On my product form I have a subform that shows the vendors and their product costs. I would like a textbox on the form that shows the lowest cost vendor name and cost (in the subform). This prevents the user from having to scroll through all the records in the subform to find out the lowest price vendor.

2. I have created a continuous form (with no datasource) that the user selects the product we need to order. Once the user selects the products (combobox) I want to be able to push a button and have the dbase create purchase orders based off the lowest vendor price. The structure for the PO is the same as N-winds (tblProducts-|-->tblPODetails<--|-tblPO<--|-tblVendor). Anyone have even a general idea how this can be done?? Thanks in advance for the help!
>>

For 1, I would sort the subform by Cost in descending order. That will put the lowest-cost vendor at the top.

For 2, I would add a command button to the Vendor subform that says "Place Order". The command button would be on each line. The code behind the command button would be able to find the Product and the vendor. Then open a pop-up form with the selected Product and Vendor and let the user enter quantity and other detail.

RichM