Foreign key data entry | User-friendly query generation
You may download the simple database used for this tutorial.
A common problem in relational database is the entry of foreign keys. It's a common problem because on the one hand, foreign keys are everywhere; they are the glue that connects relational tables. On the other hand, foreign keys are frequently short, cryptic, or otherwise unintuitive. This is particularly true when system generated keys are used. How can the poor user be expected to remember the system-generated numeric identifier for an individual. I have enough trouble remembering my own social security number, for example, let alone anyone else's.
I'm going to step you through a sequence of steps, based on the tables created for a class assignment. We have a table with two foreign keys, MaintProperty and MaintPerson which are identifiers for properties and repairpersons respectively. We will end up with a data entry form for the maintenance table that will provide combo boxes the user can use to select valid values for these two fields. Furthermore, we will enable the user to select properties and repairpersons based on their names, not any numeric identifier.
The maintenance table looks like this:
If we just use the wizard to create a data entry form, it might look like this:
Here, I'm trying to enter a repair person reference, but can't remember what the person's numeric identifier is. Rather inconvenient.
The solution is to create a combo box that will a) list the names of all the valid repair persons (those listed in the RepairPerson table) and b) store the associated numeric identifier in the Maintenance table. As we shall see, a side benefit will be that whenever we view maintenance records we will see the names, rather than the numeric identifiers for the repair people.
The steps we will take are:
In the query shown here, I am creating a two-column list.
In this query I've done a couple of things which are optional, but which make the system more user-friendly. First, I've used the '+' symbol to concatenate two fields into one. Second, I've used the ORDER BY clause to cause the list to be sorted alphabetically. When I run the query I get:
This query has been saved as 'qryRepairPerson'.
Now, I use the wizard to create a combo box based on this query. (Be
sure the toolbox 
is turned on, and that the wizard on the toolbox
is turned on
as well.). At the appropriate point, specify that the combo box
is to be based on the query we just created.
Next, choose the fields that are to be shown in the combo box. In this case, I want both the names, and the RepID to be displayed. This will enable me to distinguish between two repairpeople who have the same. It's unlikely, but it could happen.
At this stage it is important that the field you really want to be visible on the data entry form be first on the list. The way Access works is that when the Combo box is not 'clicked', it displays only the value in the first column. We want to see the repairperson's name, but not the repid, at least, not until we 'click' on the combo box.
While we want to show the Repair Person (the name), we want the Repid to be stored in the Maintenance table. The wizard permits us to specify this:
The last thing we need to do is specify which field in the Maintenance table will store the Repid value we choose.
Answer the last couple of questions by the wizard, and you end up with a new combo box on your form:
Delete the old MainPerson field, and move the new one into its place, and that's it!
Suppose I wanted to view all of the maintenance records for a particular property. I don't want to hard-code the property address into a query, of course. One solution demonstrated in class is to trick Access by putting a variable name in the WHERE clause that Access does not understand. The key is making the variable name look like a query, so the user will enter a value when prompted. As we've seen before, it looks something like this:
When you run it, Access gives you the following dialog box:
You enter the appropriate property address and get the results.
The problem is, that you must a) know what the property address is, and b) type it exactly as it is stored in the database. Otherwise, you won't get any results. We'd like to provide the user with a combo box listing the choices, and then have the query run accordingly. I'm going to implement this, and a little more. I'll do the following:
This process will involve creating buttons and macros.
In this approach, you will provide the user with a query dialog box that offers a combo box listing all the properties. The users chooses one, clicks 'OK', and then will see a properties form showing the record that matches the selection. The query dialog box will look like this:
First, create a new, blank form. The fastest way to do this is not use the wizard. If you do use a wizard, do not specify any table or query to supply data.
Second, create a query (e.g. SELECT PropertyStreet FROM Property;) that will provide a simple list of known properties.
Third, place a combo box on the form you just created, using this new query as the source of data for this combo box.
When you do this, you should have:
In design view, look at the Properties of the combo box. As shown here, Access has given my combo box a name 'combo2' which I would normally change to something more meaningful. I'll keep the name 'combo2' for now.
I will now place a button on the form which, when clicked, will cause the Maintenance Jobs form to be opened and display not all maintenance rows, but only those that have in their MaintProperty column a value equal to the one I specified in the combo box. You know how to get this started, but be sure to specify that the button is to open a form:
You'll then specify the name of the form you wish to open. Following this, you are asked whether you want to open all rows, or just some. You want just some:
The next dialog box you see looks like this:
It is here that you specify which column in the Maintenance table is to match the value in the combo box. When presented with this dialog box, I clicked first on 'Combo2', then on 'MaintProperty', then on the '<->' button. Then, finish up the button in the usual fashion.
I now have a form that looks like this:
I have chosen a property and, when I click on 'OK', I get:
Notice that only the five maintenance jobs done at 6543 Western Ave. have retrieved. Second, notice that the MaintPerson shows the name of the person, rather than the repid.
To finish up all you need to do is add another button, Cancel, that closes the query form...
In this example, the combo box on the query dialog box contained a single column of values that were both the key for the property table, and also something the user could look at and understand. A common situation will be where you want to, say, look up an employee in which the user choose an employee by name, but the database queries the employee table using the associated emp_id value. In this case you would have to create a two-column combo box showing the employee name and employee id. The "value" of the combo box would be the employee id, which would then be used in a query. The combo box would still display the employee name. (In a future version of this tutorial I'll change the example to illustrate this point...)