Solved

Setting Primary Key On a continous Form.

Posted on 2013-10-25
9
345 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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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
Comment Utility
Do you have some code to check for dupes before appending (inserting) a record?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:gigifarrow
Comment Utility
I made three columuns have primary keys

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

Author Comment

by:gigifarrow
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
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
Comment Utility
<<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.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now