Changing the Selection Fields in a Combo Box

Hi,
I've created a Combo Box using the Controls Wizard.  In creating the 
control, I specified that my unique value to be stored in the table is an ID. 
 When the user views the selection options in the Combo Box, I'd like to have 
the ID field hidden and only see the text values, while still storing the ID 
in the appropriate column.
This is probably a second question.
Also, when the user views a form that has been populated, I'd like them to 
see the actual textual values on the form, as just the stored ID value.  Is 
that possible to do with the one Combo Box or is that two controls?  If it's 
two, how do you not see the empty second control when initially populating 
the form?
0
Utf
4/5/2010 2:11:01 AM
access 16762 articles. 3 followers. Follow

4 Replies
622 Views

Similar Articles

[PageSpeed] 0

The bound column (ID) should be the first one and all you need to do is to 
set the column width to 0". If you have more than 2 columns, and want to see 
them when the combobox is not dropped down, you'll need to use unbound 
textboxes and set the controlsource to the column index, like:

= MyCombo.Column(2)

for the third column. Any columns that you do not want to see, set the 
column width to zero, so in the above example of 3 columns with 1 visible:

0";1.5";0"
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"Terry" <Terry@discussions.microsoft.com> wrote in message 
news:5568453E-FB57-4B1F-A0AA-0366E7AB0403@microsoft.com...
> Hi,
> I've created a Combo Box using the Controls Wizard.  In creating the
> control, I specified that my unique value to be stored in the table is an 
> ID.
> When the user views the selection options in the Combo Box, I'd like to 
> have
> the ID field hidden and only see the text values, while still storing the 
> ID
> in the appropriate column.
> This is probably a second question.
> Also, when the user views a form that has been populated, I'd like them to
> see the actual textual values on the form, as just the stored ID value. 
> Is
> that possible to do with the one Combo Box or is that two controls?  If 
> it's
> two, how do you not see the empty second control when initially populating
> the form? 


0
Arvin
4/5/2010 3:27:29 AM
On Sun, 4 Apr 2010 19:11:01 -0700, Terry <Terry@discussions.microsoft.com>
wrote:

>Hi,
>I've created a Combo Box using the Controls Wizard.  In creating the 
>control, I specified that my unique value to be stored in the table is an ID. 
> When the user views the selection options in the Combo Box, I'd like to have 
>the ID field hidden and only see the text values, while still storing the ID 
>in the appropriate column.

A combo has several interacting properties.

Its RowSource is usually a Query which can have up to 255 fields, 65536 rows.
Normally both would be much smaller.

Its Bound Column specifies which of those fields will be stored.

It has a Control Source - the name of the field into which the selected value
is stored.

The Column Count specifies how many fields will be included when the combo is
dropped down.

The ColumnWidths is a string of widths (in centimeters or inches) for the
fields in the ColumnCount - a width of 0 means that the corresponding field is
available (say as the Bound Column) but will not be displayed.

The first nonzero width column is displayed when the combo is *not* dropped.

So if you have the ID in column 1, and two additional text values, you could
use a column count of 3; a bound column of 1 (the ID); and a columnwidths
property like

0";1.5";0.5"

to display both text fields when the combo is dropped, and only the first when
it's not.

-- 

             John W. Vinson [MVP]
0
John
4/5/2010 5:06:48 AM
Thank you for an excellent answer and background.  Is there a website or book 
that you can point me to that has the same kinds of information on control 
properties?  Access Help sometimes only has one-liners that really don't 
provide enough detail.
Thanks again.
Terryomsn

"John W. Vinson" wrote:

> On Sun, 4 Apr 2010 19:11:01 -0700, Terry <Terry@discussions.microsoft.com>
> wrote:
> 
> >Hi,
> >I've created a Combo Box using the Controls Wizard.  In creating the 
> >control, I specified that my unique value to be stored in the table is an ID. 
> > When the user views the selection options in the Combo Box, I'd like to have 
> >the ID field hidden and only see the text values, while still storing the ID 
> >in the appropriate column.
> 
> A combo has several interacting properties.
> 
> Its RowSource is usually a Query which can have up to 255 fields, 65536 rows.
> Normally both would be much smaller.
> 
> Its Bound Column specifies which of those fields will be stored.
> 
> It has a Control Source - the name of the field into which the selected value
> is stored.
> 
> The Column Count specifies how many fields will be included when the combo is
> dropped down.
> 
> The ColumnWidths is a string of widths (in centimeters or inches) for the
> fields in the ColumnCount - a width of 0 means that the corresponding field is
> available (say as the Bound Column) but will not be displayed.
> 
> The first nonzero width column is displayed when the combo is *not* dropped.
> 
> So if you have the ID in column 1, and two additional text values, you could
> use a column count of 3; a bound column of 1 (the ID); and a columnwidths
> property like
> 
> 0";1.5";0.5"
> 
> to display both text fields when the combo is dropped, and only the first when
> it's not.
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 
0
Utf
4/5/2010 2:52:01 PM
On Mon, 5 Apr 2010 07:52:01 -0700, Terry <Terry@discussions.microsoft.com>
wrote:

>Thank you for an excellent answer and background.  Is there a website or book 
>that you can point me to that has the same kinds of information on control 
>properties?  Access Help sometimes only has one-liners that really don't 
>provide enough detail.

You might want to look at two very thourough books: _Access <version> Inside
Out_ by my friend and mentor John Viescas; and the huge, deep and dense two
volume _Microsoft Access 2002 Developer's Handbook_ by Getz, Litwin and
Gilbert. It has not been updated to 2007 but the vast bulk of what it contains
is still very much valid and useful. Lots more resources at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

-- 

             John W. Vinson [MVP]
0
John
4/5/2010 3:25:57 PM
Reply:

Similar Artilces:

Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the...

DDP (Drop down Box)
i have a front sheet that has data input onto it, this then feeds into sheets Monday thru Sunday, I would like to print data on occassions and have decided to creat a form for this option, what i dont want to do is allow users access to the data on the pages then print, am looking for a safe way to do this and decided a form, drop down box, selecting the day of the week to be printed and this would print the required data, Any idea of the code for example, Selecting Monday in the Drop down box, would then print the Sheet "Mon" Tuesday in DDB would then print "Tue" and s...

Failed to customize the dialog box of toolbar
I cannot fill the left-hand list box of toolbar's dialog box,and if I delete one button in toolbar,I lost it. Below is my code: [code] #include <windows.h> #include <commctrl.h> #include "resource.h" #define ID_TOOLBAR 20001 static char *szAppName = "Study Toolbar" ; LRESULT CALLBACK WndProc( HWND, UINT, WPARAM, LPARAM ) ; TBBUTTON tbButtons[] = { {STD_FILENEW, IDM_FILE_NEW, TBSTATE_ENABLED, TBSTYLE_BUTTON | BTNS_SHOWTEXT, 0, 0}, // 1 {STD_FILEOPEN, IDM_FILE_OPEN, TBSTATE_ENABLED, TBSTYLE_BUTTON, 0, 0}, // 2 {STD_FILESAVE, IDM_FILE_SAVE, TBSTATE...

Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a ProposalForm, ServiceTypes need to be added to a ProposalServicesTable. For instance, I have a Check Box on the ProposalForm. When a Check Box is clicked Yes, Access must search the ServiceTypes Table, select a specific ServiceTypeID, and add the ServiceType to the ProposalServicesTable. How can I add the proper Service record from the ServiceTable to the ProposalServicesTable based on the Check Box? I wouldn't do it that way. I'd use a listbox (with multi-select set to YES) that was sourced to the ServiceTab...

Excel 2007 Need to permanently change Normal settings for gridlines
I am using Excel 2007 for the first time and find the gridlines delineating the cells are so faint as to be nearly indistinguishable. I can go into the cell formatting and modify the normal style, and it is just the way I want it. But I can't find a way to make Excel remember this and treat it as the new definition of the Normal style. I don't want to have to redefine Normal every time I open a new spreadsheet. Someone please help! Regards Leonard Priestley The changes you describe are changing the Border color and NOT gridlines. Go into Excel Options and cl...

Changing which database Business Portal points to
If I install Business Portal 2.5 (GP) and point it to our Great Plains development server with an old Dynamics database, once we decide to take it live, how do I go back and point it to the live Great Plains server? Is this something that can only be done during the installation? Let me preface my response by saying I only have experience with BP 3.0.... In BP 3.0 several tables are created in the Dynamics database to support the various BP functions. Unless you plan to move or copy those tables you will probably have to reinstall BP 2.5 in order to have them created. Our attempt to cop...

Saving a calculated field
First, yes I have read the threads on storing a calculated field and that it is bad mojo to do that. However, I have pay data that I calculate and input to a database and it must be able to be reconciled with our ADP data. So I need the ability to change and fix the data so it does not change as a result of recalculations. I have a form with a field that calculates the pay based on hours and pay rate. I have another field (the "copy" field) next to that one that has the control source set to the database field. I have set the default value of that field to be equal to the...

Modification is not allowed becuse selection is locked.
Microsoft Word won't let me type anything because it says that modification is not allowed becuse selection is locked. What can I do about it? On Mon, 5 Apr 2010 17:56:01 -0700, OrangeCake <OrangeCake@discussions.microsoft.com> wrote: >Microsoft Word won't let me type anything because it says that modification >is not allowed becuse selection is locked. What can I do about it? If you have a trial version of Office (typically pre-installed on a new computer), it has expired and you'll have to buy a retail copy. Or, if you already have a licensed copy, ma...

Access, average several fields in one row
I have several rows of data in a field, I need to average all the entries in one row I have 12 fields for 12 months of data, I need the average of the sum of all non blank entries. For example 3 months completed, the solution in Excel is (field1+field2+field3)/3 I am looking for method to average the sum in Access One way if you can't change your data is to use a VBA function. I've posted one below. You would call it in a calculated field in a query. Assuming your field names are the abbreviated month names the expression might look like the following. Field: fRow...

Changing Titles by a formula
From cell A1:A10 I have standard titles that are linked to another sheet. I only need to change the office and date per each title. Is thier a way to do that making the office and date a formula in another cell? example of title ABC Comp, Office 38L Sales as of 6/07/2004 I need to only keep changeing the 38L and date. Any suggestions----Thanks Say Office # is in Column 'C' in cell A1: ="ABC Comp, Office "&C1&" Sales as of "&Today() ----- Heather wrote: ----- From cell A1:A10 I have standard titles that are linked to ...

Cell Selection?
I have a colum of numbers 198 0 0 198 2 What I need, is to be able to select 3. the 198's are not going to be used in the next part of my equation. That seems simple enough, however all the numbers could be usable (not 198) and I need to use just the first three. Any ideas? One interp / way, using non-array formulas Assuming source numbers in A1 down Put In B1: =IF(COUNT($C$1:C1)>3,"",C1) In C1: =IF(ISERROR(SMALL(D:D,ROW(A1))),"", INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0))) In D1: =IF(A1="","",IF(A1=198,"",ROW())) Select B1:D...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a pivot table thus displaying a charted image of the detail item selected. Any suggestions? ...

selectively download folders from isp
I am having trouble with Outlook. I have a yahoo account and I have mulitple folders on that account. I was wondering if there was a way to only download what is in my yahoo inbox and not in my yahoo bulk folder. Hi Moose I you take a look at your preferences in yahoo mail - http://mail.yahoo.com you have the option to send bulk mail to your pop3 client. As easy as that. Chris "MOOSE" <MOOSE@discussions.microsoft.com> wrote in message news:ADA4254F-0DB3-47BD-AC3F-D34AFD4ADEC5@microsoft.com... >I am having trouble with Outlook. I have a yahoo account and I hav...

can't change/delete bills and deposits
suddenly one day i realized that when i tried to change a bill, i could click "ok" but nothing would happen. I thought it was because i upgraded to 2004, so i deleted the bills and rescheduled. about 5 of the bills will not delete either. the error message is: "Money could not write to your Money file. The operation could not be performed or another application finished this task before you. Please try again." Now some of the newly scheduled bills will not allow me to change the properties, i click ok but nothing happens when i edit the series. I have repaired ...

Outlook 2007: When i Save a New Rule outlook Change it automatically...
Hi people! I have this problem with Outlook 2007. I have 15 e-mails addresses in my outlook, but i can=B4t create the rules for send all the received mails to each emails folder. Example: I want to create a simple rule that move all the e-mail that i get from "example1@example1.com" to the folder "Inbox/ example1@example1.com". When i create the rule, all works fine, i select the correct e-mail account name, the correct destination folder, etc... but when I hit the SAVE button in rules window, Outlook 2007 changes the account automatically to another one. Not automatically...

Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max necessary by hovering the cursor between the 2 rows I want to adjust and double-clicking, but sometimes this doesn't always work. Why is that that - do I need to adjust a setting? And is there any way to set it so that if text is added or deleted the row height would change automatically so thatthe text fit appropriately? Set the row format to Autofit and cells to Wrap Text Gord Dibben MS Excel MVP On Tue, 19 May 2009 12:14:04 -0700, DaveL <DaveL@discussions.microsoft.com> wrote: >I know I should be ...

Changing Functional Currency #4
Try to change the functional currency to Z-CNY. ERROR = Activity for this currency already exist. Finance made 2 GL transactions. Can we remove the transactions (Tools >> Utilities >> Financial >> Remove History) or (File >> Maintenance >> Clear Data ) then change the functional currency? Which tables should I remove history or clear data. Can someone on the board help us? regards, Once you process a transaction, you are stuck with the functional currency. I have not seen a table fix for this problem on the board. Consider creating a new database an...

Creating a Check Box
I would like to learn how to create a check box. Is there a way to do that? The simplest checkbox (I think) is from the Forms toolbar. So View|tool bars and show that Forms toolbar. Click on the checkbox and then click on the topleft corner where it should be placed. Then draw to the opposite lowerright corner. When you've placed it correctly, right click on it and select "Format Control" On the Control Tab, you can assign a cell link that you can use to determine if the checkbox is checked or not. =if(A1=True,"It's checked","It's not checked") ...

need to make a formula that would add a field value to current dat
I have made a form in which I input different values. On of the values is (How Many Days). Now I need to a assign a default value, or expression (not sure which way to go about this) that will take the date value for (Date) and add the value (How Many Days) I figured that the formula should read =sum([Date]+[How Many Days]) But that is not giving me any results, thanx for your help in advance =DateDiff("d", Date(), [How Many Days]) -- Wayne Manchester, England. "J Man" wrote: > I have made a form in which I input different values. On of the values is ...

Change default "SaveAs" to *.msg rather than *.html
Hi!, Using Outlook 03, I regularly need to save emails and prefer to save as *.msg so the attachments are contained within the one file. How can you change the default SaveAs command to Save as Type in a *.msg format (rather than *.html) so the type is already *.msg without having to use the pull down? Thank you Troy Outlook provides no way to change that. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "TroyB" <boeky72@hotmail.com>...

change column name from letters to numbers?
I'm importing a 3rd party CSV file into Excel and then comparing some of the contents to a printed spec. Each numbered field in the spec corresponds to a column in the work sheet. I'd like to change the column headers from letters to numbers. Is that possible? Tools-->Options, General tab. Check the R1C1 Reference Style. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Jerry" <jerry@nowhere.com> wrote in message news:OoyPef$eFHA.3048@TK2MSFTNGP12.phx.gbl... > I'm importing a 3rd party CSV file into Excel and then comparing ...

Evaluate Yes/No Field Based on User Input
Hi. I have a field that is set to Yes/No. I want to ask the user a question and based on their response (whether they type yes or no) I want the query to check the field and return all records marked yes is they type yes and all other records if they type no. How can I do this? Also, could I present them with a simple text box (having yes and no choices) or maybe a check box so they won't have to type anything? If you help me with the first part, this question is a bonus. I'll be happy with just the first question answered. Thanks! A Yes/No field actually stores -...

Update table with Multi-select list box
I have a database with a tab control that has several pages in it (my boss loves tabs for navigation). Each page has a list box based on a category of training events that members of my office attend and the box is based on a query that selects the training events for the tab page's respective category. I select an item from the list, click a button, and go to a form with a text box showing the name of the training event I previously selected. On that form, I want to select mutiple names of office members from a list box (already created and source is the Office Roster table) and then c...

Changing of range (Address) to (Cell)
Hi people I have encounter a problem with the use of range From a recorded macro, it's listed this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2,A24:M28"), PlotBy:=xlRow And I edit it to this way ActiveChart.SetSourceData Source:=Sheets("Trend").Range("A1:M2," & Cells(StartX, StartY), Cells(LastX, LastY)), PlotBy:=xlRow And obviously VBA compiler won't let me go this easily, it happen to give an "evil-comment" on my source range, May i know how can i solve this Thank You Hi Kaiyang, Try this, assuming the...