INSERT INTO syntax error

[iamnu]

This code is giving me a syntax error. I can't find the problem.

CurrentDb.Execute "INSERT INTO " & zDepartments & "([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', dirid );"

 

[AccessVandal via AccessMonster.com]

You're missing some double quotes.

CurrentDb.Execute "INSERT INTO " & zDepartments & "([DeptName],
[DirectoryID]) VALUES ('" & NewData & “’,†& dirid &â€);"

This code is giving me a syntax error. I can't find the problem.

CurrentDb.Execute "INSERT INTO " & zDepartments & "([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', dirid );"

 

[iamnu]

You're missing some double quotes.

CurrentDb.Execute "INSERT INTO " & zDepartments & "([DeptName],
[DirectoryID]) VALUES ('" & NewData & "'," & dirid &");"

iamnuwrote:

This code is giving me a syntax error. I can't find the problem.

CurrentDb.Execute "INSERT INTO " & zDepartments & "([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', dirid );"

Thanks for the response!

I copied your code, and I'm still getting a syntax
error...specifically:
Run-time error '3134':
Syntax error in INSERT INTO statement.

I should have provided more information, so let me START again. Here
is my ORIGINAL code, which executes okay, but then gives me the
following message, indicating that the data did not get inserted into
the table. "The text you entered is isn't an item in the list.
Select an item from the list, or enter text that matches one of the
listed items."

Private Sub CboDeptName_NotInList(NewData As String, Response As
Integer)
Dim byt As Byte
byt = MsgBox("Do you want to add this new Business Department to
the Lookup Table?", vbYesNo)
If byt = vbYes Then
Response = acDataErrAdded
CurrentDb.Execute "INSERT INTO zDepartments(DeptName)
VALUES( """ & NewData & """);"
End If
End Sub

Here is the Row Source for the table.

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneBook]!
[PhoneBookSubForm]![cboDirectory]))
ORDER BY zDepartments.DeptName;

 

[John W. Vinson]

This code is giving me a syntax error. I can't find the problem.

CurrentDb.Execute "INSERT INTO " & zDepartments & "([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', dirid );"

If dirid is a VBA variable, you need to concatenate its value into the SQL
string. Right now it looks like the SQL string you're trying to execute (given
guesses for the values of the variables) is

INSERT INTO ZZZ([DeptName], [DirectoryID] VALUES ('Research', dirid);

JET will have no notion what to do with dirid. Perhaps you want

....VALUES ('" & NewData & "', " & dirid & " );"


John W. Vinson [MVP]

 

[iamnu]

This code is giving me a syntax error. I can't find the problem.

CurrentDb.Execute "INSERT INTO " & zDepartments & "([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', dirid );"

If dirid is a VBA variable, you need to concatenate its value into the SQL
string. Right now it looks like the SQL string you're trying to execute (given
guesses for the values of the variables) is

INSERT INTO ZZZ([DeptName], [DirectoryID] VALUES ('Research', dirid);

JET will have no notion what to do with dirid. Perhaps you want

...VALUES ('" & NewData & "', " & dirid & " );"

John W. Vinson [MVP]

Thanks for the reply John.
I'm still getting a syntax error, but I found a solution to my problem
at the following link:
http://www.mvps.org/access/forms/frm0015.htm

I modified the posted code as by adding three lines as follows:
Dim dirid ' ' ' New line
dirid=Me.DirectoryID ' ' ' New line
rs!DirectoryID = dirid ' ' ' New line
rs!DeptName = NewData ' ' ' Part of original code

I'd still be interested in why I can't get my own code to work, which
now looks like this:

Private Sub CboDeptName_NotInList(NewData As String, Response As
Integer)
Dim byt As Byte, dirid
dirid = Me.DirectoryID
byt = MsgBox("Do you want to add this new Business Department to
the Lookup Table?", vbYesNo)
If byt = vbYes Then
Response = acDataErrAdded
CurrentDb.Execute "INSERT INTO & zDepartments & ([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', " & dirid & " );"
End If
End Sub

 

[John W. Vinson]

I'd still be interested in why I can't get my own code to work, which
now looks like this:

Private Sub CboDeptName_NotInList(NewData As String, Response As
Integer)
Dim byt As Byte, dirid

This Dims dirid as a Variant since you don't specify an As. If it should be a
Long Integer then it would be best to Dim it as Long.

dirid = Me.DirectoryID
byt = MsgBox("Do you want to add this new Business Department to
the Lookup Table?", vbYesNo)
If byt = vbYes Then
Response = acDataErrAdded
CurrentDb.Execute "INSERT INTO & zDepartments & ([DeptName],

zDepartments is not visibly defined here, for one thing, and your ampersands
seem to be missing. What is zDepartments? If it's a tablename in a string
variable you whould be using something like

CurrentDb.Execute "INSERT INTO [" & zDepartments & "] ([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', " & dirid & " );"
End If
End Sub

You say "not working" - what's not working? You chose to ignore my earlier
suggestion to use dbFailOnError to trap query errors I see; if you're still
getting an error in the query (likely given the above!) it will silently fail
and you'll not see any error message.

John W. Vinson [MVP]

 

[iamnu]

I'd still be interested in why I can't get my own code to work, which
now looks like this:

Private Sub CboDeptName_NotInList(NewData As String, Response As
Integer)
Dim byt As Byte, dirid

This Dims dirid as a Variant since you don't specify an As. If it should be a
Long Integer then it would be best to Dim it as Long.

dirid = Me.DirectoryID
byt = MsgBox("Do you want to add this new Business Department to
the Lookup Table?", vbYesNo)
If byt = vbYes Then
Response = acDataErrAdded
CurrentDb.Execute "INSERT INTO & zDepartments & ([DeptName],

zDepartments is not visibly defined here, for one thing, and your ampersands
seem to be missing. What is zDepartments? If it's a tablename in a string
variable you whould be using something like

CurrentDb.Execute "INSERT INTO [" & zDepartments & "] ([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', " & dirid & " );"
End If
End Sub

You say "not working" - what's not working? You chose to ignore my earlier
suggestion to use dbFailOnError to trap query errors I see; if you're still
getting an error in the query (likely given the above!) it will silently fail
and you'll not see any error message.

John W. Vinson [MVP]

John,

I did NOT say "not working", and you did NOT provide me with a
suggestion to use dbFailOnError. I'm sure you have mistaken me with
someone else.

What I did say is that I'm getting a SYNTAX error on the line of code,
which I have now updated with your most recent suggestion, as follows:
CurrentDb.Execute "INSERT INTO [" & zDepartments & "] ([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', " & dirid & " );"
The above line gives me a SYNTAX error.

I changed my DIM statement to:
DIM dirid as Long
and yes, zDepartments is a Table.

I provided you with the Row Source for this table in a previous
response.

 

[John W. Vinson]

I did NOT say "not working", and you did NOT provide me with a
suggestion to use dbFailOnError. I'm sure you have mistaken me with
someone else.

I beg your pardon! You're quite correct, I was remembering another thread.

What I did say is that I'm getting a SYNTAX error on the line of code,
which I have now updated with your most recent suggestion, as follows:
CurrentDb.Execute "INSERT INTO [" & zDepartments & "] ([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', " & dirid & " );"
The above line gives me a SYNTAX error.

I changed my DIM statement to:
DIM dirid as Long
and yes, zDepartments is a Table.

Try dim'ing strSQL as a String; setting strSQL to your string concatenation;
and seeing what is actually ending up in the string. You could then copy and
paste the string into the VBA window of a new Query to see why Access
considers it an error, or post it here if you prefer. Without being there I
can't see what the error might be!

John W. Vinson [MVP]

 

[iamnu]

I did NOT say "not working", and you did NOT provide me with a
suggestion to use dbFailOnError. I'm sure you have mistaken me with
someone else.

I beg your pardon! You're quite correct, I was remembering another thread.

What I did say is that I'm getting a SYNTAX error on the line of code,
which I have now updated with your most recent suggestion, as follows:
CurrentDb.Execute "INSERT INTO [" & zDepartments & "] ([DeptName],
[DirectoryID]) VALUES ('" & NewData & "', " & dirid & " );"
The above line gives me a SYNTAX error.

I changed my DIM statement to:
DIM dirid as Long
and yes, zDepartments is a Table.

Try dim'ing strSQL as a String; setting strSQL to your string concatenation;
and seeing what is actually ending up in the string. You could then copy and
paste the string into the VBA window of a new Query to see why Access
considers it an error, or post it here if you prefer. Without being there I
can't see what the error might be!

John W. Vinson [MVP]

Excellent Suggestion!

I never thought of looking at the actual code that was being
generated.
I think this suggestion should be posted in most all of the "INSERT
INTO" questions.
I got it to work (no syntax error) and the table has been updated.
Better yet, I learned something!

Thanks for your help and suggestion.
Bernie

 

[John W. Vinson]

I got it to work (no syntax error) and the table has been updated.
Better yet, I learned something!

Glad to have been of assistance!

John W. Vinson [MVP]