Adding data to a sub-form which can then be saved

[moascar]

I am creating a database of student results, importing much of the data about the students and their results from Excel. Fine so far.

I want to be able to add a new set of re sults for a student if necessary and would like to do this via a datasheet subform which shows the current results for that student and enables me to add more results.

I have created a main form for the students and a subform for results (this displays current results) but when I try to enter a new subject name, result and grade I am not permitted.

Can anyone help.

I am not an experienced Access user so any replies need to be in non-technical language please.

Grateful for any help!

Thanks

 

[suepowell]

Hi,
I presume you are using one table for your student with the student form based on that, and another table for the results which is the base for your subform.

In both tables you should have a unique identifier for the record, and this sould be the primary key (set in the table design)

In your results table you should have the primary key of the student table so the results can be assigned to the correct student.

When you link the subform to the main form you will use this filed to link the 2.

You should then be able to add more records to the results subform.

You don't have much detail in your question, so If I have completely misunderstood your problem sorry.

If the above is all correct, then do explain in a bit more detail and I'll try to help, or you could zip your database and post it so I can see what you are trying to do.

Sue

 

[moascar]

Sue

Thanks for your offer to help.

Sorry about not enough detail. I have got the links correctly to the sub-form and have attached the zipped version of the db.

The form is activated by a query, enter a student surname Axtell to try to add a result. I have been trying Business Studies as it is a result which she does not already have.

Another question - why does the Roll Number not appear on a new row of the sub-form, does it have to be expressed differently such as =RollNumber

Hope the attachment helps

Thanks for any help

 

[suepowell]

Hi,

Took a while but I have found the problem now.

In the recordsource of your subform you need to add the subjectID field of the results table, it was having trouble linking the subject table without the id field. You also need to have the rollnumber form the results table rather then the pupil table in this recordsource.

In general it would be easier to add all the fields from the table the subform is based on, and just the extra ones from the linked tables.

In fact in this case you don't need the pupil table in the subform source at all.

Now that the subform has the correct data source, the rollnumber is filled in utomatically as you expect.

One point, it is usually better, and I do this as a matter of course, to have an autonumber ID field in each table. It just saves problems with data types when linking tables, and ensures the primary code is always unique. If you use a text field there is always the possibility that someone will mistakenly try to use the same code for more than one pupil, and cause the database to throw up errors.

Good luck with the rest of it

Sue

 

[moascar]

HI Sue

Thanks for your help. I re-did the sub-form as you suggested and it did work.

One problem was that when I added a new result for a subject already in the subject table Access generated a duplicate subject, so I ended up with more than one business studies which I have already.

Do I need to index both the subjectID and the subject fields together so that they are not duplicated, or would this cause problems. I have already done this with the results table with rollnumber and subjectid, so that student will have two results of the same subject.

INcidentally the roll number is a unique id on a different database and we had to change to text to do the import.

So many things to think about and get wrong!

Thanks