Working with ADO recordsets and Queries?

Hi,
Im trying to delete unwanted data from a table using ADO recordsets and the 'Find' and 'Delete' queries.
.rscmdTemp contains project cost records.
.rscmdProjStatus contains a list of completed projects.
I am trying to delete any records from .rscmdTemp where the cost record relates to a project that has not been completed.

Im using the 'Find' method (Part 1 of code example) to see if each record is for a completed project. I assume that if .rscmdProjStatus.EOF after the 'Find' search then no matching record was found...is this correct?

Also how can I delete all records in a recordset where the contract code matches the current contract code ie in part 2 of code example.


With DataEnvironment1
.Connection2.Open
.rscmdTemp.Open
.rscmdProjStatus.Open
.rscmdTemp.MoveFirst

PART 1
Do While Not .rscmdTemp.EOF
.rscmdProjStatus.Find ("WO = '" & .rscmdTemp!Contract & "'")

PART 2
If .rscmdProjStatus.EOF Then
strSQL = "DELETE FROM .rscmdTemp WHERE .rscmdTemp!Contract = .rscmdTemp!Contract"
Set Temp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)
Set Temp = Nothing
End If
.rscmdTemp.Close
.rscmdProjStatus.Close
.Connection2.Close
End With


Any help would be much appreciated.... Cheers

Hi again,

Im just replying to my question because no one else will...
by the way I am still stuck on the same problem.

Any Help?

I've got things working after about 4 hours of trying different things. Code is below. If you have a better way of doing this please let me know.

With DataEnvironment1
.Connection2.Open
.rscmdTemp.Open
.rscmdProjStatus.Open
.rscmdTemp.MoveFirst

Do While Not .rscmdTemp.EOF
.rscmdProjStatus.MoveFirst
.rscmdProjStatus.Find ("WO = '" & .rscmdTemp!Contract & "'")
If .rscmdProjStatus.EOF Then
strSQL = "DELETE * FROM tblTemp WHERE Contract = '" & .rscmdTemp!Contract & "'"
Set Temp = DataEnvironment1.Connection1.Execute(strSQL, , adCmdText)
Set Temp = Nothing
.rscmdTemp.Requery
Else
.rscmdTemp.MoveNext
End If
Loop