Combobox for Table, From Table

Hi, 

The online help function tells me I cant make a combobox which finds its 
entries from a table and stores its entries in the same table.  I want users 
to be able to enter areas where clients live. The combobox needs to fill the 
Area column in the appropriate table. But I want the combobox to be filled 
with previous entries from the same column, with the provision that a new 
entry can be made. The project is expanding thats why I need this. 

Or do I need to make an extra table with areas that can be filled with a 
separate command, which is then used as a source for the combobox?


Thanks!

0
Utf
3/16/2010 12:48:01 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
1238 Views

Similar Articles

[PageSpeed] 55

Mafukufuku,
> The online help function tells me I cant make a combobox which finds its
> entries from a table and stores its entries in the same table.
    Not true...
    A combo's query can be based on the values from a data table it
supports.

    In order to add new values to that combo, make the combo
LimitToList = No.

    For example, when you add a new record that involves a new area,
just enter it into the Area combo... even though it does not appear on
the combo list.  The next record you add, or go to, will have that new
area in the list, and available for selection.
*** That's the easiest method... but not necessarily the best

    Another method (better I think) is to maintain a separate table for
combo
values, and when the user runs into a new Area, it's added to the dedicated
combo table programmatically... and available to all thereafter
    On my website (below) I have a 97 and 2003 sample file that demonstrates
how to do that.  It's called Not In List Combobox.
-- 
    hth
    Al Campagna
    Microsoft Access MVP 2007-2009
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."





"Mafukufuku" <Mafukufuku@discussions.microsoft.com> wrote in message
news:A07DFC07-A873-498A-B044-2FD98CC2C066@microsoft.com...
> Hi,
>
> The online help function tells me I cant make a combobox which finds its
> entries from a table and stores its entries in the same table.  I want
> users
> to be able to enter areas where clients live. The combobox needs to fill
> the
> Area column in the appropriate table. But I want the combobox to be filled
> with previous entries from the same column, with the provision that a new
> entry can be made. The project is expanding thats why I need this.
>
> Or do I need to make an extra table with areas that can be filled with a
> separate command, which is then used as a source for the combobox?
>
>
> Thanks!
>



0
Al
3/16/2010 1:39:03 PM
On Tue, 16 Mar 2010 05:48:01 -0700, Mafukufuku
<Mafukufuku@discussions.microsoft.com> wrote:

No, you can use a query with the DISTINCT predicate:
select distinct Area
  from myTable
(of course you change myObjectNames to yours)

You can type this in in SQL view, or use the Unique Values property
when in design view.

-Tom.
Microsoft Access MVP


>Hi, 
>
>The online help function tells me I cant make a combobox which finds its 
>entries from a table and stores its entries in the same table.  I want users 
>to be able to enter areas where clients live. The combobox needs to fill the 
>Area column in the appropriate table. But I want the combobox to be filled 
>with previous entries from the same column, with the provision that a new 
>entry can be made. The project is expanding thats why I need this. 
>
>Or do I need to make an extra table with areas that can be filled with a 
>separate command, which is then used as a source for the combobox?
>
>
>Thanks!
0
Tom
3/16/2010 1:54:54 PM
I now made a combobox with Row Source HtblArea.Namearea. And controlsource 
DtblClient.Area.

Now now, everytime I open the form and start entering records, the first 
record in the Dtblclient.area is shown and overwritten. Unless of course I 
use the navigation buttons to make a new record. How do I set the forms to 
start with a new record automatically after opening and after every new entry?

"Al Campagna" wrote:

> Mafukufuku,
> > The online help function tells me I cant make a combobox which finds its
> > entries from a table and stores its entries in the same table.
>     Not true...
>     A combo's query can be based on the values from a data table it
> supports.
> 
>     In order to add new values to that combo, make the combo
> LimitToList = No.
> 
>     For example, when you add a new record that involves a new area,
> just enter it into the Area combo... even though it does not appear on
> the combo list.  The next record you add, or go to, will have that new
> area in the list, and available for selection.
> *** That's the easiest method... but not necessarily the best
> 
>     Another method (better I think) is to maintain a separate table for
> combo
> values, and when the user runs into a new Area, it's added to the dedicated
> combo table programmatically... and available to all thereafter
>     On my website (below) I have a 97 and 2003 sample file that demonstrates
> how to do that.  It's called Not In List Combobox.
> -- 
>     hth
>     Al Campagna
>     Microsoft Access MVP 2007-2009
>     http://home.comcast.net/~cccsolutions/index.html
> 
>     "Find a job that you love... and you'll never work a day in your life."
> 
> 
> 
> 
> 
> "Mafukufuku" <Mafukufuku@discussions.microsoft.com> wrote in message
> news:A07DFC07-A873-498A-B044-2FD98CC2C066@microsoft.com...
> > Hi,
> >
> > The online help function tells me I cant make a combobox which finds its
> > entries from a table and stores its entries in the same table.  I want
> > users
> > to be able to enter areas where clients live. The combobox needs to fill
> > the
> > Area column in the appropriate table. But I want the combobox to be filled
> > with previous entries from the same column, with the provision that a new
> > entry can be made. The project is expanding thats why I need this.
> >
> > Or do I need to make an extra table with areas that can be filled with a
> > separate command, which is then used as a source for the combobox?
> >
> >
> > Thanks!
> >
> 
> 
> 
> .
> 
0
Utf
3/17/2010 8:20:01 AM
Mafukufuku,
>I now made a combobox with Row Source HtblArea.Namearea. And controlsource
>DtblClient.Area.
    That doesn't tell us much, as regards your initial combobox question.
    Were you able to incorporate one of my suggestions, and is that working 
for you?

> Now now, everytime I open the form and start entering records, the first
> record in the Dtblclient.area is shown and overwritten.
    That shouldn't have anything to do with your initial combobox question, 
or
either of my suggestions, unless some errors were introduced.

    You can send your form to a new record using the OpenForm method
        DoCmd.OpenForm "YourFormName"
        DoCmd.GoToRecord , , acNewRec
    After filling in one new record, you can navigate to another new record, 
or
have use a "Complete" button that issues the same GotoRecord,, acNewRec
when clicked.
                    OR
    Set the form's Data Entry property to YES.  This will only allow new 
records
to be added to the table... no browsing or editing of existing records.
-- 
    hth
    Al Campagna
    Microsoft Access MVP 2007-2009
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."

"Mafukufuku" <Mafukufuku@discussions.microsoft.com> wrote in message 
news:BD757744-0026-44CF-996B-2D994CE236EA@microsoft.com...
>I now made a combobox with Row Source HtblArea.Namearea. And controlsource
> DtblClient.Area.
>
> Now now, everytime I open the form and start entering records, the first
> record in the Dtblclient.area is shown and overwritten. Unless of course I
> use the navigation buttons to make a new record. How do I set the forms to
> start with a new record automatically after opening and after every new 
> entry?
>
> "Al Campagna" wrote:
>
>> Mafukufuku,
>> > The online help function tells me I cant make a combobox which finds 
>> > its
>> > entries from a table and stores its entries in the same table.
>>     Not true...
>>     A combo's query can be based on the values from a data table it
>> supports.
>>
>>     In order to add new values to that combo, make the combo
>> LimitToList = No.
>>
>>     For example, when you add a new record that involves a new area,
>> just enter it into the Area combo... even though it does not appear on
>> the combo list.  The next record you add, or go to, will have that new
>> area in the list, and available for selection.
>> *** That's the easiest method... but not necessarily the best
>>
>>     Another method (better I think) is to maintain a separate table for
>> combo
>> values, and when the user runs into a new Area, it's added to the 
>> dedicated
>> combo table programmatically... and available to all thereafter
>>     On my website (below) I have a 97 and 2003 sample file that 
>> demonstrates
>> how to do that.  It's called Not In List Combobox.
>> -- 
>>     hth
>>     Al Campagna
>>     Microsoft Access MVP 2007-2009
>>     http://home.comcast.net/~cccsolutions/index.html
>>
>>     "Find a job that you love... and you'll never work a day in your 
>> life."
>>
>>
>>
>>
>>
>> "Mafukufuku" <Mafukufuku@discussions.microsoft.com> wrote in message
>> news:A07DFC07-A873-498A-B044-2FD98CC2C066@microsoft.com...
>> > Hi,
>> >
>> > The online help function tells me I cant make a combobox which finds 
>> > its
>> > entries from a table and stores its entries in the same table.  I want
>> > users
>> > to be able to enter areas where clients live. The combobox needs to 
>> > fill
>> > the
>> > Area column in the appropriate table. But I want the combobox to be 
>> > filled
>> > with previous entries from the same column, with the provision that a 
>> > new
>> > entry can be made. The project is expanding thats why I need this.
>> >
>> > Or do I need to make an extra table with areas that can be filled with 
>> > a
>> > separate command, which is then used as a source for the combobox?
>> >
>> >
>> > Thanks!
>> >
>>
>>
>>
>> .
>> 


0
Al
3/17/2010 1:05:28 PM
Reply:

Similar Artilces:

Pivot Tables Freezing in Excel 2000 from Multiple Consolidation Ranges
Hi! Trying to create a pivot table from 4 separate consolidation ranges. I have downloaded the latest service pack. I have tried the files both where they are on the network and locally. But every time I get to the last step of the wizard to create the layout ti just freezes up and won't let me create the pivot table. Any thoughts? ...

Another question on plotting a bar chart from a pivot table
So I want to plot a bar chart from a pivot. I have several x-axis values (categories), and this with be a side-by-side bar chart as I have 2 different y axis values for two experimental treatments. But there is a value across the treatments at each category (i.e.the row total). How do I tell Excel to plot the column categories as well as the column total? Righ now it won't plot the total as a separate bar for each category. Thanks Jerry You can't include the Grand Total in a pivot chart. You could create a normal chart, based on the pivot table, and include the totals in that. On ...

Amending records across 4 tables
I have a table (tblcompany) that contains company information. I have 3 other tables that include various data streams but also include a company name (txtcompany) which is linked to the company name in tblcompany. I want to create a form that deals with a situation where a company changes its name. I know that I can create an update query for each table to change the name in each record where it appears but I want to create a user process. What I had in mind was an unbound form that had a combo box based on tblcompany from which the user could select the current company name. Then ...

Temporary Table
The conversion process encountered an error and the temporary table did not get removed.Remove the temporary table at the database level. ...

combobox
hi i am trying to create a combobox containing a list of names i want the list to default to a "dummy" value when the workbook is opened How do i do this? thanks kevin You would need a macro for that, assume that you use the combo box from the control toolbox, and that the linked cell is A2 in Sheet2 Private Sub Workbook_Open() Worksheets("Sheet2").Range("A2").Value = "Your_Dummy_Value" End Sub Right click the little excel icon to the left of the filemenu and select view code or press Alt + F11 and double click ThisWorkbook That's where the ma...

Requery combobox
I have a form that includes a combobox that has the following in its on enter event: Private Sub cbProjectPhase_Enter() Me.cbProjectPhase.Requery End Sub The query that is tied to this combox uses another field ("Cost Center") on this form as criteria to select records for this combobox. It works fine except when I go to the next record and click on that combobox it removes what is showing in the previous record's combobox's field because my selection "Cost Center" is different this time. I understand why this happens but I don't know how to get around it. I...

How can I update fields in one table with fields in another table?
Hi All I have two tables: my main table and a cut-down version of the main table with newly updated values: Main ------- RefNo Document_Type Version Title ModMain ------------- RefNo Document_Type_UPDATED Version_UPDATED Title_UPDATED I can't work how to use Access's query builder to do the simple task of updating the fields in "Main" with the modified values in "ModMain" where the RefNos match. Can anyone give me a pointer or web link? I cant find any information in Access Help about performing update queries with more than one table. This seems a straight for...

Pivot table-date grouping
I receive excel data from six outside agencies on a monthly schedule. One of the columns is Date. When I put all the data together on one ss, I can create a pivot table but I cannot group the dates. (I know it is a date format problem as I created a dummy ss from scratch and was able to create the chart.) Each date cell has a green tic in the top left corner. Format|Cells tells me it's a date but the infobox next to each date cell shows me a list of options. How can I take the existing column of dates and maybe move them to a new column with a correct date format so that I can use t...

Populate combobox
Hello Why doesn't this populate my ActiveX combobox? Private Sub ComboBox1_Change() Dim row As Integer ' Make sure the RowSource property is empty Sheet1!ComboBox1.RowSource = "" Sheet1!ComboBox1.RowSource = "Sheet1!A1:A12" ' Add some items to the ActiveX combobox For row = 1 To 12 Sheet1!ComboBox1.AddItem Sheets("Sheet1").Cells(row, 1) Next row End Sub Change the exclamation point (!) to a period (.) after Sheet1 everywhere except in the row source reference that is within the quote marks. &quo...

Help With Simple Combobox Programming
I have a combo box on sheet1. When the user clicks on the combo box the first time ( it gets focus ) It should add/load all items from sheet2 column A. Now when the user selects from the combobox that item should be copied/placed on sheet1.A5 similarly the next item selected in the combo box should be placed below A6 and so on How can this be accomplished with code thx Hi, Use code like that : Private Sub cboIn_Click() Dim intR As Integer intR = Range("a4").CurrentRegion.Rows.Count Range("a4").Offset(intR, 0).Value = cboIn.Value End Sub Priv...

combobox into another combobox
i got 2 combo boxes in a worksheet. i want to link the 2 combo boxes together. combo2 will depend on which is selected to the combo1. eg: combo1 - accessories, card, ram list in combo2 will vary on the item selected from the combo1. let say: combo1 - accessories have been selected combo2 - slot fan, usb to ps2 convertor. can someone helps me!!! thxns in advance Hi Take a look at Debra Dalgleish's site for lots of information on Data Validation http://www.contextures.com/xlDataVal13.html and for the section using Combo boxes http://www.contextures.com/xlDataVal10.html -- Regards ...

Pivot Table Calculated Formula If statement
Hi all, In a pivot table I am trying to add this formula: In the insert calculated field-->Formula field i type =IF('FIELD NAME'="STRING TO COMPARE", 1,0) Any Idea why this does not work? The calculated field operates on a sum of the field, and the sum of a text string is zero. The zero result won't equal the "String to compare", so the calculated field will return a zero. Perhaps you could do the calculation in the source data instead, and add that field to the pivot table. Dan McCollick wrote: > Hi all, > In a pivot table I am trying to a...

Filling More than one field a combobox selection
I have a simple Address Form Suburb, State, PostCode Suburb is a combobox linked to a Post code table What I would like to do is for the user to look up Suburb by typing into the combo box and populate the state and postcode fields on selection. Am I going about this the right way? See www.allenbrowne.com. He has an excellent search function exactly like you need. -- Milton Purdy ACCESS State of Arkansas "Avid Fan" wrote: > I have a simple Address Form Suburb, State, PostCode > > Suburb is a combobox linked to a Post code table > &...

Help with ComboBox
Hi, I'm using Excel 2003 I have a spreadsheet using a ComboBox that allows selection of one of several sets of data. Each set is a 3 column row of data. I have the following properties set: BoundColumn: 2 TextColumn: 1 When a selection is made the ComboBox displays the column 1 value. The ComboBox seems to behave normally, except that when I close and open the file the combox initially displays the value of column 2 (instead of column 1). When a new selection is made it goes back to displaying the column 1 data. Shouldn't it always display the data as assigned by TextColu...

exporting email to access table
Is there a way to export emails to a microsoft Access database table? any direction would be great. thanks, mate ...

Calculation function in table won't add correctly
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hey All! =) <br><br>Happy middle of the week! <br><br>I have created a table that has a column of numbers on the right. <br><br>I have used Form Fields for entering the numbers. <br><br>There are several sections and each section several rows. <br><br>Each section adds up so if I have a section that has 3 rows and I enter 10.00 in each form field then that section will total up to 30.00. <br><br>The next section has 5 rows and I enter 10.00 in ...

Pivot table using External data
Hi I am generating a pivot table using the following query SELECT `'1100 REG$'`.`Ap/ArID`, `'1100 REG$'`.Amount FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1100 REG$'` `'1100 REG$'` UNION SELECT `'1157 REG$'`.`Ap/ArID`, `'1157 REG$'`.Amount FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1157 REG$'` `'1157 REG$'` UNION SELECT `'1165 REG$'`.`Ap/ArID`, `'1165 REG$'`.Amount FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1165 REG$'` ...

Table within a slide master?
Sorry if someone has covered this but I can't find anything on this topic. I'm working with Slide Masters and Layouts for the first time. I'm doing OK with making them and applying them to pages. But when I make a table it doesn't work. When I go to Insert>Layout Placeholder> Table, then I get a box that has the word "Table" in it. Is that right? When I look for controls to make it look like a table with headers, cells etc., right-click on it, whatever, I don't see any table controls, just the usual text/box attributes (size etc.). So in the ribbon...

I need to delete oldest record so that table only contains latest record 09-15-07
Hi, I have a table called DDPayments which contains 4 fields: PaymentID, DDID, BankID and Date Paid. I need a query that will delete all records, so the rows will only contain the most recent Date when a transaction occured. I currently have the following in the table: ROW 1 - PaymentID: 1 DDID: 9 BankID: 1 Date Paid: 13/08/07 ROW 2 - PaymentID: 1 DDID: 9 BankID: 1 Date Paid: 13/09/07 ROW 3 - PaymentID: 1 DDID: 26 BankID: 1 Date Paid: 10/08/07 ROW 4 - PaymentID: 1 DDID: 26 BankID: 1 Date Paid: 13/09/07 ROW 5 - PaymentID: 1 DDID: 6 BankID: 1 Date Paid: 13/08/07 (This table is u...

combobox in vista
hi , guys i meet the the same issue with below http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?&query=ComboBox+in+vista&lang=en&cr=&guid=&sloc=en-us&dg=microsoft.public.platformsdk.shell&p=1&tid=9d3eeb9e-54d2-457d-8fc0-2473b2e2b203 who can tell me how to solve this question, or must wait the next version of mfc welcome any idea. thanks in advance. Doesnt the workaround as mentioned in the thread work for you? -- Ajay "John" <John@discussions.microsoft.com> wrote in message news:D9722557-9FD2-4193-AE3C-AED94F3162CA@microso...

Table format
Is there a way to place separate tables on one worksheet in Excel? I would like to be able to format one table without effecting the other columns/rows in a table. The Excel help search option did not give me any helpful information. I have attached for example a document formatted in Word showing separate tables. However, I would like to be able to format this financial information in Excel. Jim R. For the most part a "table" is not an Excel concept. It's really just data. You can put data anywhere you want in a sheet. For some specific purposes like Data, Form an...

can a table be opened in access by todays date
Hi Can anyone point me in the right direction here, I would like to open a table in access 2003 by date. I have an asp web page which needs to read data from a table with each days today's date (which ever day that is) then a new table is created with today's date. Example: I have a table called 17-may-2007 my ASP page reads this table for 24hours then tomorrow (12:00 midnight 18th) I will have a new table called 18-may-2007 and the old table is left behind (As 17-may-2007) so I need my sql statement to automatically open table by today's date. This is what I have so far but it...

Cascading comboboxes in datasheet, is it possible?
I have 3 tables: 1) Table Projects with autonumber primary key ProjectID, ProjectName (text) 2) Table Activities with autonumber primary key ActivityID, ActivityName(text), ProjectID (foreign key) 3) Table HoursWorked with autonumber ID field, ProjectID, ActivityID and Hours (Number) Tables Activities and Projects are linked. One project can have many activities. In table HoursWorked users insert ProjectID, ActivityID and Hours. This third table is linked to a datasheet form. What I would like to do is to select projects and corresponding activities by using cascading combo boxes in th...

Pivot Table formatting #2
I have a pivot table where I can left click on the fields such as Customer or Sum of Quantity and it highlights all of the cells in that fields row or column in a reverse video and then I can format that data and everything is great. I am working with someone over the phone that when they click on the field, it does not highlight the associated data cells and therefore they can not format the data and save those formats for when they change the pivot table. Any ideas why this might be? We are both using Excel 97. Thanks, Steve To turn on the Enable Selection feature: From the Pivot to...

Great Plains table names
There used to be a list of the Dynamic tables names in an easy to read format on Customersource. Does anyone have this list or know where to download? The report that prints out of Dynamics in resources if very hard to read. This list was a single row for each table name with a description thanks -- Doug I would like to find this as well. Leslie "Doug" wrote: > There used to be a list of the Dynamic tables names in an easy to read format > on Customersource. Does anyone have this list or know where to download? > > The report that prints out of Dynamics in ...