how to have data added to form/table show up in combo box drop down

[p-rat]

I have a table that I created that is basically a "location" table. I
have a combo box on my form which a data entry person can select a
location from, but if the location is not in the list can type in an
entry. How do I get this new "location" entry (location not in table
prior to this entry) to show up the next time as one of the locations
in the drop down list?

 

[Al Campagna]

p-rat,
In the query behind the combo, use the exisiting table values to create
the list of possible selections.
For example, given an address table named tblAddresses, and a field in
that table named City.
Use tblAddresses as the table in the query behind the combo. Make that
query a Totals query that has just one field... City... sort ascending.
This might yield a list of...
Boston
Albany
Scranton
If the combo is not limited to list, and you type in Chicago, the next
new record will have Chicago added to the list of selections.

In other words, you're using the list of cities already entered in your
table as the rowsource for the City combo. If you add a new entry, it too
will then be picked up in the combo items.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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

 

[p-rat]

p-rat,
   In the query behind the combo, use the exisiting table values to create
the list of possible selections.
   For example, given an address table named tblAddresses, and a field in
that table named City.
   Use tblAddresses as the table in the query behind the combo.  Make that
query a Totals query that has just one field... City... sort ascending.
   This might yield a list of...
        Boston
        Albany
        Scranton
   If the combo is not limited to list, and you type in Chicago, the next
new record will have Chicago added to the list of selections.

   In other words, you're using the list of cities already entered inyour
table as the rowsource for the City combo.  If you add a new entry, it too
will then be picked up in the combo items.
--
    hth
    Al Campagna
    Microsoft Access MVP
   http://home.comcast.net/~cccsolutions/index.html

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






- Show quoted text -

Do I need to have this table on my back-end? I went to the combo on my
form and under properties I changed the table/query to a query. I
don't know how to do a Totals query, but when I tested this by putting
some test data in I didn't see any new locations that got added to my
table. The 'limit to list' is 'No'. I don't know what I'm doing wrong.
Thanks.

 

[Al Campagna]

p-rat,
FE or BE, it doesn't make any difference.
Describe (briefly) your Location table and what field/s might apply to
this combo's selection values.
Then I'll describe how to create a Totals query, and use it as the
Rowsource for your form's combo.

And... please top post in this thread, so we don't have to scroll up and
down to see the "flow" of the problem.
Thanks.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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

p-rat,
In the query behind the combo, use the exisiting table values to create
the list of possible selections.
For example, given an address table named tblAddresses, and a field in
that table named City.
Use tblAddresses as the table in the query behind the combo. Make that
query a Totals query that has just one field... City... sort ascending.
This might yield a list of...
Boston
Albany
Scranton
If the combo is not limited to list, and you type in Chicago, the next
new record will have Chicago added to the list of selections.

In other words, you're using the list of cities already entered in your
table as the rowsource for the City combo. If you add a new entry, it too
will then be picked up in the combo items.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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






- Show quoted text -

Do I need to have this table on my back-end? I went to the combo on my
form and under properties I changed the table/query to a query. I
don't know how to do a Totals query, but when I tested this by putting
some test data in I didn't see any new locations that got added to my
table. The 'limit to list' is 'No'. I don't know what I'm doing wrong.
Thanks.

 

[p-rat]

The locations table is simple a list of Locations with the one field
'Locations'.

 

[Al Campagna]

p-rat,
On my website (below) under Access Tips see...
Not In List Combobox (Forms)
How to use the Not In List event to Add a New Selection to a Combobox when
Limit to List = Yes.
Download: NotInListComboV97.zip or NotInListComboV2003.zip
That should do it for you.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

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


The locations table is simple a list of Locations with the one field
'Locations'.

 

[p-rat]

I currently show a procedure in the Before Update event of this combo
box:

Private Sub Disposal_Site_BeforeUpdate(Cancel As Integer)

End Sub

I don't know what it does (I didn't write this application). How would
I put your code in here? When deleting the above and adding your code
(after building your Totals query) I get an error. Does this make
sense?

 

[p-rat]

Nevermind, I figured it out. It works. Thanks a bunch Al!!