none
auto populate field in access form doesn't work when I reopen my program RRS feed

  • Question

  • There are two tables in my database. One is courses(CourseID, course name) and the other one is student(StudentID, Student name, CourseID, course name). So I want to create a form for this student table in which whenever I enter courseID value the course name should be automatically displayed in the coursename field. To do this I created a relationship between these two tables and also created a trigger called "After update" on courseID attribute of student form. what this does is, it fills the attribute value in the form based on the courseID which I have entered from courses table. This works fine when I create a new program but the problem comes when I reopen my access program. It just doesn't work at all. Does anyone have any idea why this happens?
    Thursday, September 13, 2018 7:48 AM

All replies

  • 1. You don't need a CourseName field in the Student table, since it can be derived from the CourseID. You can use a combo box on forms and reports to display the CourseName corresponding to the CourseID.

    2. I assume that one student can attend multiple courses, and multiple students can attend the same course. This is a so-called many-to-many relationship. To implement it, you need three tables:

    • Students - with StudentID, StudentName, and other student-related info.
    • Courses - with CourseID, CourseName, and other course-related info.
    • Attendance - which student attends which course, with StudentID, CourseID and perhaps info related to this student-course combination.

    You can use a form with subform for data entry purposes. See https://www.dropbox.com/s/aohysaxp646ws6y/Many2Many.mdb?dl=1 for a very simple example.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, September 13, 2018 10:36 AM