I am designing a database and have run into an issue. I tried to create the relationships between my tables, enforcing referencial integrity but the message of "No Unique Index Found for the referenced field of the primary table" comes up.

Two tables are:

tblAudit
 -Audit_ID (Autonumber) (PK)
 -UserName (Text)
 -Time (date/time)
 -Event

tblUserList
 -Name_ID (Autonumber) (PK)
 -FirstName (Text)
 -LastName (Text)
 -eMail (Text)
 -UserName (Text)
 -Access_ID (Number) (PK of another table)
 -ActiveUser (Yes/No)
 -View_ID (Number) (PK of another table)

This is occurring with a few tables in the db that I'm creating. I don't want to change any of the tables for the relationship to 'work the first time' because I figure that the users won't know what is going on if thye have to select numbers versus names on the forms that I'm going to create.

Can anyone help with this issue?

Bryan

If numbers are showing instead of meaningful data, I don't think you have created the table look ups properly. You should have a combo box with one or more fields of meaningful data, and a hidden field with the number.

Can you post the queries for the look up fields (called FK fields for Foreign Key)?



mmcdonal

This error is happening when trying to set relationships. It will go through, the setting up of the relationship, it just won't allow me to 'enforce referencial integrity' to the relationship. I haven't even entered any information into the db yet, just started to create the queries and the basics of the forms, no command buttons on them though.

You need to make sure that when you create a table, you set a field to the Primary Key. Have you done this for the fields in the tables you are using for look ups?


mmcdonal

Yeah, I've done this for both tables.

In the table listing of my first post on the thread, the "PK" is primary key. It is Name_ID and Audit_ID.

How are you relating the first and second tables? I don't see an Audit_ID (FK) in tblUserList to relate it to the PK in tblAudit.

Rename the field "Time". That is an access reserved function that returns the current time. It will cause problems in queries, etc.

What is UserName? Is that someone's full name? You should separate those into first and last if it is. Or is it just a user nickname?

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

UserName is the name that the individual uses to log into the db. That is also how the tables are to be joined. The tblAudit stores the user name of th eperson for audit purposes. The time, is referenceing the date and time that the event occurred. That is not going to be used for anything as far as queries or things like that, its solely just for reference.