Looping condition Error 1

[snowmman99]

I have a problem and can not figure it out. I have duplicates in one column and a second column with data but located below the duplicate records with no duplicate records to tie it in. The duplicate records are above the criteria for deletion. I would like to delete this criteria from the table. I have tried to match duplicate records and look for the criteria and set it to yes.
This is what i have so far.

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Tbl_out")
Set rs2 = db.OpenRecordset("TblMFOEDLimport")

If rs1.RecordCount = 0 Then Exit Sub

rs1.MoveFirst

Do Until rs1.EOF
If rs2.RecordCount = 0 Then Exit Sub
rs2.MoveFirst
Do Until rs2.EOF
If rs1![Docno] = rs2![I-docno] And rs2![I-Remarks] = "*PT ORD*" Then
rs2.Edit
rs2("DELETE") = "Yes"
rs2.Update
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Sub

 

[kjv1611]

So what error are you getting, and at what line?

 

[jjlogan]

When comparing to wild card (as in And rs2![I-Remarks] = "*PT ORD*" Then)
try two things:
1) Use the syntax Like "....." instead of using "="
2) In ADO the wild card I think is a % symbol

Jeff

 

[snowmman99]

Thanks for the input. The line the PT ORD is on is all Null exept for the amount and remarks "PT ORD" and i can not seem to pull it. Any ideas?
Previos post I asked if i could copy the docno above it and paste it in the null cell. This way I can execute a delete on the dup docno with PT ORD in the remarks.

 

[jjlogan]

Another thought to consider (I use ADO but not as a full time programmer): null or empty cells in table fields can be a problem as you probably know. There are good articles via web on Nulls in Access. Sounds like the field [I-Remarks] has nulls except when "PT ORD" is present. Maybe the null is causing problem. Since ADO does NOT recognize Access's NZ function (which conversts nulls to "" or whatever you specify) ADO can use a line IF[field] IS NOT NULL, THEN. So, might insert this conditional logic line before you go through the RS2 loop and go through the loop only if IS NOT NULL is true. Shot in the dark. Jeff
PS - I may not be tracking with your problem since I really don't understand your reference to "above" and "below" the criteria. I just understand tables and their fields - not tracking with above or below criteria.

 

[snowmman99]

Thanks for the input.. I am trying to move forward. What I am trying to do is take an input table with duplicate numbers in a column, (which I need) and the column with "PT ORD" has nulls in the total row except for the price and remarks columns (which has PT ORD) and I can not associate the row with the columns with duplicate values.
It is imported this way and stays with the rows in the table as it is imported.
I have attached an excel sheet for review. The Highlighted rows have to be deleted. This file may be as large as 2000 rows.

 

[jjlogan]

I took a look at the excel and your statement that you are trying to delete the highlighted rows of this table. My question is 'What is your criteria (in words, not code) for deleting a row of this table?" This would help me go from criteria to code. Jeff

 

[snowmman99]

Delete the Docno's where there is a PT ORD EST. These are estimates and I do not need them.

 

[snowmman99]

This is the attachment with the corrected highlighted items that I am trying to delete.
Thanks

 

[jjlogan]

Have you considered using a SQL action query in the vba code rather than using ADO? As follows:
' Delete records using vba SQL

SQL = "DELETE * FROM table_name WHERE [REMARKS] Like '%PT ORD EST';"
DoCmd.RunSQL SQL

 

[jjlogan]

Oops. I used wrong wild card symbol - should be * instead of % for vba SQL.

 

[snowmman99]

Yes but that only deletes the line with "PT ORD EST" in it. I need the docno that are above it to be deleted.

 

[snowmman99]

i really do apprciate the help.. This is my last hurdle and can not figure it out. This file is going to be exported and used as an input file to automatically update prices on finacial records. Is there a way to look for NULL values and copy the cell above it. This way I can execute a duplicate value sql with PT ORD EST in the remarks value and delete it off?? Just thinking out load..

 

[jjlogan]

Ok, now I better understand your criteria and process. What I would consider doing the following (in words) as a first thought: make a pass through the table using ado and as you look at every row and if the Remark field is not PT ORD EST, then store the docno on that row in a variable (strDocNo). Go to next row. If Remark is PT ORD EST, then store Remark field into strRemark, then insert the current value in strDocno (previous row's) and strRemark into a temporary table (Before running the ado, set up this table with fields DocNumber, RemarkCopy/ Text, etc.). Then after your ado has come to end of recordset, exit the ado and now do the deletes. Can delete using a query you create outside of vba or could programmatically do the delete. Before deleting any records, open the temp table to look at its contents to make sure all records have PT ORD EST and a docno. Now to delete, create a query with temp table linked to main table on DocNo = DocNumber. Then change query to Delete type and should probably insert in Criteria line [DocNo] = [DocNumber]. Open this query to ensure getting all the records deleted, then click RUN.
Going back to above "insert this DocNo value into a temp table", you would insert code within the ado section like:
Dim SQL as string
SQL = "INSERT INTO temptable" & _
"( DocNumber, RemarkCopy )" & _
"VALUES (" & strDocNo & ", " & strRemark & ");" RunSQL SQL
There may be easier way, but I use this approach when having to analyze every row and can't just use a simple query. If you know what I mean, and I think you do.
Hope helps. Jeff

 

[snowmman99]

Thanks Jeff!! I will try this and get back to you..