Adding Hobbies Transcript

Adding Hobbies to a Student Database

This is the step-by-step for adding hobbies to a student database with a main form and a subform. It goes with my Many to Many Relationship page.

The tables

Create 3 tables, tblHobby tblStudent tblStudentHobby. The construction of them will be like this, so from that information you should be able to reconstruct those tables.

Create the two forms

Now highlight tblStudent, press create, create a form, close it. Give it a name frmStudent. Now do the same with sfrmStudentHobby — create a form, but this time you want to edit the form and call up the property sheet, and change the Default View to Datasheet. Close the form and we will make that a subform; it's no different than a main form.

Put the subform on the main form

Now open the student form in design view, make yourself a bit of room and then drag the Student Hobby form onto it. Adjust the sizes and save changes.

Now open the students form again and we've got the student form and the student hobby form on the same form.

Check the synchronisation

Flick through the student records using the navigation buttons at the bottom of the form, and watch the subform. Can you see how the student IDs are synchronised?

So that means if we go back to student 1 and add hobby 1, then move on to student 2 and add hobby 1 and hobby 2, then move on to student 3 and add hobby 1, 2 and 3 — now if I go back to the beginning, as you can see the data matches. We've got hobby 1; if I move on to student 2, we've got hobbies 1 and 2; and student 3, we've got hobbies 1, 2 and 3.

Show the hobby, not the number

Problem is we're not seeing the hobby, so we need to go into the student hobby form and make a small change. In design view we've got the ID showing, we just want to change this to a combo box. When you change it to a combo box, rename it: go into the properties, Other, Name, and give it a "cbo" name.

Then we're going to change its Row Source slightly. In the Row Source, select the Hobby table, so now it will display records from the Hobby table. We want it to "Limit To List" because we don't want people to add other hobbies to the list, not without our permission.

On the Format tab, we want two columns, so change Column Count to 2. But we don't want to see two columns, so in Column Widths we set the first one to 0 so we can't see it, and the second one to 2, and MS Access will sort that out for us, changing that to centimetres. We don't want Column Heads. There are lots of things you can do there, but you can experiment with that yourself.

Save changes, and now let's open up the students form. Do you remember we had "1" showing there before, and for the next record we had 1 and 2 showing? Now we've got Hobby 1 and Hobby 2. Where we used to have the number showing, we now have the hobby name from the Hobby table.

So that's about it really — you can add your hobbies to your heart's content in the list.

Nifty Access wants to help you establish yourself as the "Go To" person in your organisation for database improvements! Nifty Access drop in "Nifty Components" will quickly elevate you to "Power User Level!"