how to link textbox to data from another table

[dubsdj]

Hi me again

I have a form which is populated with data on a student. Problem is I want to add a textbox which displays the students surname, but that surname is located in another table called students.

I tried selecting the table and field in the data source for the textbox, but I just get a #name? displayed in the box.

I think maybe I have to create a relationship between 2 tables, I dont know as im no expert. Anyway does anybody mind giving me some advice on where to go.

 

[Dennisk]

Two Ways to do this.

1) Use a query as the record source for the form and add the table with the surname to the query. Then link it to the student and add the Surname field to the query grid. Then on the form Set the text box to read only and bind to the surname.

2) use a DLookup Function to get the surname

 

[dubsdj]

problem is that how would the textbox know the correct record to display so that it links with the current record being displayed in the form?

how would it know what surname to display if there was no link between the tables?

sorry just a bit confused.

 

[Dennisk]

the link should be in the tables if you have correctly designed them (which I don't think you have.)

A typical table schema would be, PrimaryKey, StudentSurname, StudentForename, StudentDateOfBirth, StudentAddressLine01............

The Primary key is then use as a Foreign Key in any related tables.

So for simplicity a StudentCourse Table, would have StudentCourseID (PrimarykeyOfRecord), StudentID (Foreignkey, which is the Primarykey from the StudentTable), CourseTakenID

The Student record will be linked in a query using the StudentID in both tables.

Read up on Normalisation, primary Keys and Foreign Keys.