Display Combo Box Text Instead of Key in Table

[Guest]

I have a form with basic member information. One item is Status. I created
a table title Status listing the various types of status available and
inserted a combo box in my form that references this table. However, I store
the result in another table called claim information which contains
information related to that specific member (related by ID). If I view the
record in the claim information table, it shows the primary key from the
Status table (column 0) and not the value displayed on the form (column 1).
While I realize this is technically correct, I would prefer if the table
would show the value rather than a numerical key that has no meaning. This
would make trouble shooting easier. Is it possible? If it is but is not
adviseable, please explain

Thanks
Adrian

 

[Guest]

Adrian:

It can be done in raw datasheet view of the table by setting the Display
Control on the Lookup tab of the column's properties sheet to a combo box,
but I'd advise against it. In any database application the interface with
the data should be via forms or reports not directly with the datasheet view
of the table, which, for the rare occasions, e.g. when debugging, when a
developer might wish to view it, is better left showing the actual values
stored rather than those to which they map in another table. To save time
you can create a simple autoform very easily then amend its design, or you
can use the form wizard for more detailed control.

Rather than basing a form on the table per se its usually better to base it
on a query. That way you can for instance sort the query so that the rows
appear in a meaningful order, in your case ordered by LastName then FirstName
for instance.

As regards your specific problem use a combo box bound to the StatusID
foreign key column with a RowSource such as:

SELECT StatusID, StatusType
FROM Status
ORDER BY StatusType;

Set its other properties as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, Status, column, so only the text
StatusType shows)

In a report don't use a combo box (although it is possible) but base the
report on a query which joins the Members table to the Status table on
StatusID and bind a text box to the StatusType column from the Status table.

Ken Sheridan
Stafford, England

 

[Guest]

Adrian:

It can be done in raw datasheet view of the table by setting the Display
Control on the Lookup tab of the column's properties sheet to a combo box,
but I'd advise against it. In any database application the interface with
the data should be via forms or reports not directly with the datasheet view
of the table, which, for the rare occasions, e.g. when debugging, when a
developer might wish to view it, is better left showing the actual values
stored rather than those to which they map in another table. To save time
you can create a simple autoform very easily then amend its design, or you
can use the form wizard for more detailed control.

Rather than basing a form on the table per se its usually better to base it
on a query. That way you can for instance sort the query so that the rows
appear in a meaningful order, in your case ordered by LastName then FirstName
for instance.

As regards your specific problem use a combo box bound to the StatusID
foreign key column with a RowSource such as:

SELECT StatusID, StatusType
FROM Status
ORDER BY StatusType;

Set its other properties as follows:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, Status, column, so only the text
StatusType shows)

In a report don't use a combo box (although it is possible) but base the
report on a query which joins the Members table to the Status table on
StatusID and bind a text box to the StatusType column from the Status table.

Ken Sheridan
Stafford, England