insert record

[madhula]

Dim CurrentLevel As Integer
Dim CurrentDeptId As Variant

Dim LevelUp1 As Variant
Dim LevelUp2 As Variant
Dim LobId As Variant
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


I am using the above code to insert record.Process fails at insert command.Can anyone suggest where i am going wrong.i ahven'e used vba in years.
Please help

 

[mp9]

If any of these values are text you need to put quotes in your string, e.g.

...& LobId & ",[red]'[/red]" & CurrentLevel & "[red]'[/red]," & Oc_Id ...

http://www22.brinkster.com/accessory/

 

[madhula]

i have used
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 & "')")


it still fails..

any suggestions

 

[Zion7]

What error message, are you getting?