gigifarrow
asked on
Setting Primary Key On a continous Form.
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
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?
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
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?
ASKER
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
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
Do you have some code to check for dupes before appending (inserting) a record?
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.
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.
ASKER
I made three columuns have primary keys
FormId, SerialNumber and Date. I dont understand what you are telling me.
FormId, SerialNumber and Date. I dont understand what you are telling me.
ASKER
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.
I have one record that pertains to over 20 fields. I need all 20 fiellds to append and not be considered as duplicates.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.