Form linking

[a14929]

OK. I have two sets of data - book details and student details. A combo box is used to select a book on the student form from the book table. However, simultaneously, I would like the name of the student holding that book to appear on the book form.
I.e. the book ID goes to the student form, and the student name goes to the book form.
HELP. How can i do this?

 

[Pat Hartman]

This is a one-to-many relationship. One student can hold many books but each book may be held by only one student. The studentId should be placed in the book table but you cannot place the bookID in the student table. To do that you would need multiple fields in the student table to hold bookIDs.

When you are looking at the data from the point of view of a book, you can join (this must be a left join since a book may be in the library and not at this time assigned to a student) the book table to the student table to retrieve the student name and any other student information you might need.

When you are looking at the data from the point of view of a student, you also join to the book table. But this time you use an equi-join. You will get a list of all books held by a student.

 

[homebound]

If the underlying table os the coimbo box also contains the ID field for the student table add this field to the the combo box select query. You can refer to this field using combofield.column(Column #). Remebr the first field begins with zero.
Then add a student subform that filters on that column after update.