I've been around Access for some 2 months, but considering the amount of time I put into it (around 2 hours a week), I'm very much in the newbie category.

My problem came when I tried to create a lookup control from a "Lookup Wizard" generated field ( read the post here ). This question still hasn't been cleared up (though my post is obviously dead, so I'll re-ask the question later), but my main concern is this: how do I make appropriate use of Foreign Keys??

I quote the following from an article found on The Access Web:


Up to now, I believe that FKs are numeric values containing a direct reference to another table's PK. The Lookup Wizard seems to fulfill this flawlessly. However, trying to do this manually, I found out that any SQL queries you try out

Most serious Access developers consider the Lookup Wizard to be both unnecessary and confusing. It is one way to create a relationship between two tables, but it's far from the best! The biggest problems are listed in the critique on the Access Web which you cited.

A Foreign Key is NOT necessarily a number. It is in fact a direct reference to another table's PK, but - despite Access attempts to convey this impression - a PK need NOT be an autonumber; a PK can be a number, text, a date, anything but a Memo. It can even consist of multiple (up to ten!) fields. The FK is simply a field of the same datatype as the PK that is being used as a link.

I would recommend avoiding Lookup Fields altogether; instead, use the Relationships Window to define relationships between tables.

The reason you were having the problem is that the Lookup Wizard has its own ideas about what you want. If you disagree with what you want - just don't use the wizard, or modify the combo box's Properties after you do so! A Combo Box has a Control Source (the name of the field into which the selected value will be stored; this may be blank); a Row Source (a table or query from which it retrieves values for display or storage); a Column Count (how many fields in that query are included in the combo; a BoundColumn property, specifying which field will be stored in the ControlSource; a ColumnWidths property, defining how wide each field is (some fields may have zero width so that they can be available but not displayed); and other properties.

For instance, if you have a RowSource
SELECT CustomerID, LastName & ", " & FirstName AS CustomerName, Address1 & " " & City & " " State AS CustomerAddress
FROM Customers
ORDER BY LastName, FirstName;

you could set the BoundColumn to 1 and the ControlSource to CustomerID in order to store the Customers table CustomerID into the Orders table CustomerID (as a foreign key); if you set the ColumnWidths property to

0;1.25;2.0

you'll *see* the customer name and address when the combo is dropped down (only the first visible column will be visible when it's not), but the CustomerID will be stored.

Hi arenaninja

Don't get "lookup fields" confused with "foreign keys". A FK is a field (not necessarily numeric) that contains the value of another table's PK. Generally you will have defined a relationship (one-to-many or occasionally one-to-one) between the table with the PK (one-side) and the table with the FK (many-side or other one-side). [Note that the two sides of a one-to-one relationship are not *equal*. There is still a PK and a FK]

Relationships are the whole raison d'être for relational databases. If a customer places an order, then your Orders table needs to contain no information about the customer, other than a FK to the Customers table. [Take a look at the sample NorthWind database for many examples of these relationships]

A Lookup Field is a FK which has been "doctored" so as to show, not the PK of the related table, but another foeld from that table. For example, it might show CustomerName instead of CustomerID. Most database professionals (and every Access MVP that I know) regards lookup fields to be Instruments of Satan (as you have already read at http://www.mvps.org/access/lookupfields.htm)

Now, you mentioned combo boxes. These are a great way to select a related record in order to fill in a FK value. However, you don't need to use lookup fields in order to use combo boxes.

Your example said:


I guess you are trying to use this as the RowSource of a combo box bound to a FK field named AddressType, or some such. You are most of the way there

The trick comes with understanding some of the other properties of the combo box. This RowSource is a query which has two columns: the first is AddressTypeID (the PK) and the second is AddressType (the displayed name).

As it has two columns, you must set the combo box's ColumnCount property to 2.

The value you want to store in the bound FK field is the AddressTypeID value in column 1, so you must set the BoundColumn property to 1.

Finally, you do not wish to see the AddressTypeID value in the combo box, only the text. For this you use the ColumnWidths property. This is a list of widths, one for each column, separated by semicolons.
There are three things you need to know:
1. When the combo box is not dropped down, what you see is the value from the selected row in the first column that has a non-zero width.
2. When the list is dropped, you see all the columns that do not have a zero width, each with its assigned width.
3. Any columns that do not have an assigned width have the remaining space divided equally between them.

In your case, you want to hide the first column, and let the second one use all the remaining space, so set ColumnWidths to 0;

Hope this is enough to dispel your confusion and get you going

Woa! Great explanation guys, I certainly understand now!!

Actually, this explanation is so good that I'm planning on printing it out for future reference. I've gone through some half a dozen books and a whole bunch of web sites, and I didn't get information like this =|

I think I can now proclaim mastery over this matter. Thank you for saving me a couple hundred headaches! =)