Change table text column lookup list

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
0
Utf
3/17/2010 2:44:02 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

5 Replies
851 Views

Similar Articles

[PageSpeed] 14

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]
0
Marshall
3/17/2010 4:17:34 PM
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]
> .
> 
0
Utf
3/17/2010 7:49:02 PM
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

0
Marshall
3/17/2010 11:16:28 PM
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
> 
> .
> 
0
Utf
3/18/2010 2:44:26 PM
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" <RobGMiller@discussions.microsoft.com> wrote in message 
news:6F7FF527-C120-42D4-8363-AE127851D451@microsoft.com...
> 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
>>
>> .
>> 


0
Douglas
3/18/2010 4:10:25 PM
Reply:

Similar Artilces:

normalizing a column of numbers
How do you normalize a column of numbers using a chart? I know how to do a chart but not how to interpret it for normalizing Mason, Use a helper column where you normalize your numbers using an appropriate formula prior to charting, and then chart that column with your normalizing formulas. HTH, Bernie MS Excel MVP "mason" <anonymous@discussions.microsoft.com> wrote in message news:81F1FB07-419B-4E41-87D3-25DB75E1EEDC@microsoft.com... > How do you normalize a column of numbers using a chart? I know how to do a chart but not how to interpret it for normalizing. > &...

External data link change
Hi, I've a sheet linked to an external data on the net, and I would like that a cell in this sheet to indicate the last date and time it was updated, the simplest way the better but I can do some programming too. Thanks for your attention, -- Domingos Junqueira No need of help any more, I solved the question. Thanks again ...

Import from Notepad delimited text
Is it possible to import from Notepad . There is a table with pipe delimiters that is automatically generated in a .dat text file using Notepad which I would like to import / link into my database daily. This .dat file arrives via email each day as an attachment. I tried saving the .dat file into a .txt extension but ACCESS wouldn't recognize it . I currently need to use the excel text wizard everyday to convert this file into an .xls format . I then have my access 2003 database linking to this ..xls file. Although I know how to convert this .dat file there are others ...

outlook 2003- cant send to some distribution lists
User was upgraded from office 97. the PST was moved at that time. Fast forward several months... user hit the 2gb limit on her PST file. we created a new OL2003 PST, then imported all info from the old PST. Now whenever she tries to send to certain distribution lists, she gets a "unexpected error has occurred". when she hits send. if she tries to expand the distribution list within the TO field, she gets an error of "The command selected is not valid for this recipient. The operation failed." she can open it in contacts with no errors. We have deleted the list and...

Why does the change change to a number?
Hi all, I've noticed something wierd and always wondered WHY it happens. When you type a date into a cell, and then change the Formatting of it to a 'general' cell, it turns into a number. How does it come up with that number? What is the significance? i.e. type today's date of "12/7/2007" - change it to a 'General' format, and it then says "39423". I'm a trainer of Excel and this question always comes up. I'm curious myself too. Thanks! Joe It's the number of days since January zero 1900 using Excel default for windows (M...

Help with SQL Statement for pivot table
I'm trying to put a sql statment from an access query into a pivot table query builder. I built the query in access then took the sql view and tried to put it into the excel query builder -- not working. Access put dbo_ in front of all of the table names so I took that out but it's got other problems too. Not sure how the date range should be specified and I wanted it to search a text field for the word "implant". In access I did this by specifying *implant* but sql doesn't seem to like it. Here's the statement: SELECT ApptPending.CreateDate, ApptPending....

Saving a Pivot Table
I'm trying to save a pivot table into an older format from Excel 2007. I receive a message stating that the pivot table is too large for the old format. But if I create the pivot table in Excel 2003, with same data source, I don't get any message and the pivot table is generated. Something seems to be wrong with 2007. Are there any solutions to this? ...

Track Changes
How do i remove the track changes in outlook? When i press "Enter", a symbol that represents "Enter" will appears. Same for space etc. ...

DST changes for Exchange 5.5
I noticed the 2007 DST Calendar Update "Exchange tool" is available now: http://support.microsoft.com/kb/930879 This will seemingly take care of calendars for mailboxes still on Exchange 5.5 servers, as Exchange 5.5 is listed as "compatible" . However would this address the CDO issues such as BlackBerry users and OWA users still on Exchange 5.5 ? Thanks in advance, Itrcb4 On Mon, 12 Feb 2007 14:31:00 -0800, itrcb4 <itrcb4@discussions.microsoft.com> wrote: >I noticed the 2007 DST Calendar Update "Exchange tool" is available now: > >http://su...

Sum numbers in a column beyond a certain row, how to?
How to sum numbers in a column beyond a certain row? hi, i don't know if i understand your question correctly, =Sum(A1:A10) -- isabelle Le 2012-02-15 01:25, marsup a �crit : > How to sum numbers in a column beyond a certain row? On Feb 17, 11:30=A0am, isabelle <i...@v.org> wrote: > hi, > > i don't know if i understand your question correctly, > > =3DSum(A1:A10) > > -- > isabelle > > Le 2012-02-15 01:25,marsupa =E9crit : > > > > > > > > > How to sum numbers in a column beyond a certain row? Thanks Isabelle, I a...

changing the way Excel displays selected cells
Is there a way to change the way Excel displays selected cells? I'm interested in viewing all the selected cells highlighted (with light blue for instance), but by default excel highlights all the cells but not the first one the same occurs when you define a range with no adyacents cells Your definition of the display is not quite correct. Excel highlights the current cell, Excel also highlights a selecte range. The currently selected cell is generally the first of a range, bu press enter and the current cell changes and becomes the second, the third etc in the range. You cannot...

more on VBA function name change
I thought I'd start a new thread since I haven't received any replies to my first one... To recap: I've declared a function in a module using mixed case: Function TMDE_Category (FormName As Form) I noticed recently that it appeared in the module as Function tmde_category(FormName As Form) I changed it back to the mixed case declaration, saved the module, exited the app, reopened it and looked. The function had changed back to the all lowercase declaration. Things I've tried since the original post: Using the databse documenter, I selected all ob...

Can't Publish changes with Deploy Manager
After a migration process, I can't publish the changes made on CRM. When I try do this in Deploy Manager I get the follow error: ---------------------------------------------------------------------------- ----- Publish done with errors. See the event log to get deitails NETRA-INOVACAO: ***Error*** Failed to download XSL template files from Web Server ---------------------------------------------------------------------------- ----- Can somebody help me? I don't know if the migration process have any relationship with the error. Thank you for pay attention. []'s Vin�cius Pitta...

Convert dates stored as text
I have Excel 2007 in English, but I sometimes receive data that comes from another applicationes, so dates are stored as text because they come in following format: dd/mm/yyyy. And I have the format mm/dd/yyyy. So, in the same column, I have dates stored as dates, and dates stored as text. Which is the easiest way to convert them all to date format? Thanks in advance. Regards, Emece.- Be careful. I'd bet that those values that come in as real dates aren't what the original data represent. For instance, if you have two values: 25/12/2010 and 01/02/2010 The...

Combine variable number of columns
I have a single spreadsheet with a list of clients, addresses and their product interests. This table will be used to drive a Mailmerge document. In the document, I want to be able to refer to the products in which they indicated an interest. The problem is that while one client may have identified only one product (one column) others have selected anywhere from two to 170 products - each product is in a separate column. I can join two columns with "&", but when I have an inconsistent number of columns how do I do this efficiently? - I will have to do it for each client, e...

How to change icon for my application
Hi, I am currently developing an application on visual studio 6.0, and i wish to change the MFC icon on my application header. Anyone can help? Thank you. Raed Sawalha wrote: > Hi, I am currently developing an application on visual studio 6.0, and i > wish to change the MFC icon on my application header. Anyone can help? Thank > you. > > Open the icon resource for editing by double clicking. Then notice the control just above the editing grid that lets you switch between editing the large icon and editing the small one. -- Scott McPhillips [VC++ MVP] thanx that work...

Newgroups Reseting List
When I try to reset my news group list it goes to almost 30,000 and then the program stops responding. Must shut down and restart explorer. Any suggestions? Thanks, Gordon Outlook doesn't provide NNTP (network news transfer protocol) support. Neither does Explorer. Neither does *Internet* Explorer, which is maybe what you meant. You use Outlook Express for that (Outlook and IE can call it or you can load OE yourself). [Internet] Explorer is not an NNTP client so I don't know why you are restarting it. If you are using Outlook Express, repost your question in a newsgroup that d...

How to change newsgroup message font
Like many other ribbon based programs I sometimes struggle to find how to make a change. This time its how to change the font just for newsgroup messages? regards "nobody" wrote in message news:EWTao.39493$GF5.7129@hurricane... > Like many other ribbon based programs I sometimes struggle to find how to make a change. This time its how to change the font just for newsgroup messages? Newsgroup messages are usually plain text. The font used is that selected at [no name tab] > Options > Mail > Read > Fonts for the encoding specified for the messag...

Pvt Table Question #2
Hi, I'm in excel 2007. I have a couple of really big sheets and would like to make a pvt table. My question is - is it possible to make a pvt table using two sheets? If so, how do you do this? Thanks! Alt/DP, then choose Multiple consolidation ranges -- the UI was removed from the ribbon. "lj" <lj@spu.edu> wrote in message news:a137474c-7ea2-4c35-a9ab-43af7056c140@x38g2000yqj.googlegroups.com... > Hi, > I'm in excel 2007. I have a couple of really big sheets and would > like to make a pvt table. My question is - is it possible to make a > pvt table...

Border with text
I'm trying to make a border using text. I want to repeat the same phrase over and over again. Idealy, I'd like the words to make a rectangular border for a sign... and when it hit the corner of the page, the phrase would continue down the next angle, and so on, until it made a box. Does anyone have any ideas on how to do this? WordArt would be your only option. If you had a draw program... Serif has a free draw application. http://www.freeserifsoftware.com/ OpenOffice is free and has a draw feature. www.openoffice.org I have a method using WordArt on my web site that might be...

Pivot Table showing wrong data
I have a table with information such as Salesperson, Customer, Sales $, Year, Month, Date_Customer_Started. The data contains info for 2009 and 2010. I am trying to determine for every new account created in 2009 and 2010, Sales by new customer and salesperson. The Pivot table has sales data and count of customers by year and the rows contain Salesperson, Customer, Month and date_Customer_started. I expect if a customer started in April 2009, and the customer continuing to buy in 2010, sales figures by year and month and the count of customer only in 2009. But the pivot table sh...

How do I replace the last entire word in text cells?
I understand the REPLACE command but it seems restricted to characters or bytes. I this case, I want to replace the last word in all cells but the word is always different (ie. varying number of characters). Thanks in advance! Array entered (CTRL+SHIFT+ENTER), this will work: =LEFT(A1,LEN(A1)-MATCH(" ",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0)) Change the reference to A1 to which ever cell you want. -- Regards, Dave "George" wrote: > I understand the REPLACE command but it seems restricted to characters or > bytes. I this case, I wan...

Notify change of email address when changing ISP
How do I notify my entire address book of an email address when I change ISP's? Thanks By sending a mail to everyone ? If you do so, please use BCC for the adresses, this way you won't spread everybody's e-mail adres to everybody. Niels Bob Baker wrote: > How do I notify my entire address book of an email address when I change ISP's? > Thanks ...

Change a formula to an acual number
I want to change the formula I have created to the number it has created Example: Cell A1 is the number 1. Cell A2 is the formula =a1+1 creatin the number 2. I want this to be a two not a formula. Any ideas? Laura, copy, then paste special, valves -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Laura" <anonymous@discussions.microsoft.com> wrote in message news...

How do I use cond format formula to set cell text and color?
Using MS Excel 2003: As and example, I entered into Conditional Format for cell B1: "Formula is", IF(A1="","Test",""). For true condition, the format is set to fill Yellow and font color red. I'm expecting cell B1 to display, "Test" in red text with yellow background when A1 is blank, but it does not work. No errors are displayed. Any ideas? in my experience you can only set the format,you cant have the word test show up.Apply the format condition only using =A1="" and put a seperate if statement in the cell.If it blank ...