Change table text column lookup list

[RobGMiller]

I must add a column using vba and need to change the lookup list.

Using:

Set tdf = DB.TableDefs("TableName")
Set fld = tdf.CreateField("FieldName", dbText, 50)
tdf.Fields.Append fld
tdf.Fields.Refresh

The FieldName column need a lookup list whose
Display Control is a combo box
row source type is a value list
row source consists of a list as in Item1;Item2;Itemn...


Can this be done using VBA when the column is created.

 

[Marshall Barton]

I must add a column using vba and need to change the lookup list.

Using:

Set tdf = DB.TableDefs("TableName")
Set fld = tdf.CreateField("FieldName", dbText, 50)
tdf.Fields.Append fld
tdf.Fields.Refresh

The FieldName column need a lookup list whose
Display Control is a combo box
row source type is a value list
row source consists of a list as in Item1;Item2;Itemn...


Can this be done using VBA when the column is created.

Well, since there is no good reason to ever dispay a table's
datasheet to users, you should never create a lookup field
in a table.

Tables are for storing data, form's and report are for
interacting with the data. A simple lookup table with your
items and a combo box on a form provides all the features
users need to do.

If you insist on using table datasheets as a user interface,
you will be providing users with a dangerous capabilty to
seriously make hash of the data. Just because it can be
done does not in any way imply that it should be done.

To create that kind of field you need to create and set all
the properties needed to get Access to display a combo box:

DisplayControl (acCombobox)
RowSourceType (Value List)
RowSource (Item1;Item2;Itemn)
BoundColumn (1?)
ColumnCount (1?)
ColumnHeads (False?)
ListRows
ListWidth (0twip??)
LimitToList (True?)

 

[RobGMiller]

Thanks for your reply Marshall,

I need to maintain several similar databases and this table level
functionality is useful to help perform that task. I thought it would be
simpler to change the structure of tables using code rather than performing
the same task manually many times.

In any case,

I tried the following which did create the field or column but did not
create the required lookup configuration. Perhaps the format of the property
values are wrong.

Set tdf = DB.TableDefs("RateBooks")
Set fld = tdf.CreateField("Status", dbText, 20)
fld.DisplayControl (acComboBox)
fld.RowSourceType ("Value List")
fld.RowSource ("DeActivateRBook;History;Signed;UsedRateBook")
fld.BoundColumn (1)
fld.ColumnCount (1)
fld.ColumnHeads (False)
fld.ListRows (8)
fld.ListWidth (100)
fld.LimitToList (True)
tdf.Fields.Append fld
tdf.Fields.Refresh

 

[Marshall Barton]

You have to use lookup fields because you already have
lookup fields??? That's just propogating a mistake.

Oh well. If you must, then you needd to understand that
these properties are not built in Jet properties, they are
added by Access when you use the table designer and use the
Lookup window. When you want to do it in your own code, then
you have to create the property and append it to the field's
properties collection If the property does not already
exist, See VBA - Help on the CreateProperty method.

 

[RobGMiller]

Since you asked....

If I have 50 databases to maintain and I find that adding lookup fields on a
certain table will help do the work. I'd rather run code to create the lookup
configuration than doing it manually.

Thanks for clarifying the createProperty requirement of your solution. I
guess I glossed over the word "create" in your explanation.


Thanks for your time Marshall.
--
RobGMiller

You have to use lookup fields because you already have
lookup fields??? That's just propogating a mistake.

Oh well. If you must, then you needd to understand that
these properties are not built in Jet properties, they are
added by Access when you use the table designer and use the
Lookup window. When you want to do it in your own code, then
you have to create the property and append it to the field's
properties collection If the property does not already
exist, See VBA - Help on the CreateProperty method.
--
Marsh
MVP [MS Access]

I need to maintain several similar databases and this table level
functionality is useful to help perform that task. I thought it would be
simpler to change the structure of tables using code rather than performing
the same task manually many times.

I tried the following which did create the field or column but did not
create the required lookup configuration. Perhaps the format of the property
values are wrong.

Set tdf = DB.TableDefs("RateBooks")
Set fld = tdf.CreateField("Status", dbText, 20)
fld.DisplayControl (acComboBox)
fld.RowSourceType ("Value List")
fld.RowSource ("DeActivateRBook;History;Signed;UsedRateBook")
fld.BoundColumn (1)
fld.ColumnCount (1)
fld.ColumnHeads (False)
fld.ListRows (8)
fld.ListWidth (100)
fld.LimitToList (True)
tdf.Fields.Append fld
tdf.Fields.Refresh

.

 

[Douglas J. Steele]

Presumably you realize that your users should never be interacting directly
with the tables, that there should always be forms for interactions with
tables. Given that, lookup fields buy absolutely nothing in terms of
productivity gain.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Since you asked....

If I have 50 databases to maintain and I find that adding lookup fields on
a
certain table will help do the work. I'd rather run code to create the
lookup
configuration than doing it manually.

Thanks for clarifying the createProperty requirement of your solution. I
guess I glossed over the word "create" in your explanation.


Thanks for your time Marshall.
--
RobGMiller

You have to use lookup fields because you already have
lookup fields??? That's just propogating a mistake.

Oh well. If you must, then you needd to understand that
these properties are not built in Jet properties, they are
added by Access when you use the table designer and use the
Lookup window. When you want to do it in your own code, then
you have to create the property and append it to the field's
properties collection If the property does not already
exist, See VBA - Help on the CreateProperty method.
--
Marsh
MVP [MS Access]

I need to maintain several similar databases and this table level
functionality is useful to help perform that task. I thought it would be
simpler to change the structure of tables using code rather than
performing
the same task manually many times.

I tried the following which did create the field or column but did not
create the required lookup configuration. Perhaps the format of the
property
values are wrong.

Set tdf = DB.TableDefs("RateBooks")
Set fld = tdf.CreateField("Status", dbText, 20)
fld.DisplayControl (acComboBox)
fld.RowSourceType ("Value List")
fld.RowSource ("DeActivateRBook;History;Signed;UsedRateBook")
fld.BoundColumn (1)
fld.ColumnCount (1)
fld.ColumnHeads (False)
fld.ListRows (8)
fld.ListWidth (100)
fld.LimitToList (True)
tdf.Fields.Append fld
tdf.Fields.Refresh

.