[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • Last Modified:

iterate through fields

How can I code the following iteration with these two tables:  table "tbla" has a list of city names in field "city" and table "tblb" has the following fields "cityname1","cityname2","cityname3".   I want to transpose the list of city names into table "tblb".
Set rs1 = CurrentDb.OpenRecordset("tbla")
Set rs2 = CurrentDb.OpenRecordset("tblb")
 
    Do While Not rs1.EOF()
        Counter = 0
        rs2.AddNew
        While Counter < 3
            Counter = Counter + 1
            fldPosition = "cityname" & Trim(Str(Counter))
            rs2![fldPosition] = rs1!city
            rs1.MoveNext
            If rs1.EOF Then
                rs2.Update
                Exit Do
            End If
        Wend
        rs2.Update
    Loop

Open in new window

0
SFWolverine
Asked:
SFWolverine
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this:
Set rs1 = CurrentDb.OpenRecordset("tbla")
Set rs2 = CurrentDb.OpenRecordset("tblb")
 
    Do While Not rs1.EOF()
        Counter = 0
        rs2.AddNew
        While Counter < 3 and not rs1.eof
            Counter = Counter + 1
            fldPosition = "cityname" & Trim(Str(Counter))
            rs2.fields(fldPosition).Value = rs1!city
            rs1.MoveNext
        Wend
        rs2.Update
    Loop

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now