Invalid value error-dropdown

[Danny]

Hi,
I created a new dropdown field called outcome which includes two choice items. When attempting to select one of them, I received “The value you entered isn’t valid for this field” error. Attached is my design screenshot. Do I need to define my field outcome as a text data type?

TIA
Regards,

 

[DHookom]

It seems you are attempting to store a text value in a numeric field. Rather than a Value List, consider using a small lookup table to store the drop-down values. These tables typically have a numeric primary key (autonumber) that gets stored in the main tables

 

[June7]

Agreed.
Your options:
1. define field as text type
2. keep number type but use a value list that saves a number while showing text
set combobox for 2 columns with widths 0";1" and ValueList of 1;LEE;2;Withheld
This means will have to use combobox on report (will look like textbox) or an expression in textbox on report to convert number to text equivalent.
3. keep number type but use a table as RowSource
again, set combobox for 2 columns and widths 0";1"
include this table in reports to retrieve text equivalent

 

[Ken Sheridan]

The values LEE and Withheld are data. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way.

I would therefore recommend that you store the values in a referenced Outcomes table with columns like this:

OutcomeID  Outcome
1          LEE
2          Withheld

The OutComeID column should be the primary key, and can be an autonumber for convenience. Set the combo box's RowSource property to:

SELECT OutComeID, OutCome FROM Outcomes ORDER BY Outcome;

Set its BoundColumn property to 1, Its ColumnWidths property to 0cm, and its ColumnCount property to 2.

This will hide the first column so that only the text Outcome values are listed. When a value is selected the property of the control, and hence of the column to which it is bound will be the value of the numeric first column.

An enforced relationship should be created between the referencing table to which the form is bound and the Outcomes table.

 

[Danny]

Hi,
Thank you all for your feedback. If I understand it correctly, I have to start from scratch and create the "outcome' table, correct?
Also, if I had to use options button or check box for Outcome, do I still need to create the Outcome table?

An enforced relationship should be created between the referencing table to which the form is bound and the Outcomes table.

how do I go about accomplishing this?

TIA
Regards

 

[arnelgp]

on Table design, change Outcome to Short Text.

 

[June7]

Options have been described. Make a choice.
Table would not be required for option buttons or checkbox.

 

[Ken Sheridan]

how do I go about accomplishing this?

To create a relationship firstly add both the Outcomes table and the referencing table to which your form is bound to the Relationships window. Then drag from the OutcomeID primary key of Outcomes to the Outcome foreign key column in the referencing table. Then right click on the line which joins the two, and select Edit Relationship. In the ensuing dialogue check the Enforce Referential Entity check box to enforce the relationship.