Not In List Event Problem

[cdogstu99]

I have a combo box on a form i'm trying to use the not in list event....
I'm getting an error when trying to imput a value in the combo box..
My errror says error with Inster Into Syntax...

Here's my code

Private Sub Firm_Description_NotInList(NewData As String, Response As Integer)
On Error GoTo Firm_Description_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The firm " & Chr(34) & NewData & _
Chr(34) & " is not currently in the database." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Prospect Database")
If intAnswer = vbYes Then
strSQL = "INSERT INTO Firm ID([Firm_Description]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new firm has been added to the list." _
, vbInformation, "Prospect Database"
Response = acDataErrAdded
Else
MsgBox "Please choose a firm from the list." _
, vbInformation, "Prospect Database"
Response = acDataErrContinue
End If
Firm_Description_NotInList_Exit:
Exit Sub
Firm_Description_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Firm_Description_NotInList_Exit
End Sub

 

[sredworb]

Try This

Private Sub cbo"Name"_NotInList(NewData As String, Response as Integer)
Dim strMsg As String
Dim rst As DAO.Recordset
Dim db As DAO.Database

strMsg = "'" & NewData & "' is not in the list. "
strMsg = strMsg & "Would you like to add it?"
If vbNo = MsgBox(strMsg, vbYesNo + vbQuestion,"New Company") Then
Response = acDataErrDisplay

Else

Set db = CurrentDb()
set rst = db.OpenRecordset(tbl"Name")
rst.AddNew
rst("Company") = NewData
rst.update
Response = acDataErrAdded
rst.Close

End If
End Sub

 

[RuralGuy]

Is the name of your table "Firm ID"? If so then because of the embedded space in the name, you need to enclose the name in brackets!

strSQL = "INSERT INTO [B][COLOR="Red"][Firm ID][/COLOR][/B] ([Firm_Description]) " & _
"VALUES ('" & NewData & "');"

 

[cdogstu99]

RG ...thanks...it sort of worked, but not really, after typing in a name, the prompt comes up to ask if you want to add the new firm, and after choosing yes it says that it has been added to the database....but, then a prompt comes up that says, "The text you entered isn't an item in the list" ...Also, i don't see any new records added to the FIRM ID Table.

Just to note also, My Firm ID table has other fields asides from Firm Description...Should that matter??

 

[RuralGuy]

Insert is for updating existing records. You will need to Append if you want to add a new record.

 

[pbaldy]

Insert is for updating existing records. You will need to Append if you want to add a new record.

This must be a typo?

 

[cdogstu99]

Uhhh..yeah maybe some more clarification here?????

 

[pbaldy]

It looks like RG is offline. INSERT INTO is the correct SQL to add a record (and I know RG knows that, which is why I think it was a typo). If there are other fields, it could be that one or more of them are required, so the insert would fail. Is that the case?

 

[cdogstu99]

yeah pb..my table FIRM ID contains.....the following

An Auto Number
FIRM_ID (which is a three letter code i created, but now looking back probably shouldn't have)
Firm Description --which is the value i'm trying to insert
Firm Address
Firm State
Firm City

But the last three are blank for everything else in the table...

I did create a dummy table with only an auto number, and Firm Description, and it does input the value, but i still got "The text you entered isn't an item in the list" after it was updated. Yet with the current FIRM ID table, the value is not even being inserted into the table.

 

[RuralGuy]

When you execute:
Response = acDataErrAdded
in your code, Access requeries the RowSource of the ComboBox and it the data has been properly inserted will now find the new entry. Paul was *absolutely* correct about my post and boy was I ever all wet on that one. Thanks for pitching in Paul. BTW, the [FIRM ID] table (query) *IS* the RowSource for your ComboBox, isn't it?

 

[cdogstu99]

RG, the row source for the ComboBox is a query from the FIRM ID Table which only selects the actual Firm Description.

 

[RuralGuy]

Is the [FIRM_ID] field the primary key? You would need to supply that at the same time and the new Description for the update to take. Have you checked the table to see if the new Description has been added?

 

[cdogstu99]

Yep..the Firm_ID field is the primary key and is essentially just a three letter identifier i input to link up with another table. I'm guessing that i should have just used an autonumber for this??? But when i make a dummy table with just an autonumber (as primary key) and firm description, it is updating the dummy table, but still giving me that popup with the "text you entered is not in the list"....

 

[RuralGuy]

Post the NotInList code you are using for the dummy table from Private Sub to End Sub. Surround your code with the code tags to preserve the formatting please. Yes, an AutoNumber would be a LOT better!

 

[cdogstu99]

Private Sub Firm_Description_NotInList(NewData As String, Response As Integer)
On Error GoTo Firm_Description_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The firm " & Chr(34) & NewData & _
Chr(34) & " is not currently in the database." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Prospect Database")
If intAnswer = vbYes Then
strSQL = "INSERT INTO Table2([Firm_Description])  " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new firm has been added to the list." _
, vbInformation, "Prospect Database"
Response = acDataErrAdded
Else
MsgBox "Please choose a firm from the list." _
, vbInformation, "Prospect Database"
Response = acDataErrContinue
End If
Firm_Description_NotInList_Exit:
Exit Sub
Firm_Description_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Firm_Description_NotInList_Exit
End Sub

 

[RuralGuy]

This one is a little easier to read:

Private Sub Firm_Description_NotInList(NewData As String, Response As Integer)
On Error GoTo Firm_Description_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The firm " & Chr(34) & NewData & _
            Chr(34) & " is not currently in the database." & vbCrLf & _
            "Would you like to add it to the list now?" _
            , vbQuestion + vbYesNo, "Prospect Database")
If intAnswer = vbYes Then
   strSQL = "INSERT INTO Table2([Firm_Description])  " & _
            "VALUES ('" & NewData & "');"
   DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
   DoCmd.SetWarnings True
   MsgBox "The new firm has been added to the list." _
         , vbInformation, "Prospect Database"
   Response = acDataErrAdded
Else
   MsgBox "Please choose a firm from the list." _
         , vbInformation, "Prospect Database"
   Response = acDataErrContinue
End If

Firm_Description_NotInList_Exit:
   Exit Sub
   
Firm_Description_NotInList_Err:
   MsgBox Err.Description, vbCritical, "Error"
   Resume Firm_Description_NotInList_Exit
End Sub

Could you post the RowSource for the ComboBox and the RowType.

 

[cdogstu99]

Guess what...i think you might have just solved my problem with that last question....The Row Source is a query that is selecting from the FIRM ID Table.... uggh....sorry....but thank you...I'm gonna have to change all my firm id's to autonumbers.......thanks !!!!!!!!!!!

 

[RuralGuy]

Glad you got it sorted. Have fun!