According to Chapter 4 of the Base Handbook, the section on List Boxes states that, “A list box shows the user different data from what is actually transmitted into the table.” On the other hand, the section on Combo Boxes states that, “Combo boxes write the selected text directly into the table underlying the form.”
I am working on a private tutoring database. Suppose I have a combo box that has a list of student names (populated from a table called Students). I want the user to be able to start typing in the combo box and have it autocomplete the student’s name. Then, when the form gets saved/submitted, the StudentID (the primary key in Students) is the value that actually gets written into the Payments table. Is there any way to have the autocomplete goodness of a combo box, and yet have the actual data that gets written to the table be different than the selected text? Could I do it with some sort of SQL statement? Or is it time to resort to a macro?
In Microsoft Access, this functionality is apparently called a “lookup field”. In this post, there’s mention about foreign keys, but I’m not really sure how that relates to a combo box.