Insert into loop not working


R

Rhys Davies

Hi, im trying to loop through a recordset and insert the contactID and
current date/time into a table. The query has 150 rows for example, after i
run this code the table has the 150 rows in there with the correct date/time
but the contactID is the same for each row in the table, namely the opening
record in the query, so it seems to loop through the records in a way but
doesnt move to the next record before running the INSERT code.
Can anyone point out where im going wrong?
Thanks.

Dim rsdbase As Database
Dim rstemp As Recordset
Set rsdbase = CurrentDb
Dim strSQL As String
Set rstemp = rsdbase.OpenRecordset("SELECT [contactID] FROM
[qrycompanycontacts]")
DoCmd.SetWarnings False
Do Until rstemp.EOF
strSQL = "INSERT INTO tblmailinghistory([userID],[date])" & _
"SELECT" & "'" & contactID & "'," & _
"'" & Now & "'"
DoCmd.RunSQL strSQL
rstemp.MoveNext
Loop
DoCmd.SetWarnings True
End Sub
 
Ad

Advertisements

S

Steve Sanford

This is UNTESTED!!!

You need a reference set to Microsoft DAO 3.6 Object Library (for A2K3)

NOTE: "Date" is a reserved word in Access (and SQL) and shouldn't be used as
an object name. Plus it is not very descriptive. "Date" of what???

'----------------------------------
Dim rsdbase As DAO.Database
Dim rstemp As DAO.Recordset
Dim strSQL As String

Set rsdbase = CurrentDb

strSQL = "SELECT [contactID] FROM [qrycompanycontacts]"
Set rstemp = rsdbase.OpenRecordset(strSQL )

If rstemp.BOF and rstemp.EOF then
msgbox "No contact ID records!!! Aborting"
rstemp.Close
set rstemp = nothing
Exit Sub
end if

Do Until rstemp.EOF
strSQL = "INSERT INTO tblmailinghistory([userID],[date])" & _
"VALUES" & "'" & rstemp.Fields("contactID") & "', " & _
"#" & Now & "#"

rsdbase.execute strSQL, dbfailonerror
rstemp.MoveNext
Loop

rstemp.Close
set rstemp = nothing


End Sub


HTH
 
J

John Spencer

Why not just use one query and no loop?

StrSQL = "INSERT INTO tblMailingHistory (UserId,[Date])" & _
" SELECT [ContactID], Now() FROM qryCompanyContacts"

That should be a lot more efficient.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Ad

Advertisements

R

Rhys Davies

thanks John, that did the job!

John Spencer said:
Why not just use one query and no loop?

StrSQL = "INSERT INTO tblMailingHistory (UserId,[Date])" & _
" SELECT [ContactID], Now() FROM qryCompanyContacts"

That should be a lot more efficient.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Rhys said:
Hi, im trying to loop through a recordset and insert the contactID and
current date/time into a table. The query has 150 rows for example, after i
run this code the table has the 150 rows in there with the correct date/time
but the contactID is the same for each row in the table, namely the opening
record in the query, so it seems to loop through the records in a way but
doesnt move to the next record before running the INSERT code.
Can anyone point out where im going wrong?
Thanks.

Dim rsdbase As Database
Dim rstemp As Recordset
Set rsdbase = CurrentDb
Dim strSQL As String
Set rstemp = rsdbase.OpenRecordset("SELECT [contactID] FROM
[qrycompanycontacts]")
DoCmd.SetWarnings False
Do Until rstemp.EOF
strSQL = "INSERT INTO tblmailinghistory([userID],[date])" & _
"SELECT" & "'" & contactID & "'," & _
"'" & Now & "'"
DoCmd.RunSQL strSQL
rstemp.MoveNext
Loop
DoCmd.SetWarnings True
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top