Solved

Setting Primary Key On a continous Form.

Posted on 2013-10-25
9
355 Views
Last Modified: 2013-11-20
Hello Experts!!
I have been working  on a questionaire database.
I need to prevent the user from saving a record that has already been saved.
The fields that cant be the same data at the same time are:
FormID
TimeOFInduction
Serail Number
Data
I put a a Primary Key on all three fields. But since it is a continuous form it will only allow one of the fields to append because of the continous record.


How do I set it to not accept duplicates on a continous form?
0
Comment
Question by:gigifarrow
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 57
ID: 39601668
<<But since it is a continuous form it will only allow one of the fields to append because of the continous record.>>

 Not sure I follow that...

<<How do I set it to not accept duplicates on a continous form? >>

 As you sumerized, the simplest method is to create an index on the key, with allow duplicates (Unique records) set to true.

 It does not need to be flagged as the Primary Key index to do that.

Jim.
0
 

Author Comment

by:gigifarrow
ID: 39601711
I append the record and it will only allow one field to append. It wouldnt allow all records that pertained to that serial number to come through.

All these records pertain to one Serial Number

ResponseIDFormID      QuestionID  QuestionTextResponses             Serial
25      1      1          Label Vehicle, Turret-Serial   7AGR0149Y
25      1      2          Raise Engine Access Door:        7AGR0149Y
25      1      3          Notify Supervisor if Found!      . 7AGR0149Y
 When I put a primaryKey on the FormID serial and FormID
it only appends 1 record
ResponseIDFormID      QuestionID  QuestionTextResponses             Serial
25      1      1          Label Vehicle, Turret-Serial   7AGR0149Y
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39602747
Do you have some code to check for dupes before appending (inserting) a record?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 35

Expert Comment

by:PatHartman
ID: 39603059
The index needs to be compound. That means it includes multiple columns.  Access (Jet/ACE) supports up to 10 columns in a primary key or index.

To create a compound PK, hold down the cntl key while you select the three columns.  When all three are highlighted, press the key button.  

If you would rather keep the autonumber as the PK, then you need to create a compound index and specify that it is unique.  To do this, open the indexes dialog.  In the first free line, enter a name for the index and select the first column.  Move to the next line but skip over the index name to select the second column.  On the third line, skip the name field and select the third column.  Since the name column is blank, Access assumes that the column belongs to the "named" index above.
0
 

Author Comment

by:gigifarrow
ID: 39603276
I made three columuns have primary keys

FormId, SerialNumber and Date.  I dont understand what you are telling me.
0
 

Author Comment

by:gigifarrow
ID: 39603280
If indexed can only use up to ten fields then that will not be enough.


I have one record that pertains to over 20 fields.  I  need all 20 fiellds to append and not be considered as duplicates.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39603360
A table may have only a single primary key.  That PK may be comprised of a single column or of multiple columns.  When the PK is compound (multiple columns), it is the combination of the three columns that must be unique not any of them individually.  So.

The following two records are valid since although the first two columns of the PK are "duplicates", the third column is different.
aa,bb,cc,other data
aa,bb,dd, other data

aa ff, cc, other data
is also valid because the combination of aa, ff, and cc is unique.
I dont understand what you are telling me.
Print out my directions and follow them step by step.
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 39603362
I have one record that pertains to over 20 fields.  I  need all 20 fiellds to append and not be considered as duplicates.
Then you will need to validate the data with code prior to inserting it or switch to SQL Server which allows for more columns in a compound PK/Index.

20 columns is a rather large number to obtain uniqueness.  Originally you said you had only three.  It doesn't matter how many columns are in the table.  The PK/index, is only concerned with uniqueness for the fields defined to be part of the PK/index.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39603797
<<I have one record that pertains to over 20 fields.  I  need all 20 fiellds to append and not be considered as duplicates. >>

 I think we need to step back and look at your database design.  While it's not impossible to need 20 fields to define a unique key, it's highly unusual.   Questioner type databases can be difficult to develop.

 I'm also left wondering if there's not confusion over logical PK's vs a physical ones and "uniqueness", and over indexes and keys.

You may find it helpful to read this:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2041-The-great-PK-debate-Natural-Keys-vs-Surrogates-again.html

 which discusses the differences in keys.   It's not long and won't take long to read through.

 Also, to further what Pat has said, with a JET DB, you can:

1. Have 10 fields per index
2. Have 32 indexes per table.

 Note that on the 32 index limit, if you have relationships and enforce RI on those, JET creates a hidden index on the PK for the many side of the relationship, which counts against the 32 index limit.   If you do this, you don't want to create an index yourself.

  If you really do need more then that, and the design is OK, then as he said, you will need to switch to another RDBMS to house the data.

  It's somewhat rare though to bump into these limits with most normalized designs.

Jim.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question