Not In List Cbo Help

S

Stockwell43

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!!!
 
K

Klatuu

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
 
S

Stockwell43

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 said:
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 said:
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!!!
 
S

Stockwell43

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 said:
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 said:
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!!!
 
S

Stockwell43

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.
:blush:)

Klatuu said:
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 said:
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!!!
 
K

Klatuu

Glad you got it working.

Stockwell43 said:
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.
:blush:)

Klatuu said:
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 said:
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!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top