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