Hi,

   Thanks for your help in advance. Perhaps you have a better solution to this problem.

   I have two tables that I want to relate like this...

tblEmployee
EmpID - autonumber PK
FirstName - text
LastName - text
SSN - required, no zero length, Indexed no dupes

tblExam
ExamID - autonumber PK
Date - date
Time - date
SSN - required, no zero length, indexed yes dupes

   You can see there is a one to many from the tblEmployee SSN to tblExam SSN so that employees can have many exams.

   The exam results are pushed into the tblExam with VBA from data in a text file, and there is no way that the SSN in tblExam can use a look up to get the PK in tblEmployee, so it just goes in as an SSN.

   I have created a relationship between these two tables, but when I check Enforce Referential Integrity, I get this error:

"No unique index found for the referenced field of the primary table"

  Both SSN fields are indexed. They are linked to one another for purposes of downstream analysis, but I can't link a subform with exam results to the Employee main form. I made the link between fields on the form by SSN/SSN, but the data won't synchronize. When I build a query on these two tables, I can show the data from the tblExam table, but no data from the tblEmployee table, even though all the records from the tblExam table show up... just blank fields for the SSN and PK fields from tblEmployee.

   Any help would be greatly appreciated. Perhaps some code for the subform instead of a static data source?

Thanks folks,

P.S. Support tsunami relief through red cross and red crescent (www.ifrc.org)

mmcdonal

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Microsoft's site says that they had a similar problem with Access 2.0 through 97.

The cause for this problem there was that...

The order of the primary key fields in Design view of the table is different from the order of the fields in the PrimaryKey index.

The solution they give is:

RESOLUTION
There are two ways to work around this error:
1. Reorder the PrimaryKey fields in the index so that they appear in the same order as they do in Design view of the table.
2. Order the field names in the Relationships dialog box to match the order of the field names in the PrimaryKey index.

It kind of made sence to me, but without something hands on to try it, I can't tell if this would fix your problem. Let me know either way. The url at Microsoft is http://support.microsoft.com/default...b;EN-US;155514

Mike

Mike
EchoVue.com

Thanks for your speedy reply. I have since played around with the data and it was a very stupid problem...

   I was storing the SSN's in the tblEmployee with "-" from the input mask, but was not in the tblExam table, and apparently this caused the problem. I made them both the same (no "-") and it seems to work fine now, at least for purposes of linking the main and subform.


mmcdonal

Doesn't that just make you want to kick yourself?

You may have the same problem as me. Too much turkey, and not enough radiation from a PC Monitor for the last week or so!

Talk to you later

Mike

Mike
EchoVue.com

I'm glad it worked. BTW, rename your date and time fields to something other than "Date" and "Time". Those are reserved words in VBA and could cause programming errors in the future.

Note that Access date/time fields hold BOTH the date and time in them, so you could conceivably house both data in one field and extract either a date or time from it as needed.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

That's a good point about the Date and Time fields. I can do this and then rename them in the code that pushes the data there.

Since the data is being lifted from a text file like this: "1220041420" for Dec 20, 2004, 2:20pm, I am just splitting it in my code to 12/20/2004 and 14:20 (which Access reads as 2:20 pm automatically). Since I have to split it once in code already, I think putting it in two fields rather than splitting it again is quicker.

mmcdonal