Design MS Access Set Up Subforms

The main form is frmAIRCRAFT. It has 4 subforms, frmENGINE_SF, frmPROPELLER_SF, frmLIFELIMITS_SF, and frmAIRCRAFT_AD_SF. The first 3 subforms, Engines, Propellers, and Lifelimts are one to many relationships. The parts have serial numbers, so each part can be assigned to only one aircraft. For example, an aircraft can have two engines, engine #1 serial #1 and engine #2 serial #2, but once these two engines are associated with an aircraft, they cannot be associated to any other aircraft. The same is for propellers and life limited parts. I guess you can set up the subform to not allow duplicates or something. The 4th AD subform is many to many. The AD's or Advisory Directives are many to many. Each aircraft can have several different AD's, and any one particular AD can be assigned to several different aircraft. I do not want to use the subforms to enter or change records, so I created a button in the top left of each subform that opens a data entry form. In the engine subform for example, the button opens the frmENGINE form. I want to use this form to edit engine records and assign engines to aircraft. You will notice in this form that I have a drop down box. I want this drop down box to have a list of the aircraft registration numbers. When I click on a registration number in the drop down, it assigns the current engine record with the aircraft I selected in the drop down box. Now when I close this form I should see this engine now listed in the engine subform for the current aircraft record. I also want this frmENGINE form to show ALL the engine records, not just the engine records that are associated with the current record in the main aircraft form. For example, I have the main form open and the current record is PLANE3. In the engine subform it is showing Model 004 with serial# 555. When I click on the button in the top left of the engine subform it opens the frmENGINE and it should be on the current engine record which is Model 004 serial#555, but the form is going to have all the engine records in it so I can navigate forward and back through all the engine records, its just when I open the frmENGINE from the engine subform it will just go to the current engine record that is associated with the current aircraft record. From there, I can use the dropdown box to change which aircraft its assigned to, I can add/delete records and navigate through all the engine records. I would like the same thing for all the other subforms as well. The AD data entry form will be different though. I don't know how you would set it up but whichever AD record I am currently viewing, it would have to show which aircraft this AD is assigned to as it can be assigned to many aircraft, and would need a way to choose the aircraft it is assigned to. Perhaps it can have a list box where I can control-click to choose the aircraft(s) by registration# it would be assigned to or it can show the aircraft registration numbers with check boxes next to each so I can assign that way. The database will be small, it will have a total of 20 to 30 aircraft at most. Only 4 aircraft are twin engines, each aircraft will only have about 4 to 5 AD's that apply to it and maybe 7-10 life limited parts. I'd like the macros converted to vba, except for the autoexec. If there is also a way to get the list boxes on the left of form frmAIRCRAFT to work better that would be great.I assume there is a better way to code it so it works smoother. This is all I need for now. More later. Database file attached.