Just drop them and create them newly from scratch. In my form, the main form and any of its subforms is linked to a single table. Foreign keys are represented by listboxes. Related record sets are shown in one subform and 2 sub-subforms linked by their related pairs of primary/forein keys.
You may start with the wizard to create a first pair of main form and sub-form. But then you have to continue with the form navigator (menu:Form>âForm Navigatorâ).
Replace numeric foreign keys to listboxes (right-click>Replace with>Listbox), bind a listbox query to the listbox and set the drop-down property which unfortunately is not set for stand-alone listboxes.
Before creating new forms:
There is also âtbl-Coursesâ which uses the course name as primary and foreign keys in the same way as you used the subject names.
Demo of problem: Open either âtbl-Coursesâ or the related tutorials table and try to edit any name that occurs in both tables, for instance âShift Artâ to âShifting Artsâ. It is impossible to change this piece of meaningul user data because the name is copied as a foreign key in tbl-Tutorials. This relation enforces foreign key values that do exist as a primary key in the tbl-Courses.
Simple solution: Double-click the line in the relations window and select option âUpdate cascadeâ. Now you can change the primary key in tbl-Courses and the foreign keys in tbl-Tutorials will follow. Option âDelete cascadeâ deletes all related records in tbl-Tutorials when you delete one record in tbl-Courses.
My personal preference is a name column together with an auto-ID (may be because Iâm simply used to it).
How to implement a CourseName with auto-ID, keeping the existing table data (would be much easier if you simply drop the table with its data):
- Delete tbl-Courses from the relations window. This will also delete the relation.
- Rename the table, say âtbl-Courses-oldâ (right-click table icon>Rename). It keeps the names.
- Create a new tbl-Courses with name and auto-ID as I did with tbl-Subject_List.
- Append an integer CoursesID to the tutorials, like my SID in the sources table.
- Call menu:Tools>SQL⌠and run
INSERT INTO "tbl-Courses" (SELECT "CourseName",NULL FROM "tbl-Courses-old").
If your auto-ID is the first column:
INSERT INTO "tbl-Courses" (SELECT NULL, "CourseName" FROM "tbl-Courses-old")
This writes the names from the old table into the new one and NULL to the auto-ID, which will be substituted with a unique auto-ID.
- Run the following statement to fill up the foreign key with the correponding IDs from the new table:
UPDATE "tbl-Tutorials" SET "CourseID" =(SELECT "ID" FROM "tbl-Courses" WHERE "tbl-Tutorials"."CourseName"="tbl-Courses"."CourseName")
- Call menu:View>Refresh Tables
- Add the new âtbl-Coursesâ to the relations window and connect tbl-Tutorials.CoursesID with tbl-Courses.ID
- Open âtbl-Tutorialsâ for editing, make the CoursesID mandatory (Entry required = Yes) and delete the old âCourseNameâ column which is no longer referenced nor needed. (btw: I forgot to make tbl-Subjects.SID mandatory).
- Drop the old table.
With a meaningless auto-ID, you can edit all the user data without disturbing the relations established by the ID values.
If you want to enforce unique names (prevent duplicates) besides the unique auto-ID:
Open âtbl-Coursesâ for editing, menu:Tools>Index Design and create a unique index on the name column. This fails if the column has duplicates already.
P.S. The table editor fails with step 9. This works:
ALTER TABLE "tbl-Tutorials" ALTER COLUMN "CoursesID" SET NOT NULL;
ALTER TABLE "tbl-Tutorials" DROP COLUMN "CourseName";
