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_Not InList(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 = acDataErrContin ue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordse t("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!NameOfCombob ox.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******@pcdata sheet.com

<ro******@gmail .com> wrote in message
news:11******** **************@ j73g2000cwa.goo glegroups.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_Not InList(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 = acDataErrContin ue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordse t("Businesses ", dbOpenDynaset)
Rs.AddNew
Rs![Supermarket] = NewData
Rs.Update
Response = acDataErrAdded
End If
End Sub

Thanks...Rob

[StopThisAdvertising]

"PC Datasheet" <No****@Spam.Co m> schreef in bericht news:me******** ********@newsre ad3.news.pas.ea rthlink.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******@pcdata sheet.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.



"StopThisAdvert ising" <StopThisAdvert ising@DataShit> wrote in message
news:44******** **************@ text.nova.plane t.nl...

"PC Datasheet" <No****@Spam.Co m> schreef in bericht
news:me******** ********@newsre ad3.news.pas.ea rthlink.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******@pcdata sheet.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**********@y ahoo.com> schreef in bericht news:5V******** *********@torna do.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.plane t.nl...

"Doc Holiday" <te**********@y ahoo.com> schreef in bericht
news:5V******** *********@torna do.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.goo glegroups.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_Not InList(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 = acDataErrContin ue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set Rs = Db.OpenRecordse t("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.Co m> wrote in
news:me******** ********@newsre ad3.news.pas.ea rthlink.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!NameOfCombob ox.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.Co m> wrote in message
news:me******** ********@newsre ad3.news.pas.ea rthlink.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!NameOfCombob ox.Requery

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