Colum cross-reference

[btj]

Although I believe this to be a Newbie question, I can't figure this out. Maybe you can help.

I currently have a table column ("Category&quot which lists categories and their abbreviations. The data was entered in a value list (i.e. "Firewall - FW";"Policy Processing - PP", etc). Due to some coding I am doing with my forms, I discovered a need to have this information in two columns.

What I would like is to have two columns ("Category ID" and "Category). When a user selects an abbreviation ("Category ID&quot, the "Category" column would be appropriately populated automatically.

Is this possible? Again, I would appreciate your help as I cannot figure this out.

With thanks in advance,
Ben

 

[RickSpr]

Assuming this table is more than just a table of categories--that is, that the category field is just one part of some larger record--I would recommend that you create a Categories table, with CategoryID as its key and Category as a non-key field. Then, you would carry just the CategoryID in the current table, not the Category column as well. This both eliminates redundancy, and prevents having different Category values for the same CategoryID value (an illogical condition).

If you do this, you no longer need to auto-populate the Category column, because there isn't one. You may still want to show the Category, of course, and you can do that (but it won't be updatable, and you wouldn't want it to be--they should update the CategoryID instead).

In a table datasheet for the current table, you can show the CategoryID field as a combo box that contains both the CategoryID and the Category. To do this, open the table in design view, select the CategoryID field, and click the Lookup tab at the bottom. In the Display Control box, select Combo Box, then set the Row Source to your new Categories table, set the Column Count to 2, and set the Column Widths to a small fraction of an inch, say .2, which should be big enough for your CategoryID. The field itself will show only the CategoryID, but the combo's drop down list will show both the CategoryID and the Category name.

In a form based on the current table, you can create a similar combo box that works the same way. In fact, if you update the table design as suggested above, then dropping the CategoryID field on the form will automatically create the combo box for you. As with the table, the form will display only the CategoryID in the combo box's text box part, but will display both columns in the list.

If you also want the Category name displayed on the form, for reference, you can simply create a locked, unbound text box for it, setting its Control Source property to an expression that extracts the Column(1) property of the combo box. You'll also need to refresh that text box when the combo box is updated (AfterUpdate event) and when the form changes records (Current event).

For the form, there is also another solution. You can base the form on a join between the current table and your Categories table, in which case the Category text box can be a bound field. Choosing a different CategoryID will automatically cause the text box to be updated in this case. The Category text box should be locked, however; you usually wouldn't want the users changing the name assigned to a CategoryID.

It's also possible to let users enter new categories on this form, but that's starting to wander off the subject. If you're interested, try searching the FAQs for terms like "combo box" and "not in list". Rick Sprague

 

[btj]

Great!

Rick - that was exactly what I was looking for. Thanks for helping me out with a solution.

 

[btj]

OK...actually need a follow-up.

Created the CategoryID and Category fields fine in the table and it works well. I want to use the CategoryID field in a main form to look up records in a subform. I have it working fine, but I cannot get the Category combobox to show both fields (CategoryID and Category).

Am I overlooking something? I don't see any options to display multiple fields within this combobox. All I see is the Bound Column feature.

- Ben

 

[btj]

Disregard...figured out the issue.

- Ben