I am looking to create a form that has the different parts listed for each machine. I have one table with the machine and its information. I think I need to make a new table that has the parts and part numbers. The form should have the details of the machine and the all the parts listed on it as well. Thanks for your help

Hi

Yes, you'll need a table for the parts to create a one to many relationship. Once you've done that you can create a form for the parts, then add that form as a subform to your Machines form. Access should automatically set up the Parent/Child links for you if you've created the relationship in the Relationships window and you should be good to go

How will access know which parts corresspond to which machine on the form. For example, on the parts table lets say one machine has 10 parts and the next one only has one, how do I have to make it so it does it automatically?

You store the PK (primary key - Autonumber datatype) from the machine table as a FK (foreign key -Number/ Long Integer datatype) in your parts table. For example

tblMachine
MachineID-->PK (Autonumber)
MachineName
.
.
.
etc

tblParts
PartID-->PK (Autonumber)
MachineFK-->Number (Long Integer) 'this is the machine ID from tblMachine
.
.
.
etc

You know have a one to many relationship between machines (one side) and parts (many side). Open up the Realtionships window and add your two tables, then drag the MachineID field onto the MachineFK field. Access will open up a little dialog window. Put a tick in Enforce Referential Integrity and you're done

Okay thanks, that is all set. So then how do I add many parts to the one machine so that it will come up correctly on the form. Lets say machine1(ID1) has 6 different parts, how do I put those parts in the field so that it is correctly identified on the form.

You use a form/subform set up as I suggested above (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

The main (Parent) form will be bound to tblMachines and the sub (Child) form will be bound to tblParts. Access will take care of the links for you

Okay yeah I thought I was a a little different. I guess not. My new dilemma is that the subform puts all the parts for all the machines instead of just the ones that correspond to that machine. My tables look like this

Table1

MachineID ( autonumber)
MachineName
Manufacturer
Serial No.

Table2
PartsID (Autonumber)
MachineID (number)
Part
Part No.

My relationship is as a one to many from table1 to table2, then ID frome table1 and MachineID table2
and i checked the enforce referential integrity

the problem is that on the subform all of the parts listed for any machine come up on each subform. How do I make it so only the corresponding parts come up.

You set the Parent/Child links between the main form and subform. Access normally does that for you if you add the subform either using the wizard or by dragging the form you want to use as the subform onto the main form.

To do it manually, first thing I'd recommend is to change the name of the MachineID field in Table2 to MachineFK (indicating it's a foreign key) and then open the main form in design view and select the subform control. In the Poperties window select the Data tab and click on the little button at the right hand end of the Link Child Fields property to open up the Subform Field Linker dialog window. Set the links in there to your MachineID for the masterand MAchineFK for the child links and you're done (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)