I have created separate tables and have created ref integrity links.
Excellent, although I think that there may still be some way to go.
As I said in my previous post, using a single table was deliberate. I have adjusted the concept and the relationships. Please take a look at the attached relationships jpg. Note that the table previously called REW_WRAP1_WRAP2 is now (hopefully correctly) called SheetMaster. Note also that there is a related table, tblCO_Detail to hold the CO_Details when the record relates to a 'REW'.
I've arranged the relationships in my jpg in the best waterfall style that I could. An explantion, however, may help. The way I see it: You have machine categories, for which you have machines and for which you record SheetMachine details. You have SheetMaster information, again for which you record SheetMachine details. From the outer right to the middle: You have Problem Categories, about which you record Problems. You also record the SheetDetail about problems. Finally, each SheetMachine may have multiple SheetDetails (and hence problems) recorded about it.
I think that the design I put forward would only have issues if you recorded different information (needed different fields) about REW, WRAP1 and WRAP2 records (processes). If the machines (or other details) change dependant on the process to which the record is related then I would have a query for the Row Source for the combo which is used to select the machine, and ensure that it is filtered for the appropriate process.
The attached 'query' jpg shows how you could construct the query to use at the Record Source for the table into which you enter the REW details. Note that although the join from tblCO_Details to SheetMachine is shown in the Relationships editor as 1:many in reality you would only have an entry in tblCO_Detail when storing a REW record, and each REW record would only have one entry. Note also, that because the CO Details are recorded in a seperate table, a record will only be created if/when the user enters data in the CO_FROM or CO_TO product fields.
I've added a new field to the SheetMaster table, 'REW_WRAP_Indicator'. You should use this to store the fact about which process the record is related to. You could have this chosen by the user, perhaps from a drop-down, or you could have it entered automatically. To do it automatically you could place the data field on each of your REW, WRAP1 and WRAP2 forms, but hidden, and set the default value for the control according to the use of the form. Personally, if I didn't use a drop down (to select the REW/WRAP1/WRAP2 process), I would use the same form for both WRAP1 and WRAP2 and set the default value according to the button press, when I make the form visible, this would mean one less form to worry about. The Record Source for both/all three forms would be pretty much the same, as noted above, only without the join to the CO_Detail table for the two WRAP forms.
However, currently I see a problem. When I click on each of the 3 buttons (category buttons) a new record is generated. How can I limit the amount of new records for Rew, Wrap1 and Wrap2 to simply a single record each? I have made the Detail_ID an Autonumber field for Rew, Wrap1 and Wrap2, therefore it will generate a new ID and thus a new record each time I click on the buttons.
You don't need to have your buttons create a new record. Access will automatically create a new record as soon as some data is entered into any of the controls for a new record (or selected from a drop-down). This will not be saved until the form is closed, the user navigates away from the record or a manual 'save' action is instigated.
If you do not want the user to see previously entered detail then set the Data Entry property (Forms Properties\Data tab) to YES and the user will only be able to enter new records on the form. If you only want one record displayed at a time change the Default View from Continuous Forms to Single Form.
If there will only ever be one SheetMachine record for any combination of the pair 'Sheet Master' and 'Machine' you should consider setting SHEET_ID and MACHINE_ID, in SheetMachine, to be the Primary Key (as the pair), thus preventing duplicates.
Please see the attached images.
With regards to these, the first two images, about 90kb, seem to be almost identical in detail, did another picture go AWOL?
My apologies for the delay in getting back to you, been rather busy old chap.
I hope that you find this post useful. Please let us know how you get on.
Tim