trouble with updating multiple tables from a form

[ISTodd]

Hi,

I have a database with several tables including tblStudent, tblTeacher, tblClasses.

I have a query qryStuTeachClass containing

tblStudent:StudId, StLastName, etc.

tblTeacher: TeacherID, TeacherLastName, and RoomNumber

tblContacts: ContactID, ContactDate, ContactTime, ContactMemo

The form I made, StudentContacts, has qryStuTeachClass as a control source and will update the tblStudent, but I can't automatically insert a new record or multiple records (more than one contact) into tblContact on that form.

I have the tbls and relationships set correctly, I think; but am I missing something important on the query or form end that would cause this...?

Thanks for any help anyone could offer... Todd

 

[willir]

Todd

What are you trying to insert?

I suspect you do have relationships defined well enough, and hopefully you are enforcing data intergirty.

Let's step back a bit.

Assume tblClass includes...
StudentID - foreign key to student table
TeacherID - foreign key to teacher

(Perhaps a better design would be to have tblClass include just the TeacherID and not the student, and have a tblRoster which includes StudentID and ClassID. This way you have the Teacher entered once. The other way, the teacher is entered multiple times.)

Now, before you can create a class record or roaster record, you have to have already defined the teacher and the student. With a complex query, Access may not know if you are trying to create a student, teacher or class record.

A simpler approach would be to have a form that creates the student, or finds the student (unbound combo box), and then allows you to create the class record in a subform. With a bit more coding, you can create a class on the fly, and then assign the student to the class.

Richard

 

[BNPMike]

Possibly you would be better using Access Form/Subform facilties. Look this up in Help (you may to need to go onto the Web as Help has gone downhill badly). You need to spend a bit of time understanding how this works but it is a key feature of Access so it's worth it. As I recall youi can have a mainform and two levels of subform.

I did one a day or two ago but I've forgotten yet again how to do it.

Apart from that, try running your query as a datasheet. You should be able to edit and add records providing you meet the criteria for updatable views. If you can't update/add then you know your problem is with the query rather than the form.

Read this:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp