Insert into need help

[ozdemirozgur]

I have the following code which moves records from one table to another one based on a if condiction. However, my isnert statement does not work and when I run the code it is aksing for the value of the fields which is supposed to come from the do while selected record on the first table.

please help.

ozgur

Dim StrSQL As String
Set myDB = CurrentDb()
Set Rem10_2000_Combined = myDB.OpenRecordset("Rem10_2000_Combined", dbOpenTable)

Set Rem10_2000_Combined_Annualized = myDB.OpenRecordset("Rem10_2000_Combined_Annualized", dbOpenTable)

Do While Not Rem10_2000_Combined.EOF
If Rem10_2000_Combined!PERIOD_END = #12/31/1999# Or Rem10_2000_Combined!PERIOD_END = #12/31/2000# Or Rem10_2000_Combined!PERIOD_END = #12/31/2010# Then

DoCmd.RunSQL "INSERT INTO Rem10_2000_Combined_Annualized (Hems_year, Rem_Year, COY_ID) values (REM10_2000_COMBINED!Hems_Year , REM10_2000_COMBINED!Rem_Year, REM10_2000_COMBINED!COY_ID)"

End If

Rem10_2000_Combined.MoveNext
Loop

 

[stopher]

Why do you choose to do this using recordsets? It's much easier just to do it with a single query:

INSERT INTO Rem10_2000_Combined_Annualized ( Hems_Year, Rem_Year, COY_ID, PERIOD_END )
SELECT Rem10_2000_Combined.Hems_Year, Rem10_2000_Combined.Rem_Year, Rem10_2000_Combined.COY_ID, Rem10_2000_Combined.PERIOD_END
FROM Rem10_2000_Combined
WHERE Rem10_2000_Combined.PERIOD_END In (#12/31/1999#,#12/31/2000#,#12/31/2010#)

By the way, you have chosen years ending 1999, 2000 and 2010. Is that correct?

Chris

 

[ozdemirozgur]

thanks, I have to use recordsets because there will be some other code if the date is not year end. I will also need to chose the other years. Any idea why myu code is not working? any suggestions?

 

[stopher]

thanks, I have to use recordsets because there will be some other code if the date is not year end. I will also need to chose the other years.

So create a query to deal with each case. Much easier to manage/maintain.

But to answer your question...

You need to insert actual values into your SQL string, not the recordset variables. So your string needs to be something like this:

DoCmd.RunSQL "INSERT INTO Rem10_2000_Combined_Annualized (Hems_year, Rem_Year, COY_ID) values (" & REM10_2000_COMBINED!Hems_Year & "," & REM10_2000_COMBINED!Rem_Year & "," & REM10_2000_COMBINED!COY_ID & ")"

Notice how I've embedded the actual values in the sql string.

I've assume that the values you are inserting are all numbers.

hth
Chris