HELP - I'm Stuck Linking Records on Forms

[fraser_lindsay]

I posted earlier today and despite all of my best efforst I have made very little progress. I keep hitting my head on the desk at the same point.

I am trying to get one tool entry to link to many records for each tool.

The form [frmVibrationResults] holds tool info, PK is [serial number/reference] as a text field.

The subform on this form called [frmVibrationMagnitude] holds test data for each tool. I want to be able to add a record for each tool for each test. At the moment it only lets me add one per tool. If I try and add more than one record for a tool I get an error message telling that I am duplicating information in the PK, index or relationship.

I have tried all day to solve this and can't proceeed without it working. Can anyone help. I've attached the zipped version of the DB.



Thanks

 

[FoFa]

Sounds like an INDEX problem on your table the [frmVibrationMagnitude] subform writes too.

 

[ScottGem]

Did you set the reference number as a Primary key in the other table? That would explain your problem. The reference number should be a FOREIGN key not a PK.

 

[bomberchia]

PK =X= RK

Use primary key for number (auto generated),
reference number cannot be set as auto number.

 

[fraser_lindsay]

Thanks, I'll give that a go just now.

 

[phoenicks7]

This is probably really elementary question..... but, you ARE using a subform for the "Test Results", right?

The "Tool Results" form listing the tool specs or info, and then a subform listing the individual tests (one tool to many tests)?

If this is the case, then it should be a simple matter of linking the PK of the Tool table, to the ToolID key in the Tests table.

 

[fraser_lindsay]

I still can't get this to work. I have tried the following:

1. Add an autonumber ID field as PK to an intermediate table [TestDetails]
2. Add an autonumber ID field as PK to table [VibrationMagnitude]

Linked table [GeneralToolInfo] to [TestDetails] PK [Serial Number/Reference] to FK [Serial Number/Reference] which comes out a as one-to-many

Linked table [TestDetails] [Serial Number/Reference] to table [VibrationMagnitude] PK [ID], which comes out as one-to-many.

I have modfied my query and forms and it almsot looks like I'm there. i can switch between tool records and the subform changes the test data. i can add test data to a tool record. However, any test data I add seems to be added to all tools and isn't linked to a specific tool, which is what I want.

My brain hurts.

 

[fraser_lindsay]

This is probably really elementary question..... but, you ARE using a subform for the "Test Results", right?

The "Tool Results" form listing the tool specs or info, and then a subform listing the individual tests (one tool to many tests)?

If this is the case, then it should be a simple matter of linking the PK of the Tool table, to the ToolID key in the Tests table.

Hello,

Yes, it is a subform and yes you would think it would be a simple question of linking one tool to many tests but I can't seem to do it. It will almost certainly because I am a pure amateur but I can't see the problem despite it probably staring me in the face.

 

[FoFa]

Well the issue with your original DB is the tblVibrationMagnitude becuase you have the serial number defined as NO DUPLICATES. Guess what, it will not allow a single serial number to appear but just once in the table with that setting (it is a PK). Set an Autonumber as the PK and make this (serial Number) a DUPLICATE ALLOWED index instead. Keep your linkage in the Master/Sub form correct.

 

[fraser_lindsay]

Hi there.

I tried that earlier today but it still doesn't seem to work. Maybe I'll go back to square 1 and rebuild it from scratch.

Thanks anyway.

 

[FoFa]

What error are you getting now?