Runtime error 3134

[madhula]

Dim CurrentLevel As Integer
Dim CurrentDeptId As Variant

Dim LevelUp1 As String
Dim LevelUp2 As String
Dim LobId As String
Dim AttId As Integer
Dim Oc_Id As Integer
Dim LevelCheck As Boolean

Set rst_check = dbs.OpenRecordset("Select dept_level,deptid from sysNewDepartments ")
Do Until rst_check.EOF
CurrentLevel = rst_check("dept_level")
CurrentDeptId = rst_check("deptid")
AttId = 2
Oc_Id = 5
LevelCheck = False

If CurrentLevel = 5 Then
LobId = "L" & 5 & "_DEPTID"
Else
LobId = CurrentLevel
End If

LevelUp1 = "L" & CurrentLevel - 1 & "_DEPTID"
LevelUp2 = "L" & CurrentLevel - 2 & "_DEPTID"
If rst_check("dept_level") <= 6 Then

dbs.Execute ("INSERT INTO sysDeptDef (DeptId,DeptId_Up1,DeptId_Up2,AttId,LobId,Level,Oc_id,LevelCheck) VALUES (" & _
CurrentDeptId & "," & LevelUp1 & "," & LevelUp2 & "," & AttId & "," & LobId & "," & CurrentLevel & "," & Oc_Id & "," & LevelCheck & ")")
End If
rst_check.MoveNext
Loop
rst_check.Close



The process fails with runtime error 3134 syntax error in insert statemnet...
Can anyone help

 

[PHV]

You have to surround the text values with single quotes:
dbs.Execute ("INSERT INTO sysDeptDef (DeptId,DeptId_Up1,DeptId_Up2,AttId,LobId,Level,Oc_id,LevelCheck) VALUES (" & _
CurrentDeptId & ",[!]'[/!]" & LevelUp1 & "[!]'[/!],[!]'[/!]" & LevelUp2 & "[!]'[/!]," & AttId & ",[!]'[/!]" & LobId & "[!]'[/!]," & CurrentLevel & "," & Oc_Id & "," & LevelCheck & ")")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[madhula]

I get the same error even after using the single quotes...i am struck with problem for so long............

 

[PHV]

What is the actual value of the SQL code you want to execute at the time the error is raised ?
Tip: Replace dbs.Execute with MsgBox or Debug.Print and post back the result.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[madhula]

INSERT INTO sysDeptDef(DeptId,DeptId_Up1,DeptId_Up2,AttId,LobId,Level,Oc_id,LevelCheck) VALUES ('D000139010','L4_DEPTID','L3_DEPTID',2,'L5_DEPTID',5,False)

I have chnaged the code to
MsgBox ("INSERT INTO sysDeptNew (DeptId,DeptId_Up1,DeptId_Up2,AttId,LobId,Level,LevelCheck) VALUES ('" & _
CurrentDeptId & "','" & rst_check(LevelUp1) & "','" & rst_check(LevelUp2) & "'," & AttId & ",'" & rst_check(LobId) & "','" & CurrentLevel & "'," & LevelCheck & ")")


The values seem to be right but still donot understand what is wrong..........

thanks for your rpely

 

[PHV]

Why is Oc_Id gone away from the values list ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[madhula]

I have removed that field and i am inserting into a new table sysDeptNew. Sorry i didnot paste the correct tabelname. I didnot wanted to mess with sysDeptDef so i created anotehr table same fields datatypes but with no oc_id.

 

[PHV]

A shoot in the dark:
dbs.Execute "INSERT INTO sysDeptNew (DeptId,DeptId_Up1,DeptId_Up2,AttId,LobId,Level,LevelCheck) VALUES ('" & _
CurrentDeptId & "','" & rst_check(LevelUp1) & "','" & rst_check(LevelUp2) & "'," & AttId & ",'" & rst_check(LobId) & "','" & CurrentLevel & "'," & LevelCheck & ")[!];[/!]"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[madhula]

same problem...

 

[madhula]

I think the problem is with currentlevel.i have tried inserting one at a time..it breaks when i insert currentlevel..
Level field in the table is a number....currentlevel is an integer

do you think anything is wrong with mismatch of datatypes?

 

[madhula]

i have used this code

dbs.Execute ("INSERT INTO sysDeptDef (DeptId,DeptId_Up1,DeptId_Up2,AttId,LobId,LevelCheck,Oc_Id,Level) VALUES ('" & CurrentDeptId & "','" & rst_check(LevelUp1) & "','" & rst_check(LevelUp2) & "'," & AttId & ",'" & rst_check(LobId) & "', " & LevelCheck & "," & Oc_Id & "," & rst_check(CurrentLevel) & ")")

 

[PHV]

Just a guess:
& "', " & LevelCheck & ",[highlight] [/highlight]" & Oc_Id & ",[highlight] [/highlight]" & rst_check([highlight]"dept_level"[/highlight]) & ")")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[madhula]

I tried that ...didnot work..