Value lists in combos

[CW]

Two questions regarding the use of value lists in combos:
1. Is there a limit (either practical or absolute) on the number of values
that can be used (saved in Row Source) on combos?
2. Other than the (presumably much greater) number of values that can be
displayed, is there any particular advantage to using a table for looking up
the available values, compared with using a Value List? A value list seems so
much simpler but I'm wondering if there is a downside that I haven't thought
of...
Many thanks
CW

 

[Ken Sheridan]

I'd recommend that you only consider using a value list for sets of values
which are immutably fixed in the real world, such as days of the week, months
of the year etc. In most cases the items in a combo box's list are data, and
a fundamental principle of the database relational model is that data is
stored as values at row positions in columns in tables and in no other way –
its known as the Information Principle and was Codd's Rule 1 when he first
put forward the database relational model in 1970.

As well as the theoretical objections a value list of data requires an
amendment of the form definition if the list needs amendment, whereas drawing
on a table merely requires the addition, deletion or editing of values in a
table.

One area where a value list can be useful is for listing objects such as
reports for the user to select from. In the past we'd have used a call-back
function for this, but nowadays calling the AddItem method makes it very
simple.

Another time it can be useful is in providing a variable list of values such
as dates in a range. The following code for instance fills an unbound combo
box's list with dates 10 days before and after the current date and sets the
control's value to the current date:

Private Sub Form_Open(Cancel As Integer)

Dim n As Integer

Me.cboDates.RowSourceType = "Value List"

For n = -10 To 10
Me.cboDates.AddItem DateAdd("d", n, VBA.Date)
Next n

Me.cboDates = VBA.Date

End Sub

As far as any limit on the number of items in a value list is concerned I
don't know the answer to that, but I'd imagine that any limit is more likely
to be on the length of the string expression rather than the number of items
per se. I'd regard the question as academic in any case, as any list which
is, on the basis of what I've said above, appropriate to a value list is
unlikely to be of very great length.

Ken Sheridan
Stafford, England

 

[CW]

That's very helpful - many thanks Ken.
CW

I'd recommend that you only consider using a value list for sets of values
which are immutably fixed in the real world, such as days of the week, months
of the year etc. In most cases the items in a combo box's list are data, and
a fundamental principle of the database relational model is that data is
stored as values at row positions in columns in tables and in no other way –
its known as the Information Principle and was Codd's Rule 1 when he first
put forward the database relational model in 1970.

As well as the theoretical objections a value list of data requires an
amendment of the form definition if the list needs amendment, whereas drawing
on a table merely requires the addition, deletion or editing of values in a
table.

One area where a value list can be useful is for listing objects such as
reports for the user to select from. In the past we'd have used a call-back
function for this, but nowadays calling the AddItem method makes it very
simple.

Another time it can be useful is in providing a variable list of values such
as dates in a range. The following code for instance fills an unbound combo
box's list with dates 10 days before and after the current date and sets the
control's value to the current date:

Private Sub Form_Open(Cancel As Integer)

Dim n As Integer

Me.cboDates.RowSourceType = "Value List"

For n = -10 To 10
Me.cboDates.AddItem DateAdd("d", n, VBA.Date)
Next n

Me.cboDates = VBA.Date

End Sub

As far as any limit on the number of items in a value list is concerned I
don't know the answer to that, but I'd imagine that any limit is more likely
to be on the length of the string expression rather than the number of items
per se. I'd regard the question as academic in any case, as any list which
is, on the basis of what I've said above, appropriate to a value list is
unlikely to be of very great length.

Ken Sheridan
Stafford, England

 

[Klatuu]

Here is a very good article that would include the subject of your question:

http://weblogs.sqlteam.com/jeffs/archive/2006/02/10/9002.aspx