Navigate a Query

[Mile-O]

Dim db As DAO.Database
Dim rsOld As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim intCounter As Integer

Set db = CurrentDb
Set rsOld = db.OpenRecordset("Table1")
Set rsNew = db.OpenRecordset("tblNew")

rsOld.MoveLast
rsOld.MoveFirst

For intCounter = 1 to rsOld.RecordCount

(navigate through rsNew)

Next intCounter

 

[Cosmos75]

' create database and recordset objects
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim CountRS As Integer
Dim Field1val As Variant

Set db = CurrentDb()

Set rs = db.OpenRecordset("Table1")
CountRS = rs.RecordCount

Set rsNew = db.OpenRecordset("tblNew")

MsgBox CountRS

rs.MoveFirst
For i = 1 To CountRS

    Field1val = rs.Fields("Field1").Value
    MsgBox "Field1 = " & Field1val

    rsNew.AddNew
    rsNew.Fields("Field1").Value = Field1val
    rs.MoveNext

Next i

' close the recordset object (connection to the query)
rs.Close

' clear the defined objects
Set rs = Nothing
Set db = Nothing

No records are being added to tblNew. What am I doing wrong?

 

[Mile-O]

You need to add an Update statement with each new record.

    rsNew.AddNew
    rsNew.Fields("Field1").Value = Field1val
    [b]rsNew.Update[/b]
    rs.MoveNext

 

[Cosmos75]

Mile-O-Phile,

Just popped back in here to say "THANK YOU"!!

 

[Cosmos75]

DAO - Value for Field in n-th Record?

Say I have this code

Dim myDB As  DAO.Database and DAO.REcordset
Dim rst  as DAO.REcordset
Set myDb = CurrentDb()
Set rst = myDB.OpenRecordset("tblRecords", dbOpenDynaset)

rst.MoveFirst

What do I need to do next to access the value of a field for the n-th (e.g. sixth) record?


Also, I am correct in saying that any sort order I apply to my records set will affect the order in which a record is accessed?

 

[Mile-O]

Dim myDB As DAO.Database
Dim rst  as DAO.Recordset
Dim intCounter As Integer
Set myDb = CurrentDb()
Set rst = myDB.OpenRecordset("tblRecords", dbOpenDynaset)
With rst
    .MoveLast
    .MoveFirst
    For intCounter = 1 To x
        ' code in here
    Next x
End With

 

[Cosmos75]

Mile-O-Phile,

Thanks!


Never thought of using a loop within my own loop.

With some searching I did find this;

[color=green]'This is to accomodate for the fact that 
'this is a Zero based call[/color]
rst.Move x - 1

I think (read as hope) this will work for my purposes.

 

[o1110010]

ADO cursors

Here's an ADO version of Mile's code:

' open the recordset
' note that in ado, the standard recordset is forward- and read-only
rst.Open "qryExample", cnn
' this would open the recordset as static with pessimistic locking:
'rst.Open "qryExample", cnn, adOpenStatic, adLockPessimistic

i didn't know off-hand what the difference between these two statements were so i looked into it. if anyone else reviews this thread and is wondering the same thing, i found a suitable answer at the following link.
http://www.adopenstatic.com/faq/jetcursortypes.asp