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

 

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