Solved

Setting Primary Key On a continous Form.

Posted on 2013-10-25
9
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 37

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 37

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 37

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

739 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