Not In List Cbo Help

Hello,

I have a form with a cbo that I am trying to make a Not in List box so users
can enter new catagories if need be that are not already in the table. The
cbo is named Catagory that is bound to a table named Catagory created through
the wizard. I found a website that gave me code to put behind the On Not in
List envent of the combo box but when I tried entering a different catagory,
it did not flag me or save it to the table. Here is the code I used:

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

On Error GoTo cboCatagory_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Catagory " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCatagory([Catagory]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Catagory has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a Catagory from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
cboCatagory_NotInList_Exit:
Exit Sub
cboCatagory_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboCatagory_NotInList_Exit
End Sub

Here is the site it came from:

http://www.fontstuff.com/access/acctut20.htm

Any help would be most appreciated!!

Thanks!!!

The code you posted it doing only half the job. The main problem is, it is
adding the new value to the table, but not to the form's recordset. To get
the new record in the recordset, you need to requery the form. Also, most
likely, you will want the newly added record to become the current record.
Here is my version of a Not In List event:

Private Sub cboClientSearch_NotInList(NewData As String, Response As
Integer)
Dim strSQL As String

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion +
_
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

Me.cboClientSearch.Undo
strSQL = "INSERT INTO tblClient ( MainName )SELECT """ & NewData &
""" AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[MainName] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboClientSearch.Undo
Response = acDataErrContinue
End If
End Sub


"Stockwell43" wrote in message
...
Hello,

I have a form with a cbo that I am trying to make a Not in List box so
users
can enter new catagories if need be that are not already in the table. The
cbo is named Catagory that is bound to a table named Catagory created
through
the wizard. I found a website that gave me code to put behind the On Not
in
List envent of the combo box but when I tried entering a different
catagory,
it did not flag me or save it to the table. Here is the code I used:

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

On Error GoTo cboCatagory_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Catagory " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCatagory([Catagory]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Catagory has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a Catagory from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
cboCatagory_NotInList_Exit:
Exit Sub
cboCatagory_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboCatagory_NotInList_Exit
End Sub

Here is the site it came from:

http://www.fontstuff.com/access/acctut20.htm

Any help would be most appreciated!!

Thanks!!!

Hi Klatuu,

If I plug this code in my Not in List event, your saying I should be able to
enter a catagory NOT in the table and have it enter it in and stay as part of
that record? I will change the names and give it a try. Thanks Klatuu!!!

"Klatuu" wrote:

The code you posted it doing only half the job. The main problem is, it is
adding the new value to the table, but not to the form's recordset. To get
the new record in the recordset, you need to requery the form. Also, most
likely, you will want the newly added record to become the current record.
Here is my version of a Not In List event:

Private Sub cboClientSearch_NotInList(NewData As String, Response As
Integer)
Dim strSQL As String

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion +
_
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

Me.cboClientSearch.Undo
strSQL = "INSERT INTO tblClient ( MainName )SELECT """ & NewData &
""" AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[MainName] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboClientSearch.Undo
Response = acDataErrContinue
End If
End Sub


"Stockwell43" wrote in message
...
Hello,

I have a form with a cbo that I am trying to make a Not in List box so
users
can enter new catagories if need be that are not already in the table. The
cbo is named Catagory that is bound to a table named Catagory created
through
the wizard. I found a website that gave me code to put behind the On Not
in
List envent of the combo box but when I tried entering a different
catagory,
it did not flag me or save it to the table. Here is the code I used:

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

On Error GoTo cboCatagory_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Catagory " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCatagory([Catagory]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Catagory has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a Catagory from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
cboCatagory_NotInList_Exit:
Exit Sub
cboCatagory_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboCatagory_NotInList_Exit
End Sub

Here is the site it came from:

http://www.fontstuff.com/access/acctut20.htm

Any help would be most appreciated!!

Thanks!!!

I must have something set up wrong because it is doing the same thing as the
other code. In my table I only have Catagory, should I have I CatagoryID and
Catagory? I thought the way these cbo worked was if you enter a name that is
not already in the table it should tell it is not in the table and if you
want to add it. Then, when you click ok, you should see in the drop down list
and can use it on another record. I never did one of these before that's why
I was researching it.

What am I doing wrong??

Thanks!!

"Klatuu" wrote:

The code you posted it doing only half the job. The main problem is, it is
adding the new value to the table, but not to the form's recordset. To get
the new record in the recordset, you need to requery the form. Also, most
likely, you will want the newly added record to become the current record.
Here is my version of a Not In List event:

Private Sub cboClientSearch_NotInList(NewData As String, Response As
Integer)
Dim strSQL As String

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion +
_
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

Me.cboClientSearch.Undo
strSQL = "INSERT INTO tblClient ( MainName )SELECT """ & NewData &
""" AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[MainName] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboClientSearch.Undo
Response = acDataErrContinue
End If
End Sub


"Stockwell43" wrote in message
...
Hello,

I have a form with a cbo that I am trying to make a Not in List box so
users
can enter new catagories if need be that are not already in the table. The
cbo is named Catagory that is bound to a table named Catagory created
through
the wizard. I found a website that gave me code to put behind the On Not
in
List envent of the combo box but when I tried entering a different
catagory,
it did not flag me or save it to the table. Here is the code I used:

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

On Error GoTo cboCatagory_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Catagory " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCatagory([Catagory]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Catagory has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a Catagory from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
cboCatagory_NotInList_Exit:
Exit Sub
cboCatagory_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboCatagory_NotInList_Exit
End Sub

Here is the site it came from:

http://www.fontstuff.com/access/acctut20.htm

Any help would be most appreciated!!

Thanks!!!

Got it, I didn't change the limit to list and the name was still Catagory
instead of cboCatagory. I need to pay better attention. sorry about all that.
)

"Klatuu" wrote:

The code you posted it doing only half the job. The main problem is, it is
adding the new value to the table, but not to the form's recordset. To get
the new record in the recordset, you need to requery the form. Also, most
likely, you will want the newly added record to become the current record.
Here is my version of a Not In List event:

Private Sub cboClientSearch_NotInList(NewData As String, Response As
Integer)
Dim strSQL As String

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion +
_
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

Me.cboClientSearch.Undo
strSQL = "INSERT INTO tblClient ( MainName )SELECT """ & NewData &
""" AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[MainName] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboClientSearch.Undo
Response = acDataErrContinue
End If
End Sub


"Stockwell43" wrote in message
...
Hello,

I have a form with a cbo that I am trying to make a Not in List box so
users
can enter new catagories if need be that are not already in the table. The
cbo is named Catagory that is bound to a table named Catagory created
through
the wizard. I found a website that gave me code to put behind the On Not
in
List envent of the combo box but when I tried entering a different
catagory,
it did not flag me or save it to the table. Here is the code I used:

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

On Error GoTo cboCatagory_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Catagory " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCatagory([Catagory]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Catagory has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a Catagory from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
cboCatagory_NotInList_Exit:
Exit Sub
cboCatagory_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboCatagory_NotInList_Exit
End Sub

Here is the site it came from:

http://www.fontstuff.com/access/acctut20.htm

Any help would be most appreciated!!

Thanks!!!

Glad you got it working.

"Stockwell43" wrote in message
...
Got it, I didn't change the limit to list and the name was still Catagory
instead of cboCatagory. I need to pay better attention. sorry about all
that.
)

"Klatuu" wrote:

The code you posted it doing only half the job. The main problem is, it
is
adding the new value to the table, but not to the form's recordset. To
get
the new record in the recordset, you need to requery the form. Also,
most
likely, you will want the newly added record to become the current
record.
Here is my version of a Not In List event:

Private Sub cboClientSearch_NotInList(NewData As String, Response As
Integer)
Dim strSQL As String

If MsgBox(NewData & " Is not in the list - Add " & NewData,
vbQuestion +
_
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

Me.cboClientSearch.Undo
strSQL = "INSERT INTO tblClient ( MainName )SELECT """ & NewData
&
""" AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[MainName] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboClientSearch.Undo
Response = acDataErrContinue
End If
End Sub


"Stockwell43" wrote in message
...
Hello,

I have a form with a cbo that I am trying to make a Not in List box so
users
can enter new catagories if need be that are not already in the table.
The
cbo is named Catagory that is bound to a table named Catagory created
through
the wizard. I found a website that gave me code to put behind the On
Not
in
List envent of the combo box but when I tried entering a different
catagory,
it did not flag me or save it to the table. Here is the code I used:

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

On Error GoTo cboCatagory_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Catagory " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCatagory([Catagory]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Catagory has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a Catagory from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If
cboCatagory_NotInList_Exit:
Exit Sub
cboCatagory_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboCatagory_NotInList_Exit
End Sub

Here is the site it came from:

http://www.fontstuff.com/access/acctut20.htm

Any help would be most appreciated!!

Thanks!!!