Parts and multiple vendors

[Countermeasure]

I’ve developed a bill of materials database for a client and it works well but I need to add on a facility for employees to requisition parts. Central to the database is a Parts table. Any of the parts can have more than one supplier. The user needs to create a new requisition, enter their own name, requisition date and identify the project that the requisition applies to. This is shown in the form frmRequisition. The subform is intended to allow the user to select from the list of the company part numbers and enter the number required. So far so good. What I can’t do is to enable the user to select the supplier of the part where there is more than one supplier and show that supplier and the supplier’s part description and price in the subform. (Ultimately the requisition form is the basis of a report that needs to be printed or the requisition data is to be exported to pre-printed order stationery.) Presumbly on entering the company’s own part number a drop down list of the available suppliers and their part numbers and descriptions is needed but I can’t see how this can be done.

Any suggestions?

 

[bhoran]

presumably your company partnumber is linked somehow to the available suppliers.

You need to create a query that uses the part number combo box on the form as the criteria, once you have that set up and you can get the correct data ion the query when you have selected a partnumber on the form. Create a new combo box using this new query as the source.

Then you need to add a requery command on the OnChange event of the part number combo box (original combo box). it will look something like Me.cmbSuppPartNum.requery - requery syntax can get a bit tricky but there are a few thread on this site that will help you there.

I hope this does the trick.