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.
--
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


--
RobGMiller


"Marshall Barton" wrote:

> RobGMiller wrote:
>
> >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?)
>
> --
> Marsh
> MVP [MS Access]
> .
>

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


"Marshall Barton" wrote:

> 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]
>
>
> RobGMiller wrote:
> >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
>
> .
>

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!)

"RobGMiller" <(E-Mail Removed)> wrote in message
news:6F7FF527-C120-42D4-8363-(E-Mail Removed)...
> 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
>
>
> "Marshall Barton" wrote:
>
>> 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]
>>
>>
>> RobGMiller wrote:
>> >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
>>
>> .
>>