Ordering List Box Values

[Guest]

I have a main table tblPlants and wanted to populate several fields via
listboxes. I used separate tables (e.g tblLightConditions,
tblMoistureConditions, etc) rather than typing a value list. I want the
values to show up in the listbox in a specific, but somewhat arbitrary,
order. For example, I want the LightConditions listbox to show, in this
order, Full Sun, Part Sun, Part Shade, Deep Shade.

The best way I've come up with to do this so far is to have a numeric
SortNumber field (which is different from the autonumber key), then use a
query sorted on that as the row source for the listbox.

Is there a smarter or easier way to do this? Maybe I should just type up the
value list?

Thanks for any advice.

 

[Albert D.Kallal]

The best way I've come up with to do this so far is to have a numeric

SortNumber field (which is different from the autonumber key), then use a
query sorted on that as the row source for the listbox.

That sounds like a *excellent* solution. I don't think I could come up with
a better solution...

Is there a smarter or easier way to do this? Maybe I should just type up
the
value list?

Well, in both cases, you have to type in the list. The beauty/advantage of
the table is now you can add more to the list, and NOT have to open up the
application in design mode. I mean, I don't think a company would be too
happy
if they had to call in a developer to add a extra value to a list?

The other advantage of this table is that you now have a READY made list.
What happens if latter on you build a nice report prompt form, and want the
user to select the particular "light condition" for a report? If you have a
table with the list, and you can build a "nice" looking prompt screen that
allows the user to select the light condition. (ie: you don't have to
"repeat" the value list over and over each time/place you use it in your
application).

Here is some screen shots of what I mean:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

Further, you might want to later on add a "description" field that further
explains what the 4 choices mean. You could simply add another "description"
column to the table. So, over time, you can add more light conditions
without having to write/change code or your application. And, those
"description" fields could then show in a report etc.

Thanks for any advice

Your current design decision seems to be a good one...I would stay the
course...

 

[Arvin Meyer [MVP]]

Using a value list limits your choices and more especially user choices. A
table allows the addition of more choices with relative ease.

Adding a sort field is the most common way to do custom sorting.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access