views:

34

answers:

1

All but the first and last bit here is copied from another poster's question. The topic is MS-Access Forms with Bound Controls. I am new to Access. I appreciate all the 'levels of indirection' it provides for forms, but I can't seem to find an example of what I imagine to be a very, very common task... here is the answer (Thanks, Thomas) to the other user's question, followed by my 'twist':

Row Source is typically used to determine how to build a list of items whereas Control Source determines what field will be used to store or retrieve the value. For example, in a Combo Box you have both properties. The Row Source determines how to build the list the user sees when they hit the down arrow. The Control Source determines where to store the value that the user selects.

EXAMPLE Suppose we have a form that is bound to table called Cars which lists information about automobiles. One of the columns in this table is used to store the color of the car. Let's suppose that column is named BodyColor. You also have another table of allowed colors (e.g. Blue, Yellow, Green, Steel Blue, Midnight Blue etc.). You want to ensure that users choose from this list of colors when they enter a value for the car's color.

On our form we add a Combo Box where we set the following properties:

Control Source  :   BodyColor
Row Source      :   SELECT Colors.Name FROM Colors ORDER BY Colors.Name;
Row Source Type   :   Table/Query

When a user sees your Combo Box, they will be able to hit the down arrow on the Combo Box and see a list of colors. When they choose a color, the Form will save their selection into the BodyColor column.

With the exact situation above, how do I get the form to store the primary (autonumber) key for BodyColor instead? I still want the user to pick BodyColor from the Combo Box, but I don't want them to see/pick the PK. Thanks.

+1  A: 

You can set column widths and bound column, which is not needed here because the default value is zero, but nice to know. So:

 Row Source : SELECT Colors.PK, Colors.Name FROM Colors ORDER BY Colors.Name

 Column Widths: 0; 2cm

The user will not see PK because it is hidden (0 width), but it is in the bound column zero that is written to the control source.

BTW I really hope your table does not contain a column (field) called name, because it is a very reserved word indeed and will make your life deeply unpleasant.

Remou
Thanks. zero-width column, nice. No "name" column, most of my question is a paste of someone else's here on SO. ...10 minutes later ... Well my exact situation is actually a text box, not a combo box, so the column-width thing isn't an option. But it's good to know anyway.
klausnrooster
You say "I still want the user to pick BodyColor from the Combo Box", I do not see any reference to a textbox in your question at all. In fact, I do not see how the question could have anything to do with a textbox.
Remou
Yep, sorry. It's a listbox. Visually I could not ID it correctly. I guess the presence of spinner arrows didn't register.
klausnrooster
You can use the same technique with a listbox.
Remou
Thanks. I'll try it sometime. I'm not positive but I think it's necessary to delete a control, recreate it, and start from scratch setting rowsource. Many times I would edit rowsource and either the change didn't seem to stick or just didn't work. This is with closing the design view each time, answering 'Yes' to the 'Do you want to save...' message (and plenty clicks on save icon before). I finally decided for a less normalized design. I'll redo it if it's slow. I'll get a "good enough" version in place, then hit the books - hopefully I'll know enough to be dangerous before my users revolt.
klausnrooster
It is not necessary to start from scratch. It is very common indeed to edit the row source. If you are having difficulties, you should use the three dots button (...) to build the sql statement, do not forget that you can also simply use the name of a table or query for the rows source. It is almost never a good idea to use a less normalized design. There is a lot to be said for creating a simple database and a few forms for testing ideas.
Remou