Filter Subform Combo Box using Master Combo Box

[nigelapt]

For every batch of product we manufacture I need to monitor time spent on each stage of production. I have setup 4 tables:

tblResources - details product BulkCode, Description, Batch Size

tblListOfStages - Each product has a set number of stages it must pass through. Table contains BulkCode (lookup to
tblResources), StageID, Stage Description and TargetTime

tblBatchDetails - records the details of each product made. Table contains BatchNo, BulkCode, Date of Man, Yield etc

tblStages - details the time spent on each stage. Table contains BulkCode, StageID (lookup to tblListofStages), ActualTime.

I have a form frmBatchDetails which records details of the batch. It also contains a subform frmSubFormStages into which the user must enter the actual time spent on each stage.

My problem is that when the user clicks on the Stage in the subform, all the stages are listed for all products. I only want to display the stages for the bulkcode defined in the tblListOfStages.

In the frmSubFormStages I also need to lookup the target time from the tblListofStages. When I do a DLOOKUP for target time based on the StageId in tblListofStages, it displays the same value for all stages

Any help would be gratefully received
Thanks

Nigel

 

[KenReay]

See FAQ on Combo boxes in this forum

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK