AnsweredAssumed Answered

Script for Adding a record to a join table from two related tables

Question asked by RobParsons on Jan 10, 2014
Latest reply on Jan 10, 2014 by philmodjunk

Title

Script for Adding a record to a join table from two related tables

Post

     Hi All,

     I am creating a DB for a non profit to track membership and contacts.  They also want to track course attendance.  i have a contacts table [Contacts] and a Courses table [Courses] and a join table to track the registration [CourseHistory].

     [CourseHistory] is set up with the following:

     {CHID} = unique identifier
     {FIDContacts} = Foreign key for contacts
     {FIDCourses} = foreign key for courses
     {Paid} = drop down of yes/no to indicate if they have paid yet for the course they registered for.

     The procedure:

     The person will contact to register.
     The receptionist will look up the contact first in the DB
     With the record of the person to register displayed, the receptionist will click a register for a course button

     I want to create a script that:

     Creates a new record in the Courses History Table and auto populates the current {CID} from [Contacts] into the {FIDContacts} field of the new record in the [CoursesHistory] table

     then

     Opens a new layout to allow receptionist to search for a course for the person to register
     Once found, then click a register button that then adds the current {CoursesID} to the last record created in the [CoursesHistory] table.

     Any help would be greatly appreciated.

Outcomes