form gets error message with multiple users

[neil27]

Hi,
I have created a profiling form for my school. Each boy can access his form using his admission number ( Adno) which is the primary key. On the form are lots of sub forms - for things like favourite authors, clubs, teams played in etc - but these don't have a primary key ( as they are automatically linked to the Adno via the main form ). Unfortunately when several boys are filling in their own forms at the same time I keep getting error messages, one says that the data can't be updated and another says that there is a primary key problem.
Does anyone have an idea about what might be wrong?

Thanks

 

[pr2-eugin]

neil27, just add a Primary Key to the table, and set it to AutoNumber.. This will avoid multiple users addition.. BTW, is your DB split?

 

[neil27]

Hi Paul,

My profile form has about 30 tables for the subforms. Do I need to add a primary key to each of them?
If I add a primary key:
1. Will I lose any of the data which I already have?
2. Will it affect the way any of my queries work?

Thanks
Neil27

 

[pr2-eugin]

Neil, Primary Key <-> Foreign Key linking is the basis of a perfectly Normalized table.. In general, every table must have its own Primary Key, this will make search of records easier, as the PK is UNIQUE..

To answer your Question..

1. Will I lose any of the data which I already have?

Yes and No... You cannot add an AutoNumber field if the table has data in it.. So normally you have to create another table and Insert the data from here to there, considering the number of tables this might be a tedious process.. but most definitely worth it.. There is another possibility that the table might have another field as Number which can be unique, but if even one NUMBER is duplicated within the table the option is to delete it (or) re issue another number.. Which again is a bit of an annoying process..

2. Will it affect the way any of my queries work?

I would say NO, but without knowing how your Queries are designed I cannot give a confident answer.. But adding a PK will only enhance a query performance..

 

[neil27]

Paul,

I have reviewed the content of my tables and most already have the Adno as their primary key - but some just have the Adno as a field - presumably because I neglected to make them the primary key. If I now amend these table to make the Adno the primary key will that cause any problems with existing data?

thanks

 

[pr2-eugin]

Okay I think you misunderstood me.. A table can have ONLY ONE primary key, but many Foreign Keys.. So I am not sure how your tables are organised.. but a sample DB would look like this..
tblStudents
admissionNo - PK
studentFirstName
studentLastName
'and all other information

The next table would be say Subjects??
tblSubjects
subjectID - PK
subjectName

Now if a Student is enrolled in a Year, then the subjects would be..
tblCurriculum
curriculumID - PK
studentAdNo - FK
subjectID - FK

 

[missinglinq]

To answer your Question..
... You cannot add an AutoNumber field if the table has data in it..

Don't know where you got this bit of misinformation from, but it's certainly not true! An AutoNumber Field can be added at any time and can be designated as the Primary Key, without loss of data.

The question that was asked has to be answered: Is this database split into a Front End/Back End configuration? Having multiple users sharing a single, non-split database, sitting on a network drive is the sure way to repeated episodes of corruption, speed and timing problems, and all manner of strange, odd and curious behavior, including the problem you're having, now! Multiple users simply have to work off of a split database, with each user having their own copy of the Front End, which contains everything except the data/tables, on their respective hard drives, and a Back End with only the Tables on a shared drive.

Linq ;0)>

 

[pr2-eugin]

Don't know where you got this bit of misinformation from, but it's certainly not true! An AutoNumber Field can be added at any time and can be designated as the Primary Key, without loss of data.

My bad..

I messed up thinking about 'Changing the previously existing field into Autonumber'.. Sorry about that Neil.. Thanks Linq for clearing that..

 

[missinglinq]

Well, yeah! Changing the Datatype of any existing Field will cause the loss of all data for those Records where the data currently held in that Field is not compatible with the new Datatype. But at least the Access Gnomes pop a warning message, telling you this!

Linq ;0)>