Insert Query..Invalid Operation

[vbjohn]

This is my query and I am not sure why it is calling an invalid operation...

----CODE--------------

Dim sSql As String
Dim sFName As String
Dim sLName As String
Dim sEmpNum As String
Dim db As DAO.Database
Dim wks As Workspace
Dim tblDef As TableDef
Dim rst As DAO.Recordset

i = 0

sFName = LTrim$(Right$(Combo8.Value, Len(Combo8.Value) - InStr(Combo8.Value, ",")))
sLName = Left$(Combo8.Value, InStr(Combo8.Value, ",") - 1)

Set db = CurrentDb()
sSql = "SELECT [EMP#] FROM EMPINFO WHERE [FIRST NAME] = '" & sFName & "' AND [LAST NAME] = '" & sLName & "'"
Set rst = db.OpenRecordset(sSql)

sEmpNum = rst.Fields("EMP#").Value
rst.Close

Do Until strGCode(i) = ""
sSql = "INSERT INTO FIXASSET (BARCODE, [ITEM NOTE], CATEGORY, [G-CODE], [EMP#], MFLAG )" & _
" SELECT '" & strBarcode(i) & "', '" & strItemNote(i) & "', '" & strCategory(i) & "', '" & strGCode(i) & "', '" & sEmpNum & "', '-1';"

Set rst = db.OpenRecordset(sSql)
db.Execute sSql

i = i + 1
Loop

i = 0

------END CODE---------


When I use the DEBUG.PRINT sSql this is what comes out.

"INSERT INTO FIXASSET (BARCODE, [ITEM NOTE], CATEGORY, [G-CODE], [EMP#], MFLAG ) SELECT '06116', 'GPVA5', 'LT', '033', '20069', '-1';"

 

[anna the third]

Hi,

try to build your code that the result when debug.print is something like

INSERT INTO FIXASSET (BARCODE, [ITEM NOTE], CATEGORY, [G-CODE], [EMP#], MFLAG ) VALUES ('06116', 'GPVA5', 'LT', '033', '20069', '-1')

no quotes around the sql statement
no ; at the end
use VALUES instead of SELECT

It might be usefull to create the query using the access auery builder first. You can check your SQL statements much easier then in code.

anna

 

[Pat Hartman]

I see two major problems. The first is a syntax one. You have confused the two different formats for an Insert statement. You are using the multi-record format when in fact you are doing a single record at a time. The following is directly from help:

"Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])"

The second problem is that some of your fields seem as though they should be numeric values. Numeric values should not be surrounded by quotes.

 

[vbjohn]

Ok. I changed it and I still get the message.


sSql = "INSERT INTO FIXASSET (BARCODE, [ITEM NOTE], CATEGORY, [G-CODE], [EMP#], MFLAG )" & _
" VALUES ('" & strBarcode(i) & "', '" & strItemNote(i) & "', '" & strCategory(i) & "', '" & strGCode(i) & "', " & sEmpNum & ", '-1')"


'Debug.Print sSql
INSERT INTO FIXASSET (BARCODE, [ITEM NOTE], CATEGORY, [G-CODE], [EMP#], MFLAG ) VALUES ('06116', 'GPVA5', 'LT', '033', 20069, '-1')


John-

 

[vbjohn]

I figured out my problem.. Thanks for all your help!