Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 11th, 2007, 09:59 AM
Authorized User
 
Join Date: Apr 2007
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default Error code / General question

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
 
Old April 11th, 2007, 02:24 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 11th, 2007, 02:29 PM
Authorized User
 
Join Date: Apr 2007
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old April 12th, 2007, 06:11 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 12th, 2007, 07:51 AM
Authorized User
 
Join Date: Apr 2007
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old April 20th, 2007, 07:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old April 20th, 2007, 08:12 AM
Authorized User
 
Join Date: Apr 2007
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.






Similar Threads
Thread Thread Starter Forum Replies Last Post
General SELECT question rsearing SQL Server 2005 1 January 16th, 2007 12:01 PM
General Question About c++ madeebmir C++ Programming 0 August 15th, 2006 10:48 AM
Very general question merianos VB How-To 4 August 1st, 2006 12:24 PM
General Scoping Question cliffd64 VB.NET 2002/2003 Basics 2 June 9th, 2005 06:37 AM
General Question stu9820 ASP.NET 1.0 and 1.1 Basics 2 September 23rd, 2003 03:36 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.