NotInList event VB help

[LyndsyJo]

(This is a case of being told, "Make this database, make it do this, this,
and this, and do it without any real training.")

I have a tiny table called "Color Table", and a main table called "Item
Table". "Item Table" has a field called "Color" that looks up the values in
"Color Table". In my form, if the color I need is not in the list, I want to
type the value and have it add it to my "Color Table" so that it is available
as a choice on my form for future records.

I have spent a couple hours looking through the discussion groups here, and
have followed several links that people have posted to sites that list Visual
Basic code to assist me in doing this. However, I know *nothing* about
Visual Basic, and while I tried several ways of changing the info in the
codes to match my database, I just don't know enough to make it work. Can
anyone give me a suggestion on how to make this work?

Here's the code I got from http://www.pacificdb.com.au/MVP/Code/NIL.htm:


Private Sub Combo0_NotInList(NewData As String, Response As Integer)

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO tblMyTable (SomeField) VALUES (""" & NewData &
""")", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

db.Close
Set db = Nothing

End Sub


And this is what I came up with:


Private Sub Color_NotInList(NewData As String, Response As Integer)

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO [Colors Table] (Color) VALUES (""" & NewData &
""")", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

db.Close
Set db = Nothing

End Sub



What am I doing wrong, or not doing at all? Please be gentle! Thank you.

 

[ruralguy via AccessMonster.com]

See if Mr. Green's explaination helps at all.

(This is a case of being told, "Make this database, make it do this, this,
and this, and do it without any real training.")

I have a tiny table called "Color Table", and a main table called "Item
Table". "Item Table" has a field called "Color" that looks up the values in
"Color Table". In my form, if the color I need is not in the list, I want to
type the value and have it add it to my "Color Table" so that it is available
as a choice on my form for future records.

I have spent a couple hours looking through the discussion groups here, and
have followed several links that people have posted to sites that list Visual
Basic code to assist me in doing this. However, I know *nothing* about
Visual Basic, and while I tried several ways of changing the info in the
codes to match my database, I just don't know enough to make it work. Can
anyone give me a suggestion on how to make this work?

Here's the code I got from http://www.pacificdb.com.au/MVP/Code/NIL.htm:

Private Sub Combo0_NotInList(NewData As String, Response As Integer)

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO tblMyTable (SomeField) VALUES (""" & NewData &
""")", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

db.Close
Set db = Nothing

End Sub

And this is what I came up with:

Private Sub Color_NotInList(NewData As String, Response As Integer)

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO [Colors Table] (Color) VALUES (""" & NewData &
""")", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

db.Close
Set db = Nothing

End Sub

What am I doing wrong, or not doing at all? Please be gentle! Thank you.

 

[LyndsyJo]

I'm sorry, but I don't know what you're talking about. Who's Mr. Green?
What explanation?

See if Mr. Green's explaination helps at all.

(This is a case of being told, "Make this database, make it do this, this,
and this, and do it without any real training.")

I have a tiny table called "Color Table", and a main table called "Item
Table". "Item Table" has a field called "Color" that looks up the values in
"Color Table". In my form, if the color I need is not in the list, I want to
type the value and have it add it to my "Color Table" so that it is available
as a choice on my form for future records.

I have spent a couple hours looking through the discussion groups here, and
have followed several links that people have posted to sites that list Visual
Basic code to assist me in doing this. However, I know *nothing* about
Visual Basic, and while I tried several ways of changing the info in the
codes to match my database, I just don't know enough to make it work. Can
anyone give me a suggestion on how to make this work?

Here's the code I got from http://www.pacificdb.com.au/MVP/Code/NIL.htm:

Private Sub Combo0_NotInList(NewData As String, Response As Integer)

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO tblMyTable (SomeField) VALUES (""" & NewData &
""")", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

db.Close
Set db = Nothing

End Sub

And this is what I came up with:

Private Sub Color_NotInList(NewData As String, Response As Integer)

Dim db As Database
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO [Colors Table] (Color) VALUES (""" & NewData &
""")", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

db.Close
Set db = Nothing

End Sub

What am I doing wrong, or not doing at all? Please be gentle! Thank you.

 

[Keith Wilby]

(This is a case of being told, "Make this database, make it do this, this,
and this, and do it without any real training.")

I have a tiny table called "Color Table", and a main table called "Item
Table". "Item Table" has a field called "Color" that looks up the values
in
"Color Table". In my form, if the color I need is not in the list, I want
to
type the value and have it add it to my "Color Table" so that it is
available
as a choice on my form for future records.

This works for me, just change the names where appropriate:

Private Sub cboManagerName_NotInList(NewData As String, Response As Integer)

strResponse = MsgBox("You entered " & NewData & " - is this the manager's
network logon ID?", vbYesNo, "Confirm Manager ID")
If strResponse = vbYes Then
Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String
Set db = CurrentDb
strSQL = "Select ManagerName from qcboManager"
Set rs = db.OpenRecordset(strSQL)
With rs
.AddNew
![ManagerName] = NewData
.Update
End With
rs.Close
Set rs = Nothing
db.Close
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.cboManagerName.Undo
End If

End Sub

Regards,
Keith.
www.keithwilby.com

 

[ruralguy via AccessMonster.com]

Sorry LyndsyJo,
I just forgot to add the link. D'oh! Here it is:
Mastering the Combo Box NotInList Event
http://www.fontstuff.com/access/acctut20.htm

It looks like Keith might have a solution for you as well.

I'm sorry, but I don't know what you're talking about. Who's Mr. Green?
What explanation?

See if Mr. Green's explaination helps at all.

[quoted text clipped - 53 lines]

 

[LyndsyJo]

That helped, I finally got it to work. (I think using two-word table names
was causing a problem). Thanks!

Sorry LyndsyJo,
I just forgot to add the link. D'oh! Here it is:
Mastering the Combo Box NotInList Event
http://www.fontstuff.com/access/acctut20.htm

It looks like Keith might have a solution for you as well.

I'm sorry, but I don't know what you're talking about. Who's Mr. Green?
What explanation?

See if Mr. Green's explaination helps at all.

[quoted text clipped - 53 lines]

What am I doing wrong, or not doing at all? Please be gentle! Thank you.

 

[ruralguy via AccessMonster.com]

Great news. Thanks for posting back with your success. Here's a few links
that you might find useful:
http://support.microsoft.com/?id=286335
http://support.microsoft.com/?id=321266
http://support.microsoft.com/?id=826763

That helped, I finally got it to work. (I think using two-word table names
was causing a problem). Thanks!

Sorry LyndsyJo,
I just forgot to add the link. D'oh! Here it is:

[quoted text clipped - 11 lines]