I have the following code:

Public Sub DoSomething()

    On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Integer

    'Define using current database, and open table
    Set db = CurrentDb
    Set rst = db.OpenRecordset("yourtablename", dbOpenSnapshot)

    'Make sure table has records, and set to first record.
    With rst
        If .RecordCount > 0 Then
            .MoveLast
            .MoveFirst
        End If
    End With

    'Loop through each record
    With rst
        For i = 1 To rst.RecordCount ' 1 to Count of records
            If .Field5 = "xxx" Then
                'do whatever
            End If
            'Move to the next record
            .MoveNext
        Next
    End With

ExitHere:
    'Clear variables used from memory
    Set rst = Nothing
    Set db = Nothing
    i = 0
    Exit Sub

ErrHandler:
    'Always include error trapping. this is the bare minimum...
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHere

End Sub

i need to find a way to do the following:

1. Set two fields of the table as variables
2. copy rows of the table based on the variables
3. delete the rows after they are copied until the table is empty.

I am new here so am trying learn

Thank you in advance.

Can you explain more of what you want? I think you can do this with some queries. Otherwise you may end up nesting.

Another option is to do the copying first, save the PK in an array, and then do your deletes by looping back through the array.

So what does: "set two fields of the table as variables" mean, for instance?



mmcdonal