Record set looping

Question

Record set looping

Cobra17 (Mechanical)

(OP)

6 Oct 21 17:58

I'm trying to loop through a table records and fields. The field loops work fine, but I can't get it to move down to the next "tbl" record..

i'm using 3 record sets all based on the same table
tbl = the set I want to loop through
rst1 = loops through table field names and compares against rst2
rst2 = loops through table field names

CODE -->

tbl.MoveFirst
        Do While Not tbl.EOF
            If tbl!ID_Vessel = OldVesID And tbl!Est_ID = OldEst Then
                With rst1
                    .AddNew
                    !ID_Vessel = NewVesID
                    !Est_ID = NewEst
                    For Each fld1 In rst1.Fields
                        If fld1.Name <> "ID" And fld1.Name <> "ID_Vessel" And fld1.Name <> "Est_ID" Then
                            For Each fld2 In rst2.Fields
                                If fld1.Name = fld2.Name Then
                                    If IsFieldCalculated(tblName, fld2.Name) <> True Then
                                        fld1 = fld2
                                    End If
                                End If
                            Next fld2
                        End If
                    Next fld1
                    .Update
                    .Close
                End With
            End If
            tbl.MoveNext
        Loop

I was thinking that maybe an SQL statement would work with to copy and paste if ID_Vessel = OldVesID AND Est_ID - OldEst... but i'm not sure how i could replace only the copied ID_Vessel & Est_ID with NewVesID & NewEst. or maybe some other nice and easy way.

JG2828 (Electrical) · Answer 1 · 2021-10-12 18:51:56

Can you post the file with sample data? I'm not even sure if you are using access or excel here.

SkipVought (Aerospace) · Answer 2 · 2021-10-12 19:05:21

Hi,

Not sure why you're using 3 recordset representation on 1 table.

I've always used just 1.

So I need to understand what you're trying to do functionally.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

Cobra17 (Mechanical) · Answer 3 · 2021-10-13 21:02:26

here are the record set explanations.

tbl = i'm going through the table row by row looking for ID_Vessel and Est_ID

if the row meets the criteria then
rst1 = goes through the column header names one at a time and compares them to rst2
rst2 = goes through the column header names within rst1, and compares them to rst1

it rst1 fld1.name = rst2 fld2.name, then I copy rst1 fld1 value and put it into rst2's fld2 value, where fld = field

basically i'm trying to copy a record that has some fields that you can't copy.. like calculated fields, autonumber, things like that.. and have it dynamic so I feed it a table name and it doesn't care what type of field they are or how many fields.

Come Join Us!

Posting Guidelines

Record set looping