Problem with deleting record

[madhula]

HI,

I am using the following function to delete a record from sysDeptDef table which is not present in ups_new table. I have two rows for the same deptid but diff attid. when i use the delete command the two rows get deleted but when the loop executes it cannot find the deptid so it fails.can please anyone suggest me how to handle this situation.

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_local = dbs.OpenRecordset("SELECT * FROM sysDeptDef")

Do Until rst_local.EOF

[COLOR=red yellow]CurrentDeptId = rst_local("DeptId") (Error record not found)[/color]
CurrentLevel = rst_local("DeptLevel")

LevelUp1 = "L" & CurrentLevel - 1 & "_DEPTID"
LevelUp2 = "L" & CurrentLevel - 2 & "_DEPTID"

Set rst_pplops = dbs.OpenRecordset("SELECT " & LevelUp1 & "," & LevelUp2 & ",Dept_Level,EFF_STATUS,DeptId FROM ups_new WHERE DEPTID = '" & CurrentDeptId & "'")

If rst_pplops.RecordCount <> 0 Then

If rst_pplops(LevelUp1) = rst_local("DeptId_Up1") And _
rst_pplops(LevelUp2) = rst_local("DeptId_Up2") And _
rst_pplops("Dept_Level") = CurrentLevel And rst_pplops("EFF_STATUS") = "A" And rst_pplops("DeptId") = CurrentDeptId Then

rst_local.Edit
rst_local("LevelCheck") = True
rst_local.Update

End If

Else
Set rst_check = dbs.OpenRecordset("select Deptid,AttId from sysDeptDef where Deptid='" & CurrentDeptId & "'")
If rst_check.RecordCount > 0 Then

MsgBox "Deptid '" & CurrentDeptId & "' to be deleted since record not found in pplops and "

[COLOR=red yellow]dbs.Execute ("delete AttId from sysDeptDef where DeptId='" & CurrentDeptId & "'")[/color]
End If
End If

rst_pplops.Close

rst_local.MoveNext
End If
Loop

rst_local.Close





Thanks for your reply

 

[SimonSellick]

What can't it find - the column 'DeptId' in rst_local, or a record?

Your delete statement doesn't need a column name - you can only delete entire rows. If you just want to get rid of a column value, use update to set it null.

 

[Golom]

Just some questions

Do you want to delete both records or just one of them that has a particular AttId?

What is the actual error message? You stated that it's Record Not Found but the highlighted statement isn't attempting to "Find" a record, it's just referencing a field in the current record.

Have you considered deleting from the recordset rather than running a separate delete query?

This

Set rst_pplops = dbs.OpenRecordset( _
[COLOR=blue]"SELECT " & LevelUp1 & "," & LevelUp2[/color] & _
",Dept_Level,EFF_STATUS,DeptId FROM ups_new WHERE DEPTID = '" & CurrentDeptId & "'")

Appears to be creating a recordset whose first two fields will have values (i.e. LevelUp1 and LevelUp2) but no names (there's no alias specified for them.)

Your reference to those fields in the next statement

If rst_pplops(LevelUp1) = rst_local("DeptId_Up1") And _
   rst_pplops(LevelUp2) = rst_local("DeptId_Up2") And _

Looks for Field Numbers "LevelUp1" and "LevelUp2" and that will work correctly only if "LevelUp1" and "LevelUp2" have the values "0" and "1" respectively.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[madhula]

Yes i can just delete the entire row.I was just playing with the code to try my luck. when i delete the netire row with that deptid..i think both the rows with same deptid are deleted.when the process searches for next record it cannot find the record since it is already deleted and process fails with error runtime error 3167 record is deleted.
Can i just delete the first row with the deptid and loop the process for next record and delete it if it has same deptid.
hope i explained my problem well.
Thanks for your reply

 

[madhula]

I just tried replacing

dbs.Execute ("delete AttId from sysDeptDef where DeptId='" & CurrentDeptId & "'")

with

rst_local.delete

It worked
Thanks for your replies

 

[ddiamond]

Does the code you posted compile without errors? I counted 3 if statements, and 4 end if statements. That means that you have an if - end if mis-match. My guess is that the end if before the loop statement should not be there.

 

[ddiamond]

You can ignore my last question. I see you solved your problem while I was typing my question.