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
964 Views

Similar Articles

[PageSpeed] 23

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:

Trying to change txt color from message map function
HI, My menu has a option that changes the text color. so I have my OnPaint() function but I am trying to call the DC from another function in same class.. what am I doing wrong? void CChildView::OnTextBlack() { // TODO: Add your command handler code here CDC* pDC = GetDC(); pDC->SetTextColor(RGB(0,0,0)); pDC->TextOut(mPt.x, mPt.y, mStr); ReleaseDC(pDC); mCount = 1; Invalidate(); } I changed my approach to ON_COMMAND_RANGE... and came up with this... it worked... but why not the first code? void CChildView::OnTextColor(UINT nID) { CClientDC dc(this); switch(nID) { ca...

256 column limit
hi, I have read all the posts on the 256 column limit. irritating. i only want about 500 columns and about 200 rows - could i transpose the data, but view it reverse transposed - if you know what i mean. i don't want to read the data down, but across. i am assuming the limit is to control the overall number of cells? this would not affect that. ellebelle Next version of Excel has 16384 columns and 1048576 rows but until it's released you can't do anything about it except using another layout -- Regards, Peo Sjoblom "ellebelle" <ellebelle@discussions.mic...

Multiple stacked columns
Hiya, I want to create a stacked column chart comparing 2 sources of data: Stack1 (Data) Stack2 (Budget) Product A Product B Product C Product A Product B Product C Q1 10 50 12 15 45 15 Q2 14 55 11 16 50 14 Q3 23 43 9 20 40 10 Q4 1 4 0 ...

Drop-down Lists
I created a drop-down list...works fine. Now I'm looking to create a feature whenever one of the items is selected (from the drop-down list) that data from another sheet (same workbook) is displayed. Thanks. It depends how your data is organised, but you could use VLOOKUP( ) to return data from another sheet - you might need several formulae across a row to return several cells of data, each dependent on your choice from the drop-down list. Hope this helps - if you need further help please re-post with some further details. Pete ...

How do I unhide The first column or row
After hiding the first column or row in Excel, how do I unhide it One way: Enter A1 in the Name Box on the left side of the formula bar and type Enter. Choose Format/Row/Unhide or Format/Column/Unhide In article <26178EB3-8474-4C26-9087-75487987BBFB@microsoft.com>, ashekumar <ashekumar@discussions.microsoft.com> wrote: > After hiding the first column or row in Excel, how do I unhide it Another way - click in the column B label and drag into the empty box above the row numbers, then right-click and choose unhide same process to unhide the first row, but drag fr...

Need to Change Column of Telephone numbers to just 10 characters
Hi I imported data from another program into excel. The telephone numbers are in the following format 333 333-3333. How do I change it to just having 10 digits without spaces of hyhens. I need it in this format to export to another system Jen Edit>replace find what -, leave replace with blank, then replace again and this time put a space in the find what and replace with nothing -- Regards, Peo Sjoblom "Jennifer Leen" <anonymous@discussions.microsoft.com> wrote in message news:7923E5C4-B199-464F-B34D-9B3B2C726376@microsoft.com... > Hi , > > I imported d...

how do I enter a list in a list box or a combo box
hi i am trying to enter a list of names into either a list box or a combo box but i can not find out how to do it... can anybody help please... On 31/05/2010 11:42 AM, word challenged wrote: > hi i am trying to enter a list of names into either a list box or a combo box > but i can not find out how to do it... can anybody help please... See the following page of Greg Maxey's website: http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm If it's an ActiveX combobox that you haved inserted directly into the document, right click on it and select View Code and use c...

lookup with 2 criteria
I'm working on utility data for a chain of stores. I need to lookup an account number based on both facility # and meter # b/c some facilities have multiple meters. This formula only matches the account # and does not cross reference meter #. =VLOOKUP(A96,Gaps!$A$8:$D$2102,3) A96 = fac # on sheet 2 Gaps!A8:D2102 = where data is kept On gaps page, fac # is column A, account # is column C, meter # is column D Thanks in advance for any words of wisdom! Hi try the following array formula entered with CTRL+SHIFT+ENTER): =INDEX(Gaps!$C$8:$C$2102,MATCH(1,(Gaps!$A$8:$A$2102=A96)*(Gaps!$D$8:...

Comparing two tables
hey just a short question :-D I have two tables, one with a list with 1330 entries and one with 720 entries, the 720 are all in the big list as well, is there a way to write the 610 entries from the big list that arent in the small list into another excel table without doing it one by one ? thanks in advance Peter PEter, try to use the "VLOOKUP" function...it will help you to identify with values are in both tables (sheets)... so you can mark the values that are in both tables and separete then easily... i don´t know if you know the VLOOKUP function...if you don´...

how to change from mayusc to minusc?
Hi there, I need help on how to change for mayusc to minusc on excell 2007. many thanks!!! Tienes que preguntar: How do I change from Upper to Lower case. "Ivo - Spain" wrote: > Hi there, > I need help on how to change for mayusc to minusc on excell 2007. > many thanks!!! Yo no se mucho de Excel, pero no dudo que sea igual que Word, toca la tecla "Caps Lock" que es la que cambia de may. a minus. y viceversa. "Ivo - Spain" wrote: > Hi there, > I need help on how to change for mayusc to minusc on excell 2007. > many th...

Unable to hide columns
I am trying to hide some columns which have no data, but get a message: "Cannot shift objects off sheet" I'm not trying to delete the column, simply trying to hide it. And I can't find any objects in the column in the first place. I appreciate any explanation Thank you, Tony Bender Do you have any Comments in the cells? See this KB Article http://support.microsoft.com/default.aspx?scid=kb;en-us;211769 Gord Dibben MS Excel MVP On Wed, 17 Mar 2010 10:19:30 -0700 (PDT), Tony Bender <tony_bender@yahoo.com> wrote: >I am trying to h...

Change Data Connection Definitions from local to sharepoint
Hi, I am using Excel 2007 and have created a query tool/workbook that contains a linked table worksheet from sharepoint as the source data (its a custom view/subset of the records data). This worksheet is refreshed from the sharepoint source periodically. I created this tool/workbook on my desktop, and now want to put it on the sharepoint location for multiple users. However, the Data Connection definition for the linked worksheet is showing the local address of my pc, so the data refresh won't work once the workbook is posted to the sharepoint. I have not been able t...

Change Directory Name in Exchange 2003, Windows Server 2003
The directory name that appears in exmerge does not match the user account name and I would like to know how to change it. Another administrator created an account in Exchange 2003. The display name is Jane Smith, the login is jane@domain.org and the e-mail is janes@vera.org. When the account was created, the word test was put in one of those fields (which one I am no longer sure and it was changed after creation to the names above, although test@domain.org is still one of the e-mails for this user but not the default). When I use exmerge the directory name that appears for the accou...

Username not in Global Address List
Hi all, I have added several user accounts on my Exchange 2003 server. All of them would show up in the Glabal Address List (GAL) except the last user I added. I am going to called it UserX. This last user account would show up in all the users' workstations except one station. Let's call it StationY. This particular client at StationY would see all the users in the Global Address List except UserX. In other words, UserX doesn't show up in the GAL on StationY. What should I do in order to bring UserX to show up in GAL on StationY? I appreciate any help on this. Thanks, ...

FE-BE Server Distribution Lists
Hello, I have a front end 2003 exchange server and a back end 2003 exchange server. I have an SMTP virtual server accepting in mail, and the SMTP connector configured with the FE VS as the bridgehead. My problem is when people send to global distribution lists from outside of the organisation they sit in the FE SMTP Mailbox and cause a x.400 message loop.. I have managed to research this problem to discover if I go in and manually set the distribution group expansion server to the BE, then people sending to the distribution group works fine, otherwise doesnt work. As mentioned this is a p...

Change Row Fill Color
I'm having a problem that I'm pretty sure can only be resolved with some type of Macro. I would like to know if it is possible to change the fill color of an entire row when text is entered in a specific cell within that row. For example. When "Not Received" is entered in cell C3, I would like all of row 3 to be filled with light blue. If something else is entered in that same cell (e.g. "Received"), I do not want the fill color to change at all. I know it's possible to change a single cell this way using Conditional Formatting, but how do you apply...

Take the space out of text?????
I have exported some information into a excel spreadsheet that I will be importing into another program. Unfortunately one of the colums has a space between the text that I need to remove. Example: how cell looks now: abcde fghij how I need it to look: abcdefghij Unfortunately it is not a small worksheet. It has 5863 rows that need to be changed. PLEASE!!!! PLEASE!!!! HELP!!!!!! If anyone can help I would greatly appreciate it!!!!!!!!! Thank you, Nichole --- Message posted from http://www.ExcelForum.com/ Hi you have already posted this. Have you read the answers ?? -- Regards Frank...

Changing PORT of Default CRM Site
I recently installed 3.0 and installed it in the Default web site, which is obviously Port 80. I did some port forwarding in my firewall to make it acccessible from the outside of our network and need to change the Port. I changed it in IIS but evidently there is another location that also needs changing, as the CRM errors when trying to load data. Any ideas or suggestions? Thanks Hi Stan, In the registry, HKLM\software\microsoft\mscrm "Stan" <Stan@discussions.microsoft.com> wrote in message news:BD5A13DB-58AE-4A11-82A4-DEA25B89C966@microsoft.com... >I recent...

At each change in data apply formula
Is there a function/ code that can look down a column and apply a formula when there is a change in data. i know there is the subtotal function but this does not work for my requirement. Using the following data I would want to automatically look at where the data changes (in column A) and apply a formula in another column (say Column B) for the same row. I would therefore expect to apply the formula as per the following example to cells B1, B4, B5 and B9 Col A row 1 AAA row 2 AAA row 3 AAA row4 BBB row5 CCC row6 CCC ro...

Auto Fill/Auto Lookup??
I have created a form to input new data into the database. The data table for the database is called "TAODailyLog". In this table the column headings are Date, Site ID, Buoy ID, WMO ID, and Deploy Date (there are others, but these are the important ones). I have another table called "siteid". This table I use primarily for looking up values. In the "siteid" table, the columns are Order, SiteID, pmID, wmoID, and juliandeployed. With each new record that is placed into the database, the user must put in the Site ID, Buoy ID, WMO ID and the Deploy Date. ...

Impact of changing database column width?
Hi, We are a software product company and are planning to begin using the Contract Administration module. To do so, we will be switching our inventory items from Track: None to Track: Serial Numbers and we will be storing our software license keys in the Serial Number field which is column SERLTNUM in table SOP10201. Unfortunately, SERLTNUM is CHAR(21) and our license keys are a few characters wider than 21. We can get the keys down to 21 characters by removing three embedded hyphens, but there are several databases outside of GP where the license keys must still exist with...

CRM 1.2 and Change Domain
Hi, I know Microsoft CRM is tightly integrated to Active Directory and Windows Domains. However, we've been acquired by another company to which we need to merge our systems to their AD. Our CRM has a lot of data and our current Windows domain should go after every systems are migrated. Does anyone ever tried a domain migration with a Microsoft CRM installed ? Do we need to export everything and then reimport everything? Thanks for your help! Sylvain Belanger Well, I would definately make sure you do a complete backup prior to doing anything. Depending on how they merge the Forests...

Importing Text Files #2
Anyone know how to create an overflow for text files? I import massive amounts of data from a text file into Excel, and I always must do this several times after opening text file and deleting top 62000 lines. I found a macro online once, and had it set up on my computer, but have since reformatted and lost the macro. Anyone know where to find this macro again? Or know how to force the import to continue on a second sheet? THank you in advance for any help..... Adam HI, Play with this code - it'll ask you how many lines to put on a page before adding a new page and filling it u...

Making legend (or textbox) move as data changes
Hi I'm trying to make an XY scatter chart which my users will be able to change, just by changing the data in the data ranges. The chart contains several curves each having its own legend/text box, placed at the right end point of the curve. When the data changes, I need the legend/textbox to move along with the end point of the curve (colors and different line styles are not an option). I have tried to group the curve with the legend/textbox but it doesn't seem to work. Any ideas? mr Hi, Maybe these examples will help. http://peltiertech.com/Excel/Charts/LabelLastPoint.html h...

Handling the System Image List ?
Hi guys, In my program I made a Shell Namespace tree viewer that needs the system imagelist which I handle like this: // let's get the image list SHFILEINFO sfi; ZeroMemory(&sfi,sizeof(sfi)); HIMAGELIST hImageList=(HIMAGELIST)SHGetFileInfo(_T("C:\\"),0,&sfi,sizeof(SHFILEINFO),SH GFI_SYSICONINDEX|SHGFI_SMALLICON); // attach it to my class member m_imageList.Attach(hImageList); // later on in my destructor I free it m_imageList.Detach(); I think I am doing it right because it works. The thing is, I want to use that tree I made in different spots but it seems I can'...