NotInList adding new entry as a new record each time

[robingSA]

Hi...I have a number of Combo Boxes on a form. If one types a name
which is not in the table, I want it to add the typed, new item. I am
using the following code to do this and it works well, except for one
thing - each new item is added as a new record. I want the new name to
be added after the last entry in the relevant field...is this possible
- preferably by tweaking the code I am using already?

Private Sub Supermarket_NotInList(NewData As String, Response As
Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Businesses", dbOpenDynaset)
Rs.AddNew
Rs![Supermarket] = NewData
Rs.Update
Response = acDataErrAdded
End If
End Sub

Thanks...Rob

[PC Datasheet]

If you have an autonumber in your table, sort "Businesses" on the autonumber
field ascending or descending depending on how you want to see the list. Add
the following line to your code after Response = acDataErrAdded:
Me!NameOfCombobox.Requery

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
re******@pcdatasheet.com

<ro******@gmail.com> wrote in message
news:11**********************@j73g2000cwa.googlegr oups.com...

Hi...I have a number of Combo Boxes on a form. If one types a name
which is not in the table, I want it to add the typed, new item. I am
using the following code to do this and it works well, except for one
thing - each new item is added as a new record. I want the new name to
be added after the last entry in the relevant field...is this possible
- preferably by tweaking the code I am using already?

Private Sub Supermarket_NotInList(NewData As String, Response As
Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Businesses", dbOpenDynaset)
Rs.AddNew
Rs![Supermarket] = NewData
Rs.Update
Response = acDataErrAdded
End If
End Sub

Thanks...Rob

[StopThisAdvertising]

"PC Datasheet" <No****@Spam.Com> schreef in bericht news:me****************@newsread3.news.pas.earthli nk.net...

--
--

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1175 users have come to me from the newsgroups requesting help '1175 users ????
re******@pcdatasheet.com

--
To Steve:
No-one wants your advertising/job hunting here!
Over 875 !! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.
And he is known here as a shameless liar with no ethics at all.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

[Doc Holiday]

Whats worse than his advertising is his sending emails to the person who
asks the question trying to get them to pay him to help them.

Really makes you not want to even write to the group anymore.

I've gotten my share of emails from him offering his help for a fee.



"StopThisAdvertising" <StopThisAdvertising@DataShit> wrote in message
news:44**********************@text.nova.planet.nl. ..

"PC Datasheet" <No****@Spam.Com> schreef in bericht
news:me****************@newsread3.news.pas.earthli nk.net...

--
--

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ???? Over 1175 users have come to me from the newsgroups requesting help '1175 users ???? re******@pcdatasheet.com

--
To Steve:
No-one wants your advertising/job hunting here!
Over 875 !! users from the newsgroups have visited the website to read what
kind of a 'resource' you are... (rapidly increasing..)

To the original poster:
Most people here have a common belief that the newsgroups are for *free
exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell
his services.
And he is known here as a shameless liar with no ethics at all.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R

[Arno R]

"Doc Holiday" <te**********@yahoo.com> schreef in bericht news:5V*****************@tornado.texas.rr.com...

Whats worse than his advertising is his sending emails to the person who
asks the question trying to get them to pay him to help them.

Really makes you not want to even write to the group anymore.



I've gotten my share of emails from him offering his help for a fee.

Hmmm,
I am beginning to think that Steve spammed 1175 people ... ???
Could you show me (email me) these unwanted emails please?

I will be glad to expand the info on this topic on the website if you don't mind.
I might even might make a new page.

Arno R

[Doc Holiday]

Sent you what i hadnt deleted.
"Arno R" <ar***********@tiscali.nl> wrote in message
news:44**********************@text.nova.planet.nl. ..

"Doc Holiday" <te**********@yahoo.com> schreef in bericht
news:5V*****************@tornado.texas.rr.com...

Whats worse than his advertising is his sending emails to the person who
asks the question trying to get them to pay him to help them.

Really makes you not want to even write to the group anymore.

I've gotten my share of emails from him offering his help for a fee.

Hmmm,
I am beginning to think that Steve spammed 1175 people ... ???
Could you show me (email me) these unwanted emails please?

I will be glad to expand the info on this topic on the website if you don't
mind.
I might even might make a new page.

Arno R

[Bob Quintal]

ro******@gmail.com wrote in
news:11**********************@j73g2000cwa.googlegr oups.com:

Hi...I have a number of Combo Boxes on a form. If one types a
name which is not in the table, I want it to add the typed,
new item. I am using the following code to do this and it
works well, except for one thing - each new item is added as a
new record. I want the new name to be added after the last
entry in the relevant field...is this possible - preferably by
tweaking the code I am using already?

Private Sub Supermarket_NotInList(NewData As String, Response
As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Msg = "'" & NewData & "' is not in the list." & vbCr &
vbCr Msg = Msg & "Do you want to add it?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("Businesses",
dbOpenDynaset)
Rs.AddNew
Rs![Supermarket] = NewData
Rs.Update
Response = acDataErrAdded
End If
End Sub

Thanks...Rob

The code between the Else and End If is what is the problem. I'd
need to know the recordsource of your combobox before being sure,
but I think you want to change the code to
Else
me.[supermarket] = newdata
Response = acDataErrAdded
End IF

Post the recordsource for the combobox. If it's a named query, post
that as well.

--
Bob Quintal

PA is y I've altered my email address.

[Bob Quintal]

"PC Datasheet" <No****@Spam.Com> wrote in
news:me****************@newsread3.news.pas.earthli nk.net:

If you have an autonumber in your table, sort "Businesses" on
the autonumber field ascending or descending depending on how
you want to see the list. Add the following line to your code
after Response = acDataErrAdded: Me!NameOfCombobox.Requery

Bzzzzt! Wrong Answer.
--
Bob Quintal

PA is y I've altered my email address.

[robingSA]

Thanks. Had no intentions of going any further with Steve - idiot. Why
would anyone pay someone when there are more competent experts on the
newsgroups who are willing to help for nothing?

Bob - do you mean the Row Source? If so, it is:
SELECT [Businesses].[Supermarket] FROM Businesses;

If I use your code above, I get the error message that "The text you
entered isn't an item in the list. Select an item from the list....

[Keith Wilby]

"PC Datasheet" <No****@Spam.Com> wrote in message
news:me****************@newsread3.news.pas.earthli nk.net...

If you have an autonumber in your table, sort "Businesses" on the
autonumber field ascending or descending depending on how you want to see
the list. Add the following line to your code after Response =
acDataErrAdded:
Me!NameOfCombobox.Requery

Hahahahahaha ... oww, my sides, stop it ... ahhhh hahahaha! And I thought
Frasier was funny ...

[Bob Quintal]

ro******@gmail.com wrote in
news:11*********************@j73g2000cwa.googlegro ups.com:

Thanks. Had no intentions of going any further with Steve -
idiot. Why would anyone pay someone when there are more
competent experts on the newsgroups who are willing to help
for nothing?

Bob - do you mean the Row Source? If so, it is:
SELECT [Businesses].[Supermarket] FROM Businesses;

If I use your code above, I get the error message that "The
text you entered isn't an item in the list. Select an item
from the list....

OK, What is happening is this: your form and the combobox use the
same table. Until you update the table you can't get the data
you've entered to appear in the combobox. You can't update the
table unless the data has been added to the combobox.

Quick fix: Set the limit to list property to no..

The right way to fix this is to have a separate table containing
the lookup values for your combobox. Then you'd use the code you
posted to write to the lookup table, which then requeries the table
and allows it to be used..

Actually, database design theory says you should put a numeric ID
in your lookup table and the text, and store the ID in your main
table. It saves space and makes for easier changes when you need to
modify a lookup value.

--
Bob Quintal

PA is y I've altered my email address.