Deleting records in recset (record set)

[GT_engineer]

I have a record set of drawings (DWGNo) and their revisions (REVNo). I want to create a short list that only contains the latest revisions. I tried "recset.Delete" but I believe that deletes the whole record set. Is there a way to delete the current record. Also, does that move lower records up to the next slot?? I need to make sure my MoveNext and Move Previous are correct.

        Drawing=""
        Rev="" 
        While recset.EOF = False
            'Look for duplicates and remove lower revisions
            If recset!DWGNo = Drawing Then
                If recset!REVNo > Rev Then
                    recset.MovePrevious
                    recset.Delete
                    recset.MoveNext
                Else
                    recset.Delete
                End If
            Else
                Drawing = recset!DWGNo
                Rev = recset!REVNo
                recset.MoveNext
            End If
        Wend

 

[Macropheliac]

Try a DELETE statement instead...

Dim dSQL as string
 
dSQL="DELETE * FROM yourtable WHERE Revno >'" & Rev & "';"
Currentdb.Execute dSQL

 

[GT_engineer]

That may work with some fixing,

you see every drawing has its own set of revisions, Drawing 1 may have revisions 0,A,and B where as Drawing 2 may only have revision 0.

So the value of Rev will change for everydrawing and I need to make sure I have the highest (latest) version.

 

[Macropheliac]

Can you post more of your code, especially how you're determining the value for Rev?

 

[GT_engineer]

I got it working!!!!
I needed to add an extra recset.MoveNext. Below is the finsihed code. Rev and Drawing start off as "", then as i step through the recordset I update the values for them so they are always the latest and greatest. I should note that the recordset is ordered by DWGNo (Drawing Number). So I compare the current records value with the previous records value and because it is sorted by drawing number it will only compare like drawings.

        Drawing = ""
        Rev = ""
        While recset.EOF = False
        'Look for duplicates and remove lower revisions
            If recset!DWGNo = Drawing Then
                If recset!REVNo > Rev Then
                    Rev = recset!REVNo
                    recset.MovePrevious
                    recset.Delete
                    recset.MoveNext
                    recset.MoveNext
                Else
                    recset.Delete
                    recset.MoveNext
                End If
            Else
                Drawing = recset!DWGNo
                Rev = recset!REVNo
                recset.MoveNext
            End If
        Wend

 

[Macropheliac]

Good news. I'm glad you got it working.

 

[GT_engineer]

LOL MAJOR FAIL!!!!!:banghead:

So the script I wrote works, except it actually deletes the records from my source table. Is there a way to only delete items from a query and not to remove them from the database???

 

[boblarson]

LOL MAJOR FAIL!!!!!:banghead:

So the script I wrote works, except it actually deletes the records from my source table. Is there a way to only delete items from a query and not to remove them from the database???

I guess we need to share some info with you. Records don't exist independently in a query. A query shows you the actual records in the table. So, to exclude certain records you can put their id's into a table and have the query joined to that.

See here for how to exclude data using a table:
http://www.btabdevelopment.com/ts/tbl2exclude

 

[GT_engineer]

Hi Boblarson, thanks for that tip, unfortunately I can't use it as of now since I don't know which drawings to exclude. The script I created goes through my table and finds the latest drawing revisions and then removes (deletes) drawings I don't need.

But saying that, I can change my script to just find the latest drawings and don't remove/delete anything then create a new query to search for drawings minus the ones I just found.

I was hoping not to go through this route becuase I have already done two queries plus my while loop. Adding another query may slow things down.

Using VB whats the best way to create a new table with the drawings I don't want? Thanks

 

[GT_engineer]

Hi Boblarson, I'm thinking of using a SQL string

SQLstring = "INSERT INTO Exclude_Table (Drawing_ID) VALUES (" & Number & ")"
DoCmd.RunSQL SQLstring

before doing this I need to make sure the exlcusion table is clear of all records.

Is there an SQL code for doing this???

thanks

 

[boblarson]

Hi Boblarson, I'm thinking of using a SQL string

SQLstring = "INSERT INTO Exclude_Table (Drawing_ID) VALUES (" & Number & ")"
DoCmd.RunSQL SQLstring

before doing this I need to make sure the exlcusion table is clear of all records.

Is there an SQL code for doing this???

thanks

Use CurrentDb.Execute instead of DoCmd.RunSQL but your code would be changed to:

SQLstring = "DELETE * FROM Exclude_Table"
CurrentDb.Execute SQLstring, dbFailOnError
SQLstring = "INSERT INTO Exclude_Table (Drawing_ID) VALUES (" & Number & ")"
CurrentDb.Execute SQLstring, dbFailOnError

 

[GT_engineer]

Hi Boblarson,

your code and method worked well, my query shows just the latest drawings using the Null exclusion trick. Now I want to pass this info off to a printable form.

whereTxt = " View_Outgoing_Transmit_pt2.Job_ID=" & jobNumTxt & " AND View_Outgoing_Transmit_pt2.Partner_ID=" & custNumTxt & " AND Exclude_Table.TransmitItem_ID Is Null ORDER BY DWGNo;"
 
DoCmd.OpenReport "Report_Outgoing_Transmit", acViewPreview, , whereTxt

now when I type this SQL text into Access manually in the report, it works fine. However when I try to pass this using VBA it does not like the "Exclude_Table.TransmitItem_ID Is Null ORDER BY DWGNo;"

Any ideas why???

-Answered my own qeustion, does not need the ORDER BY



thanks Boblarson for your help