Solved

Setting Primary Key On a continous Form.

Posted on 2013-10-25
9
354 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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