Access VBA and records 1

[ease20022002]

Hi All,

I am trying to compare records in an Access table using VBA and I really want to do this in VBA. Please don't respond if you are going to suggest running various queries, etc., through the query "wizard".

For example, I have a table with two important fields, say Field1 (string) and Field2 (double). What I am doing is comparing the 1st record in Field1 with the next record down in Field1. If the value in those records are equal, then I want to goto Field2 and check to see if the corresponding records in are = to 0. If they are equal, I want to delete them.

I have code that works well, but when I delete the table and fill it with test information again, when I cycle through all the records using .movenext, etc., many times the next record that is selected is a record that appears to be from the previous deleted table and in that order. Basically, when the code hits .movenext, it doesn't move to the next record, it moves farther down to one that was there previously.

I guess my question is, is there any way to refresh the entire recordset so it doesn't maintain and old sort or data when I run it on a new table?

Here is my code and I hope you understand.

Sub DeleteDups()
Dim db As Database, rst As DAO.Recordset
Dim rcdOne As String, rcdTwo As String
Dim rcdThree As Double, rcdFour As Double
Dim rcdCount As Integer, rcdCount2 As Integer
Dim strSQLsort As String
Set db = CurrentDb
Set rst = db.OpenRecordset("Table1", dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
rcdCount = 1
rcdCount = 0

Do While Not rst.EOF
With rst
rcdOne = rst!mcp
rcdThree = rst!quantity1
.MoveNext
If .EOF Then
.MoveLast
.MoveFirst
rcdCount = .RecordCount
If rcdCount = rcdCount2 Then
Exit Do
End If
rcdOne = rst!mcp
rcdThree = rst!quantity1
.MoveNext
End If
rcdTwo = rst!mcp
rcdFour = rst!quantity1
If rcdOne = rcdTwo Then
If rcdThree + rcdFour = 0 Then
.MovePrevious
.Delete
.MoveNext
.Delete
.MoveLast
.MoveFirst
rcdCount2 = .RecordCount
End If
End If
End With
Loop
End Sub

 

[PHV]

In relational database the records aren't sorted in a table, you have to use the ORDER BY clause in a SELECT instruction.
So, replace this:
Set rst = db.OpenRecordset("Table1", dbOpenDynaset)
with something like this:
Set rst = db.OpenRecordset("SELECT * FROM Table1 ORDER BY relevant field list", dbOpenDynaset)

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

 

[ease20022002]

Thank you very much for the help. It worked like a charm.