Insert into loop not working

  • Thread starter Thread starter Rhys Davies
  • Start date Start date
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
 
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
 
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
'====================================================
 
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
Click to expand...
Click to expand...
 
Back
Top