Help on linking two tables vertically and horizontally

[ristoner]

I have Multiple tables. For the sake of simplifying things there is one with Specs, one with Materials, and on with Part Numbers. There can be multiple specs for one part number and multiple materials for one part number. Is there any way that I can have all three tables have a primary key without using auto numbers. I want the primary key to be the spec number, the material number, and the part number. I want to create multiple, non designated fields in the part number table and have VBA search through each field and find the matching ones in the specs and materials tables, then spit those numbers out onto forms sorted by part numbers. Right now I have been just had multiple part numbers (one for each spec, and one for each material), but I would love to avoid this because if a part number changes I want the change to carry over to the other two tables. I have tried loops, recordsets, and a lot of web browsing and no one seems to have the same problem as me. Please help if this is at all possible.
Rick

 

[JBurlison]

you use the Relationship manager to link the "Part number" field on all 3 tables, make a form for each of the tables so you can add/edit the information. On the material and specs forms make a dropdown that is bound to the part number field on its respected table, then lookup the information that is in the dropdown by the "part Number field" on the part number table.
on the part number Form put 2 subforms in with the tables from your Specs and materials. and thats all she wrote you now have combined all 3 into one form. VBA is not required for this. if you want post the database and i can help you out with this.

 

[ristoner]

Thank you for the quick response. The only problem is that some part numbers use the same specs and material and I don't want to duplicate them by part number either. I would load the database if I knew how. Do I have to download a plug-in or something? Let me know, I'm still new at this, lol. Thanks for the help.

 

[JBurlison]

There is a paperclip button that you can use to put attachments on your post Click on go advanced and click the paperclip. It will have to be in zip format, just right click on the file ->send to -> zip file.

As for your part numbers and specs, not relate them by serial number if your doing it this way, have dropdowns on the serial number from with the lookup values of the material names and specs. then have text boxes that are linked to the Material, spec table so when you select the material the info will auto populate into the txt boxes.

you dont have to save all of the details of the material and specs on your serial number table just what spec they are and material they use, then have that information populated from there respected tables automatically.

 

[ristoner]

I tried to upload my database and it is to big even when zipped.

As far as the P/N form, there can be 50 to 100 specs or materials so what I did was create a button that opens a form just for materials filtered by P/N so I only get one P/N at a time. When I put in drop down menus for the Specs and Materials it wouldn't fill in the information automatically and all the drop down menus displayed the same number. I am using a Column based form which could be the problem. I think the only way to make this work is to have the Specs and material forms fill in the Spec and material numbers automatically by looking at each field in the P/N table and if that field value matches a record in the Specs or material table then fill in the respective number then let it auto update the rest of the information. I already have it so when you type in the spec or material numbers on the spec/material forms it fills in the rev and description. But I still have the same problem that if there is more than one spec/material for the same P/N, I have to have multiple listings of the same P/N. I figured if I could have one P/N listing and then list off the specs and material in different fields across then it would make my life easier if something changed in any of the tables. It is similar to the V/Hlookup functions in Excel. Thank you for the help so far.