Creating Combo-boxes in Access

As we've been designing database tables over the past few weeks, it often seems like we've got primary keys everywhere - our joining tables are just all numbers which makes them really hard to understand!

It can be very tempting to add Lookup Fields to our database tables which lookup the corresponding value and display that instead (for example, instead of displaying the student ID of 945, the related name of "Henry the Octapus" is displayed instead). But one of the 10 commandments of Access is:

thou shalt abhor the use of "Lookup Fields" which art the creation of the Evil One.

(If you're interested, follow the link on Lookup Fields to find out why.)

So how do we then make our database more user friendly? The key is to do it through our forms (our users should never see our tables anyway!).

Open up your Student Enrolments database, and create a form to enrol students into courses.

• Initially, after creating your form with the Wizard, you should have fields for Student ID, Course ID and perhaps Enrolment Date or other fields associated with the enrolment in your database.
• Test your form to make sure it works.
• Now replace your Student ID text control with a combo box, and see if you can get it to display student names.
• Test your form again... does it work? If not, see if you can find out why it's not working.

The key is that, even though we want to display the students name to the user, we still need to store the student ID in the enrolments table - not the name! How do we do that? TechOnTheNet has a great tutorial to step you through the process, see Access: Bind combo box to primary key but display a description field. Don't you love the information age! You might want to print it out.

See if you can apply this to your Student ID field, as well as the Course ID field, and test out your Enrolments form.

Challenge: See if you can modify your Student ID combobox so that it displays the students name in the format "lastname, firstname" (you'll need to join lastname and firstname together somehow in your query.)

Once you're happy with your combo boxes in the Student Enrolments database, practise with your Traffic Fines database!