List, Combo, or something else

[JR Hester]

Access XP in WinXp environment

I am trying to stay away from lookup datatypes in my tables as I have read
so many are against this practice. Now I need add a lookup-like feature to my
data entry form. and can't seem to find teh right sequence to make it work. I
have tried both combo and list box types, but don't undersatnd where to put
the values I want to see.
underlying table has field "status", data entry form will have a field
"status" that I wish to display five options A, H, R, T, X to choose from.
Then have this selction stored to teh status field of the underlying table.
Isn't there some way to create the list box or combo box with these five
options OR do I HAVE to create another table with these five optins and link
my row source to that table?

Guidance greatly appreciated.

 

[Klatuu]

You are wise to avoid lookup fields.
What you want is not difficult. It can be done with or without another
table. You can bind a combo to the status field and use a Value List row
source type and manually create the value list. In fact, it would look like
this:
"A";"H"; "R";"T";"X"

But, I wouldn't suggest you do that.

If you follow the philosophy that code belongs in modules and data belongs
in tables, the the above method violates that rule. A, H, R, T, X are all
data items and therefore, should be in a table. Now, my guess is that these
status codes can be represented by a textual description a user who doesn't
know what the code represents means. The most correct way to do it is the
create a status code table. You will need two field in the table. One to
store the code and one to store the description:

tblStatusCode
StatCode - Text 1 long, primary key
StatDescr - Text - however long you need to describe the code

Now, for the combo you use a row source type of Table/Query and you use the
table as the row source. Then you need to set the following properties to
these values:

Column Count 2
Bound Column 1
Column Widths 0"; 1" ( the 0 will hide the first column (StatCode) and make
the Descriptoion 1 inch wide. You can change the 1" to whatever you need to
show the description.
Limit to List Yes

You can also set the Default Value property to one of the status code values
if you want that code to be used if the user doesn't select a different code.

The other thing to do, because Access error messages serve only to confuse
and frighten users, is to put a couple of lines in the combo's Not In List
event to present your own meaningful message if a user enters a value that is
not one of the codes in the list:

Private Sub MyCombo_NotInList(NewData As String, Response As Integer)

MsgBox "You must select one of the codes from the list", vbExclamation
Response = acDataErrContinue
Me.MyCombo.Undo

End Sub

That is all there is to it. Now a user can start typing in the description
and hit tab or enter when the description is displayed or they can drop the
combo down and select from the list. Because Column 1 is the bound column,
that is the value that will be assigned to the bound field.

That is the way it is done.

 

[JR Hester]

Thank you, I will proceed with teh additional table method.

 

[JR Hester]

Thanks for the ideas and suggestions. I created the additional table. MAde
the five entries into the table. Changed the text box to a combo box with
"Status" field in Employee table the control source and tblStatus the
rowsource. Set the Limit to list property to Yes; defined a default value of
"A"; Set bound column to 1; set column widths to 0";1". Everything works as
expected EXCEPT teh combo box does not SHOW any of the entries in the status
table. I get the expected errror if an entry is made that does not reside in
the list so that part is working.

What did I miss that is preventing the combo box from displaying the
choices? ( by the way my tblstatus contains two fields ID( one character
alpha and is Primary key) and statusname, which is what I want to be
displayed for the operator.

Thank you.

 

[JR Hester]

OK, OK, I am having a heck of a time removing this egg from my face. Took one
more look and found one of my errors-- Number of columns for the combo box
was still set to 1, changed that to 2 and now my list displays and I can
select as expected. Noticed howeever that my default entry is being ignored
now. Is that standard operating procedure when using the combo box and
pulling from a table?

Thanks again

 

[NKTower]

You might consider these suggestions:

a) Add two columns to your status table:
defaut_value - yes/no data type - set ONE row to TRUE
sort_order - type int - put in numbers such that you can control the order
of the status display list in the combo. The default
order is to use the primary key which may not be the sequence you want. If
sorted by key you might get something like this
c code 30
d design 20
i install 60
r request for proposal 5
s specifications 10
t test 40

(Although now that I think of it, I think have seen systems built in this
sequence.)

Your rowsource for the combo can then reference the 'sort_order' column.

In the Form_Open() event, so a quickie query to find the value of status
that has default_value = TRUE. Save it in a variable.

In Form_Current() and/or Form_BeforeInsert(), take the value of the default
value variable and stuff it into the status field (if it IsNull() )