Excel In Access --- Part 3

Excel in Access (Part 1) (http://www.access-programmers.co.uk/forums/showthread.php?p=671226)
Excel in Access (Part 2) (http://www.access-programmers.co.uk/forums/showthread.php?t=143607)

Video Version HERE: (http://msaccesshintsandtips.ning.com/profiles/blog/show?id=948619%3ABlogPost%3A7031)

How to use the Normalization Form (http://www.access-programmers.co.uk/forums/showthread.php?t=143983)

Using the normalization form --- Download Here (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=20619&d=1202664804) we converted part of a spreadsheet type table into the beginnings of a relational table.

From this table we derived a “look up table” now giving us a total of three tables, the remains of the first table, “tblStudent” (the student names), the next table “tblStudentSubject” stores the subject(s) related to each student, and finally a third table, “tblSubject” a “look-up table” to store the actual subject description.

http://i185.photobucket.com/albums/x317/UncleGizmo/ThreeTables.png

From the table “tblStudentSubject” we created a form in datasheet view:

http://i185.photobucket.com/albums/x317/UncleGizmo/DataSheetForm.png

Now all we need do is combine this datasheet view form with a form based on the students list, and this will give us a form for correctly displaying the student names and the subject(S) the student is taking in one Form:

Using the wizard create a basic form from the student table and name it “frmStudent” arrange its size so it has some open space as shown.

http://i185.photobucket.com/albums/x317/UncleGizmo/StudentForm.png


Now open “frmStudent” in design view

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_1.png

And drag the subform “sfrmlStudentSubject” into the clear area on the student form “frmStudent”

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_2.png

You may wish to delete the text box, you don’t have to but I usually find it looks better without it.

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_3.png

Size the form to suit

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_4.png


Now save the form and have a look, you will notice that it incorrectly shows all of the records in the subform,

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_5.png

Now the next bit is tricky, for two reasons, the form isn’t really on top of the other form, it actually sits in a subform window and you need to gain access to the properties of this subform window by clicking on the tiny line that you can just see around your subform. This can be a difficult task to master first time.

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_6.png

Now open the “subform field linker” dialog box by clicking on the ellipsis (…)

http://i185.photobucket.com/albums/x317/UncleGizmo/DragSub_7.png


Phone: +44 1635 522233
Mobile: +44 7747 018875
Email: email@tonyhine.co.uk

Web: http://msaccesshintsandtips.ning.com