Automatically Add Entry to Combobox

[Mellstock]

I have a database which records client addresses. I have a field
called Town which is selected via a Combo Box from a Table called
Towns. What I would like to do is to automatically add new Towns to
the Table and the Combo Box.

I believe I need to limit the selections to the list in the table
(Combo Box) and then write some code on the NotInList event.

If this is correct how do I go about it ?

 

[Douglas J. Steele]

What you're describing is correct.

The sample code in the Help file (under NotInList event) is reasonably good,
or you can take a look at http://www.mvps.org/access/forms/frm0015.htm at
"The Access Web"

 

[fredg]

I have a database which records client addresses. I have a field
called Town which is selected via a Combo Box from a Table called
Towns. What I would like to do is to automatically add new Towns to
the Table and the Combo Box.

I believe I need to limit the selections to the list in the table
(Combo Box) and then write some code on the NotInList event.

If this is correct how do I go about it ?

Make sure the Combo Box's LimitToList property is set to Yes.
You can use a simple Append query code to add the new
data.

Code the Combo Box's NotInList event:

If MsgBox("The Item Entered is not in database, would you like to add
it?", vbYesNo) = vbYes Then
CurrentDb.Execute "INSERT INTO Towns(Town) Select " &
Chr(34) & NewData & Chr(34) & ";", dbFailOnError
Response = acDataErrAdded
End If

 

[Mellstock]

What you're describing is correct.

The sample code in the Help file (under NotInList event) is reasonably good,
or you can take a look athttp://www.mvps.org/access/forms/frm0015.htmat
"The Access Web"

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)








- Show quoted text -

I have looked at the code on the link. I can get it to display the
programed message box if I un Dim the Dim db statement. (put a ' in
front of it) The example starts with Dim db as DAO.Database. Having
got the message box the code errors as it does not like Dim rs As
DAO.Recordset and continues to error if I try and un Dim that
statement.

I am using Access 2000 and my record source is a table named Towns.
Should I be attaching something different to these Dim statements.

 

[Mellstock]

Make sure the Combo Box's LimitToList property is set to Yes.
You can use a simple Append query code to add the new
data.

Code the Combo Box's NotInList event:

If MsgBox("The Item Entered is not in database, would you like to add
it?",  vbYesNo) = vbYes Then
CurrentDb.Execute "INSERT INTO Towns(Town) Select " &
Chr(34) & NewData & Chr(34) & ";", dbFailOnError
Response = acDataErrAdded
End If

I have tried this and a longer version you posted in September 2003
and cannot get either version to work. If I enter a new town I get a
message telling me the town is not on the list and to enter one from
the limited list. Any pointers as to where I might be going wrong?

Thank you.

 

[Douglas J. Steele]

I have looked at the code on the link. I can get it to display the
programed message box if I un Dim the Dim db statement. (put a ' in
front of it) The example starts with Dim db as DAO.Database. Having
got the message box the code errors as it does not like Dim rs As
DAO.Recordset and continues to error if I try and un Dim that
statement.

I am using Access 2000 and my record source is a table named Towns.
Should I be attaching something different to these Dim statements.

Sounds as though you don't have a reference set to DAO. (Access 2000 doesn't
include one by default).

Go into the VB Editor and select Tools | References from the menu. Scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object library, select it, then back out of the dialog.

 

[Mellstock]

Sounds as though you don't have a reference set to DAO. (Access 2000 doesn't
include one by default).

Go into the VB Editor and select Tools | References from the menu. Scroll
through the list of available references until you find the one for
Microsoft DAO 3.6 Object library, select it, then back out of the dialog.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)- Hide quoted text -

- Show quoted text -

Thanks that helps. DAO Objects now recognised. Code sort of works. If
I enter a new town it is recognised as new, however if I click yes to
enter, it does not get entered onto form but new town appears in Towns
table ! If I press No code errors on resetting rs to nothing.

 

[Mellstock]

Thanks that helps. DAO Objects now recognised. Code sort of works. If
I enter a new town it is recognised as new, however if I click yes to
enter, it does not get entered onto form but new town appears in Towns
table ! If I press No code errors on resetting rs to nothing.- Hide quoted text -

- Show quoted text -

Seem to have cracked it with some help from a download
support.microsoft.com/kb/197526/en-us. Similar structure to the code
from Access Web.

 

[Mellstock]

Seem to have cracked it with some help from a download
support.microsoft.com/kb/197526/en-us. Similar structure to the code
from Access Web.- Hide quoted text -

- Show quoted text -

I think I am nearly there. When the form is open the code I have
written recognises a new entry and allows it to be added to the table
and Combo Box selections.

I still have a problem when I open the database and the form from
scratch I get a message saying the Table (Towns) to feed the query
cannot be found.

If I check Tables it is there and if I go into the Town field property
all appears OK.

Source Combo Box entries from an existing Table/Query and
SELECT[Towns]:[Town]FROM[Towns];

If I come out of properties back to the form the function works the
Combo Box appears including Towns added from a previous session. (ie
prior to reopening the database / form)

 

[Douglas J. Steele]

Do you really not have spaces in that SQL Statement? It should be

SELECT Town FROM Towns ORDER BY Town

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Seem to have cracked it with some help from a download
support.microsoft.com/kb/197526/en-us. Similar structure to the code
from Access Web.- Hide quoted text -

- Show quoted text -

I think I am nearly there. When the form is open the code I have
written recognises a new entry and allows it to be added to the table
and Combo Box selections.

I still have a problem when I open the database and the form from
scratch I get a message saying the Table (Towns) to feed the query
cannot be found.

If I check Tables it is there and if I go into the Town field property
all appears OK.

Source Combo Box entries from an existing Table/Query and
SELECT[Towns]:[Town]FROM[Towns];

If I come out of properties back to the form the function works the
Combo Box appears including Towns added from a previous session. (ie
prior to reopening the database / form)

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.

 

[Jan Baird]

Jan Baird is out of the country until September 20. Every effort will be
made to respond to messages, but please be patient.