Udate a combo box

[Michael Parr]

I have been at this problem for sometime now and at my
complete wits end and asked on here before. I have been
told to goto a website
WWW.mvps.org/access/forms/frm0015.htm and copied the code
into 'on notinlist' but it has not worked.

Down to the knitty-gritty!!!
I have a form with a combo box called 'county' (it is
COUNTY not COUNTRY, I live in the UK) and a table also
called county. In the forms combo box I want to list
counties of the UK, but when a new county is typed into
the combo box which is not there I want it to be saved
into the table called 'county' for easy retyping at a
later stage in the form. Someone out there must be able to
help. Thanks in advance from a complete novice. Please
help.

 

[Albert D. Kallal]

That sample code of course requites you to change the table name,and the
field name used. (it could not possibly read you mind!).

So change:
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)

Change the name of "tblAE" to your table name of COUNTRY

and also change:
rs!AEName = NewData


AEName to whatever the field name is in the country table.

That should do it...

 

[Douglas J. Steele]

You forgot to mention what the field name is in table COUNTY. I'll assume
it's CountyNM

Change


Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

to

Set db = CurrentDb
Set rs = db.OpenRecordset("COUNTY", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!CountyNM = NewData
rs.Update

or to

Set db = CurrentDb
CurrentDb.Execute "INSERT INTO COUNTY (CountyNM) " &
"VALUES " & Chr$(34) & NewData & Chr$(34), dbFailOnError

 

[Michael Parr]

Thanks for replying back, but I have done everthing you
said but I still cannot get the combox to update with the
new county. I must be doing something wrong.

 

[Michael Parr]

Still did not work, Sorry

-----Original Message-----
You forgot to mention what the field name is in table COUNTY. I'll assume
it's CountyNM

Change


Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

to

Set db = CurrentDb
Set rs = db.OpenRecordset("COUNTY", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!CountyNM = NewData
rs.Update

or to

Set db = CurrentDb
CurrentDb.Execute "INSERT INTO COUNTY (CountyNM) " &
"VALUES " & Chr$(34) & NewData & Chr$(34), dbFailOnError


--
Doug Steele, Microsoft Access MVP






.

 

[Albert D. Kallal]

Thanks for replying back, but I have done everthing you
said but I still cannot get the combox to update with the
new county. I must be doing something wrong.

When you say it does not update, can you explain more. Does it prompt you to
add the country, and then give an error?

Or, does it prompt you to add the country, and when you go yes, the country
is not added?

Or, does it give any kind of error message?

You are not giving much to go on here. (my car is broke is not much for he
mechanic to go on either!).

You should also show your code that you currently have. You also need to
give us the name of the combo control on your form.

You did add your code to the not in list event of the combo..right?

 

[Michael Parr]

The new county that I have just typed in does not appear
in the combo box. I do not get any sort of error message.
The name of the combo control in my form is 'County'
(AGAIN).

I did add my code to the not in list event of the combo

 

[Douglas J. Steele]

Did you remember to set the combo box's LimitToList property to Yes?

 

[Albert D. Kallal]

Ok, must make sure you combo box has the limit to list set to yes.

other then that, it should work.

If it does not work, then please post you code: