Combo Box Displaying Text, But Storing ID

[SeaTigr]

Hi all,



Hoping this is a simple fix and I just didn't know the right phrasing to find the solution.

I'm creating an inventory database using a form to input new assets. Instead of defining the options for a combo box when I created the box, I put the options I wanted in a separate table so they can be added to and deleted as needed. The combo box is so users don't accidentally misspell a system name.

While the combo box displays text, the equipment table displays the ID number of the option (ID from the options table).

How do I get the equipment table to store the options text instead of the options ID number?

[alansidman]

What you have is correct and proper for a data base. It is the most efficient way for storing data which is what tables are for. If you need to see the corresponding information that is associated with the value in the table, it may be retrieved in a query or a report. To get the information, you would link the lookup table to the main table joined on the IDs. Users should not be looking in tables for information. Use forms, reports and queries.

[SeaTigr]

Thank you for your reply, Mr. Sidman.

Normally, I would leave the issue alone, as you are correct that users don't need to be looking in tables.

The problem arises because I am creating a second form. The first form, described above, was to allow the db admin to easily enter a new system into the db (system name, classification level, points of contact, etc.). The second form, which I am attempting to create now, is for users to enter specific pieces of equipment.

Since each piece of equipment belongs to a system, a user needs to choose a system on the equipment-entry form. When I created the combo box on the equipment-entry form, and referenced the system-details table, the system name is shown as the numerical ID. The reason I'm referencing the system-details table instead of the system-name table is I would like the other system details (classification, pocs, installation date, etc.) to auto-populate on the equipment-entry form.

Would I, perhaps, be better off referencing the system-name table (eliminating the ID/name issue) and using some kind of if/then to tell the other comb boxes what details to reference from the system-details table to auto-populate?

[alansidman]

Your explanation is a little confusing, but I think you need to have cascading combo boxes to achieve your expected results. Look at these two links for tutorials on cascading combo boxes.

http://www.fontstuff.com/access/acctut10.htm

http://www.techonthenet.com/access/c...ked_combos.php

and lastly, here is a link on combo boxes. Scroll down abit for the three tutorials on combos

http://www.datapigtechnologies.com/AccessMain.htm

If I missed the boat on what you are looking to do, post back and will try again.

I would like the other system details (classification, pocs, installation date, etc.) to auto-populate on the equipment-entry form.

This is redundant and contrary to normalization. Information should only be stored in one place. You can join the tables in a query to gain the information you need on classification, pocs, etc.

Alan

[SeaTigr]

Cascading combo boxes, unfortunately, won't do the trick.

In my database:
Systems: Table - where the details of a system are kept (name, installation date, etc.)
Systems: Form - where system info is entered/modified
Unit Type: Table - different unit types for the equipment table (desktop, server, etc.)
Operating Systems: Table (computer OS list)
Voltages: Table (110 or 220)
Classification Level: Table
POR Systems: Table (system names)
Equipment: Table (where info for individual pieces of equipment, which belong to a system, will be stored)
Equipment: Form (where users can enter/modify equipment information)

There are 11 systems, each with its own equipment. The system form is so information about the system (name, classification level, points of contact, etc.) can be entered/modified.

The equipment form is so information about individual pieces of equipment, which will belong to one of the eleven systems, can be entered/modified. One of the records for a piece of equipment will be the name of the system the equipment belongs to. What I would like to do is set up the equipment form so that all a user needs to do is pick which system the equipment belongs to and some other system information will auto-fill/auto-populate on the equipment form.

I created two separate tables, system and equipment, so that if a system's information changes, I don't need to change the data for every piece of equipment.