Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 23rd, 2007, 03:42 AM
Authorized User
Join Date: Jan 2007
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with Recordsets

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
        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
    End With

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

    '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.
Old January 23rd, 2007, 08:36 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

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?


Similar Threads
Thread Thread Starter Forum Replies Last Post
Recordsets JezLisle Access VBA 11 July 17th, 2007 03:47 AM
Assigning Recordsets mrjeret BOOK: Access 2003 VBA Programmer's Reference 0 July 6th, 2006 09:39 AM
Cloned Recordsets taraj Access VBA 4 June 13th, 2006 08:28 AM
Need help with recordsets chacquard Access VBA 5 June 21st, 2004 11:58 PM
Recordsets bph Access VBA 17 February 17th, 2004 03:19 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.