"FOR" loop in Access.

[pikkhuanloy]

Hi guys , as title, I would like to ask you guys whether there is "FOR" loop in Access VBA codes.

It's because currently I have been writing a code to build a loop. and the code is something like :

Dim rs As Recordset
Dim strTableFields As String
Dim strOutputTableFields As String
Dim strSQL As String
 
for i = 1 to n where n = number of fields in table(Mapping)
    'basically i want to do here is for i=1,Table=Table1 then for i=2, Table=Table2.is it possible? 
    Table= "Table"&i 
     Set rs = CurrentDatabase.OpenRecordset("Mapping")
    Do While Not rs.EOF
      strTableFields = strTableFields & rs!Table & ","
      strTable2Fields = strTable2Fields & rs!OutputTable & ","
    'so that that Table here is Table1 rather than a string "Table".
rs.MoveNext
    Loop
    rs.Close
 
    'Remove the last comma.
    strTable1Fields = Left(strTable1Fields, Len(strTable1Fields) - 1)
    strTable2Fields = Left(strTable2Fields, Len(strTable2Fields) - 1)
 
    strSQL = "INSERT INTO OutputTable (" & strOutputTableFields & ") SELECT " & strTableFields & " FROM Table " & i
    db.Execute strSQL
 
    Set rs = Nothing
    Set db = Nothing
end for

i got the idea but i dont know how to write the code. Please help me, guys!

Hope to hear from you soon. Thanks

 

[namliam]

Perhaps this will help?
http://stackoverflow.com/questions/3343922/get-column-names

It is atleast the first hit of some popular search engine....

 

[CJ_London]

it is very straightforward

for i = 1 to n
    ...do something
next

 

[pikkhuanloy]

Perhaps this will help?
http://stackoverflow.com/questions/3343922/get-column-names

It is atleast the first hit of some popular search engine....

thanks, i wrote a for loop ,

Sub TransferData()
Dim db As Database
Dim rs As Recordset
Dim strInputFields As String
Dim strOutputFields As String
Dim TableInput As String
Dim strSQL As String
 
For i = 1 To 2
    TableInput = "INPUT" & i
    i = i + 1
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Mapping")
    Do While Not rs.EOF
      strOutputFields = strOutputFields & rs.Fields(1) & ","
      strInputFields = strInputFields & rs.Fields(i) & ","
      rs.MoveNext
    Loop
 
    'Remove the last comma.
    strInputFields = Left(strInputFields, Len(strInputFields) - 1)
    strOutputFields = Left(strOutputFields, Len(strOutputFields) - 1)
 
    strSQL = "INSERT INTO OUTPUT (" & strOutputFields & ") SELECT " & strInputFields & " FROM " & TableInput
    MsgBox strSQL
Next i
End Sub

why the msgbox only pops out "INSERT INTO OUTPUT (" & strOutputFields & ") SELECT " & strInputFields & " FROM INPUT1" but no "INSERT INTO OUTPUT (" & strOutputFields & ") SELECT " & strInputFields & " FROM INPUT2"

May you help me to find out i could loop it?

 

[pikkhuanloy]

it is very straightforward

for i = 1 to n
    ...do something
next

Hi thanks for your reply, "do something" is a VBA command?

 

[KenHigg]

The for/next loop you have increments by itself, you don't need the i = i + 1. By the time you get to the bottom i already equals 2...

Why not try to get the for next loop to work with out all of your other code to get a better understanding of how it works - ?

 

[pikkhuanloy]

The for/next loop you have increments by itself, you don't need the i = i + 1. By the time you get to the bottom i already equals 2...

Why not try to get the for next loop to work with out all of your other code to get a better understanding of how it works - ?

Oic, so since the i has become value 2, the loop stops?