If not conditional loop 1

[madhula]

HI,
It has been long time i used Acess. I am trying to check a condition and edit the recordset.

there are two recordset rst_local and rst_pplops.I compare the records from each recordset and edit one of them.
The condition is
If rst_pplpos("Deptid")=rst_local("DeptId") And rst_pplpos("Level")=rst_local("Level") Then

rst_local("ClearCheck")=True.

End IF
rst_pplops.Close
rst_local.MoveNext
Loop

What my question and where i am failing is ..whenever there is a deptid which is avalable rst_local but not in rst_pplops the process fails.

Can anyone suggest me a better way to handle this.

I want to compare the deptids from each recordset and edit the other recordset if and only if the deptids match.otherwise go to the next record.

Thanks

 

[snyperx3]

Im not sure what this part means:
"whenever there is a deptid which is avalable rst_local but not in rst_pplops the process fails"

but i did notice that your not using
rst_local.Edit
rst_local.Update
around the change of ClearCheck

It also looks like were missing more code. Are you only cycling through the rst_local? its the only one with .MoveNext. And are you closing rs_pplops each iteration?

-Pete

 

[madhula]

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
rst_pplops.Close
rst_local.MoveNext
Loop

rst_local.Close
dbs.Close

Set rst_local = Nothing
Set rst_pplops = Nothing
Set dbs = Nothing




I am sorry i left out the code in the posting.Above is the code i have.rst_local is recordset from a table in the database and rst_pplops is the recordset with records from pplsoft table. If there is any deptid which is not in the local table and in the pplsoft table the process fails.I ma confused aroun the looping condition.I need help in looping.The condition should if and only if the deptid which is in rst_local is in rst_pplops proceed otherwise move to next record.


Thanks.

 

[snyperx3]

Wheres the
Do Until rst_local.EOF

-Pete

 

[madhula]

Do Until rst_local.EOF

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

'If CurrentDeptId = "D000073497" Then

'MsgBox (rst_pplops(LevelUp1) & " " & CurrentLevel)

'End If


'If rst_pplops("DeptId") <> CurrentDeptId Then


' MsgBox (rst_pplops("DeptId") & " " & CurrentDeptId)

' End If
rst_local.Edit

rst_local("LevelCheck") = True

rst_local.Update

End If


rst_pplops.Close

rst_local.MoveNext


Loop

rst_local.Close
dbs.Close

Set rst_local = Nothing
Set rst_pplops = Nothing
Set dbs = Nothing

 

[snyperx3]

if the do until is there...then you should not close rst_pplops until you have finished going through the locals. or are you wanting to cycle through both of them with nested loops. Either way you shouldnt close rst_pplops until you have finished the loops.

-Pete

 

[JerryKlmns]

madhula

Your code doesnt show the sql statement for those two recordsets so I 'm guessing here. Cant you do this kind of "update" using an update query?

 

[madhula]

Hello,
I have given the code for the function.

Sub DeptCheck_New()

Dim dbs As DAO.Database
Dim rst_pplops As DAO.Recordset
Dim rst_local As DAO.Recordset
Dim CurrentLevel As Integer
Dim CurrentDeptId As String
Dim LevelUp1 As String
Dim LevelUp2 As String
Dim tbl As DAO.TableDef

Set dbs = CurrentDb

dbs.TableDefs.Delete ("ups")
dbs.QueryDefs("sys_MakeUps").Execute
dbs.QueryDefs("sys_ClearLevelCheck").Execute

For Each tbl In dbs.TableDefs

If tbl.Name = "sysNewDepartments" Then

dbs.TableDefs.Delete (tbl.Name)

End If

Next

dbs.QueryDefs("sys_NewDepartments").Execute

Set rst_local = dbs.OpenRecordset("SELECT * FROM sysDeptDef")


Do Until rst_local.EOF

CurrentLevel = rst_local("Level")
CurrentDeptId = rst_local("DeptId")
LevelUp1 = "L" & CurrentLevel - 1 & "_DEPTID"
LevelUp2 = "L" & CurrentLevel - 2 & "_DEPTID"


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

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

rst_local.Edit

rst_local("LevelCheck") = True

rst_local.Update

End If

rst_pplops.Close

rst_local.MoveNext
Loop

rst_local.Close
dbs.Close

Set rst_local = Nothing
Set rst_pplops = Nothing
Set dbs = Nothing

End Sub


NOw,My problem is when the query for rst_pplops executes there is a deptid in table sysDeptdef which is not available in ups_new table.The query fails and gives an error record not found. I want to catch this(deptid) and delete it from sysDeptdef table.

Hope i explained my problem in a simple way.
Thanks for your reply.

 

[snyperx3]

after this line:
Set rst_pplops = dbs.OpenRecordset("SELECT " & LevelUp1 & "," & LevelUp2 & ",Dept_Level FROM ups WHERE DEPTID = '" & CurrentDeptId & "'")
you have to test for a record

If rst_pplops.EOF Or rs_pplops.RecordCount < 1 Then
     rst_local.Delete
Else
     [green]'do your code[/green]
End If

-Pete