Additem Workaround in 97/2000

Additem Workaround in 97/2000

Post by spammaster » Wed, 08 Sep 2004 00:37:34

I found a common workaround for the lack of additem in Access 97 and 2000.
Tablebox is the combo box I'm populating. Fields are a little harder. You have
to have the table name then set the table's fields to a RowSource.

'''''''''''''''''''Acces 97/2000 additem replacement by Jim
Dim TB as Tabledef, str1 As String
Set TB = CurrentDb.TableDefs

str7 = TB(0).Name
For i = 1 To CurrentDb.TableDefs.Count - 1
If TB(i).Attributes = 0 Then
str1 = str1 & ", " & TB(i).Name
End If
Next i
Me.Tablebox.RowSource = str1 'Tablebox is the name of the combobox populated

Additem Workaround in 97/2000

Post by spammaster » Wed, 08 Sep 2004 00:49:13


str7 = TB(0).Name
should be

str1 = TB(0).Name


Additem Workaround in 97/2000

Post by Albert D. » Wed, 08 Sep 2004 04:52:03

Well, the best solution is to base the combo box data source on a table..and
then you can simply add values to that table.

Using a "string", or so called "value list" for a combo box is not very good
anyway. So, a good solution here is to use a table with the values..and
simply add to the table.

And, anytime you have a multi-user environment..then basing the combo box on
a table as opposed to using a value list is again better.

You actually have 3 ways to fill a combo box

1) Value list "one;two;three"

The problem with Value list is there is a limit on the number of entries
(about 4000 chars max). So, if we have a multi column combo, or lst box,
and each row is 40 characters...then you max out at only 100 entries.

2) table/query
This simply means the data source for the combo is a table (or usually a
query). There is no limits here. The main advantages here are that you can
set sort criteria (very hard to do with a value list), you can add values
easily, and of course new values added to the combo thus show for all users.

3) Call back function
This is little known, but it allows you to set the datasouce of a combo
box to YOUR custom function. That function can thus map the combo box to an
array, or reocrdset, or even generate values on the fly. It is fast, and
again has none of limits of a "value list"

There are some examples in the help, and also one here:

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
XXXX@XXXXX.COM ~kallal.msn