NotInList compile error

[Angi]

My second cbo is dependent on the first. I want the user to be able to
create a new subclass if notinlist. The error I'm getting is

Too few parameters. Expected 1.

on the Execute MySql line.

It was 2 but I change the NewData to '" & NewData &"' and now it's one.
I tried doing the same to CurCID but then I get a data type
mismatch...string instead of integer. I tried Int(curCID) but that
didn't work either. What is it that I need here?

TIA!

Private Sub cboSubClass_NotInList(NewData As String, Response As
Integer)
Dim MySql As String
Dim CurCID As Integer

Beep
CurCID = Me.cboClass.Column(0)

If MsgBox("'" & NewData & "' is currently not an existing subclass.
" & vbCrLf _
& "Would you like to add '" & NewData & "' to the
menu?", vbInformation + vbOKCancel, "Info") = vbOK Then

MySql = "INSERT INTO Subclass ( ClassID, SubClassName ) SELECT
Subclass.ClassID, Subclass.SubClassName FROM Subclass " & _
"WHERE (((Subclass.ClassID)= CurCID ) AND ((Subclass.SubClassName)=
'" & NewData & "' ));"

CurrentDb.Execute MySql
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.cboClass = Null
End If

End Sub

 

[Wayne Morgan]

You're making the SQL too complicated. You need to add the information in
NewData and CurCID to the table. Yet you have added a SELECT statement that
is trying to find these values in the table already, then add them.

Try this instead:
MySQL = "INSERT INTO Subclass (ClassID, SubClassName) SELECT " & CurCID & "
As Expr1, """ & NewData & """ As Expr2;"

Essentially, for the SQL, you are just trying to add two "constants" to the
table, not a list of items that you have selected from another table.

The triple quotes are to leave quotes around the value of NewData after
assigning the string to MySQL.

 

[Angi]

Wayne,
Thank you! I knew that sql statement looked ugly. That's what I get
for using the query builder.

Angi