?
Solved

Setting Primary Key On a continous Form.

Posted on 2013-10-25
9
Medium Priority
?
375 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 58
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 38

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 38

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 38

Assisted Solution

by:PatHartman
PatHartman earned 1000 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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

Technology Partners: 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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

777 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