Problem dynamically removing controls

[boblarson]

You will need to use subforms and maybe subreports (when report building) so that you get the data in the right way and so it is updateable.

 

[stevenblanc]

Hmm how would I go about creating single line record entries for each courserecord then?

I have related all the tables to get all the info I need, but I still end up with a record for each pedagogical item. I'm assuming there's no way to do this in SQL and I'm going to have to code the export to combine individual records?

So far I have this:

SELECT tblCourseRecords.CourseRecordID, tblSemesters.semFull, tblDepartments.departmentCode, tblCourses.courseCode, tblCourseRecords.sectionID, tblFaculty.nameFull, tblGuidelines.pedTitle, tblCourseRecordPedItems.pedNum, tblCourseRecordPedItems.pedTime, tblCourseRecordPedItems.pedNotes
FROM (tblGuidelines INNER JOIN ((((tblCourseRecords INNER JOIN tblCourseRecordPedItems ON tblCourseRecords.CourseRecordID = tblCourseRecordPedItems.CourseRecordID) INNER JOIN tblCourses ON tblCourseRecords.courseID = tblCourses.courseID) INNER JOIN tblDepartments ON tblCourseRecords.departmentID = tblDepartments.departmentID) INNER JOIN tblFaculty ON tblCourseRecords.facultyID = tblFaculty.facultyID) ON tblGuidelines.pedGuide = tblCourseRecordPedItems.pedGuide) INNER JOIN tblSemesters ON tblCourseRecords.semesterID = tblSemesters.semesterID;

Though this might be usable to some extent for pivot tables...

 

[boblarson]

You would have a record for each pedagogical item and hence you would use a subform. Your Course Record would be the parent form and then the junction table would be the record source of the subform. Then you select each pedagogical item that is applicable.

 

[stevenblanc]

Thanks Bob,

Going to do some reading on junction tables. I'll let you know when I figure it out!

 

[boblarson]

If you want an example. here's one from my site:

http://downloads.btabdevelopment.com/Samples/misc/SAMPLE-StoreDiffData.zip

 

[stevenblanc]

Okay so I think I got it.

I removed the field CourseRecordPedID that you suggested earlier.

And I made tblCourseRecordPedItems the junction table joined to tblCourseRecords by CourseRecordID and to tblGuidelines by pedGuide

These fields are all PKs.

Seems to do the trick in table view. Going to slap together a form to see how it works.

 

[stevenblanc]

Right form good to go, works well. adds a record for each line, looks cleaner than the old one and i can still maintain the code relationship by having the subform in continuous view.

Now the challenge is getting this bad boy to export coherently to excel.