loop through an insert into

[gwilym40]

I'm running the following code which works great, except for that if more than 1 record is found, it doesn't loop through on the inser into line, always inserts the 1st one
How can I get it to loop through the insert into as well?

thanks
ian

str2 = "SELECT * from qry_temp_weekly where [entrydate] between " & "#" & Format([entry_date], "mm/dd/yy&quot & "#" & " AND " & "#" & Format([end_entry_date], "mm/dd/yy&quot & "#"
Msgbox str2
Set rst2 = db1.OpenRecordset(str2)
str3 = "INSERT INTO tbltemp (RefNo,entrydate,Function) values ('" & rst2!RefNo & "' ,'" & rst2!entrydate & "','" & rst2!Function & "')"
Msgbox str3
db1.Execute (str3)
rst2.MoveLast
Msgbox rst2.RecordCount
rst2.MoveFirst
Do Until rst2.EOF
rst2.MoveNext
Loop
Msgbox rst2.RecordCount

 

[dalchri]

It looks like you are just trying to move data from qry_temp_weekly to tbltemp. I would replace all of that code with the following:
[tt]
db1.Execute _
"INSERT INTO tbltemp (RefNo, entrydate, Function) " & _
"SELECT RefNo, entrydate, Function " & _
"FROM qry_temp_weekly " & _
"WHERE entrydate Between #" & _
Format([entry_date], "mm/dd/yy&quot & _
"# And #" & _
Format([end_entry_date], "mm/dd/yy&quot & _
"#"
[/tt]