AfterUpdate function

[BBonexx]

Hello,

I am very new to Access, so I would like to thank you in advance for you help.

I have created 2 Tables in Access 2010. Classes and Students.
The table "Classes" has the following columns "ID, Courses,and Coefficient."
The table "Students" has the following columns "ID,Student,Courses,and Coefficient." "Courses" in this table is a "lookup" from the listed Courses in the table "Classes"
"Coefficient" from both tables is a number.

What I would like to do is update the value of "Coefficient" in the "Students" table once I pick/select the right course for the particular student. This value is coming from "Coefficient" in the table "Classes". From What I read so far, I had to create a form based on "Student" Table and write a code for a AfterUpdate() field/function. Here is the code



Private Sub Courses_AfterUpdate()
Coefficient= DLookup("Coefficient", "Classes", "Courses = '" & Courses & "'")
End Sub

The problem is that the DLookup works fine without giving me errors. However, the form is NOT updating once I choose the right course.

Please help me with this issue,

Thanks

[June7]

Can each student have more than one 'course' record? If so this is a many-to-many relationship and really need a third 'junction' table that has records associating students with courses.

Why do you need to save the Coefficient value? It can always be retrieved in a query that joins the tables.

Is the Coefficient textbox bound to the Coefficient field in Students table?

Is ID the primary key in both tables? If so, why save the Courses value as foreign key in Students? Either save the PK ID value or set Courses as the primary key in Classes.

[BBonexx]

Thanks June7 for the reply.

Currently, I have these basic tables with the assumptions that each student can only enroll in 1 class at a time. It is just for pure simplification and learning as I go. You are right, in the future there should be another "Junction" Table. The reason for which I want to save coefficient value in the Student Table is because, I want to create 2 other fields within "Student" table that will be called Grade and Total score (both numbers). Hence, I will multiply "Coefficient" by "Grade" to get the total score.

Currently, the Coefficient textbox is not bound to the Coefficient in the Students Table.

I hope I clarified myself a bit better...Thank you

[June7]

Relational database principle of not duplicating data (the coefficient) still applies. Another principle of relational db is save raw data, calculate in reports. Instead of saving the Grade and Total score data, calculate when needed in query that joins tables and retrieves the coefficient. Even if you decide for some reason to save the coefficient, no need to save the calculated Grade and Total.

If you want the saved coefficient value to display on form then bind the textbox.