Combobox for Table, From Table

[Mafukufuku]

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!

 

[Al Campagna]

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

 

[Tom van Stiphout]

On Tue, 16 Mar 2010 05:48:01 -0700, Mafukufuku

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

 

[Mafukufuku]

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]

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