Adding Records to Recordsets

[ddsnydersnyder]

I have two questions. the two questions both pertain to the below Code at the bottom of this post

1) I receive an error 3219 and 'invalid operation' error on the code "temp.AddNew". This worked in 2003, but now fails now that I run in 2007

2) I need some help on the code. The below code should add the record, update the record to the analyst value. For some reson, though, it doesn't actually update the record until after it sends the email. Any ideas? thanks

Code snippet for question #2
temp.AddNew
temp.Fields("analyst") = analyst
temp.Update


code Starts Here
Dim dbMyDB As Database
Dim rs As DAO.Recordset
Dim temp As DAO.Recordset
Set dbMyDB = OpenDatabase("path to database - I can't post links")
Set rs = dbMyDB.OpenRecordset("SELECT * FROM [PT04 - Unique Analyst to Send Changes]")
Set temp = dbMyDB.OpenRecordset("SELECT [Analyst] FROM [PT - Active Analyst]")
Dim analyst As String

'Loops through Analysts, appending to Temp Table and sending email
If rs.RecordCount <> 0 Then

rs.MoveFirst
Do Until rs.EOF
DoCmd.OpenQuery "PT08 - Delete Active Analyst Table"
analyst = rs![Inventory Analyst ID]

'Appends Analyst to Temp Table
temp.AddNew
temp.Fields("analyst") = analyst
temp.Update

'Sends Email
Dim email As String
Dim email_Subject As String
Dim email_text As String
Dim email_cc As String
email = rs![Inventory Analyst Email]
email_cc = rs![Inventory Manager Email]
email_Subject = "Changed Import SKU Information"
email_text = "The attached report show Import items with changed inner packs or rounding profiles"

DoCmd.SendObject acQuery, "PT06 - Changed Import SKU Information", "MicrosoftExcelBiff8(*.xls)", email, email_cc, , email_Subject, email_text, True, ""
DoCmd.OpenQuery "PT09 - Mark SKUs Sent"
rs.MoveNext
Loop
DoCmd.OpenQuery "PT10 - Delete PT Change Table"
MsgBox "SKUs extracted for Inventory Management"

 

[boblarson]

Given that the error number is "Not allowed to do this in this context" then I'm guessing that possibly your recordset is non-updatable. So, is this a query:
[PT - Active Analyst] or is it a table?

 

[ddsnydersnyder]

It's a table. Found the issue with #1. During the upgrade to 2007, I also migrated the table from local to Oracle Server. i wonder why it won't let me now...

Thanks for your help. Can anyone easily tell why I am having issues with problem #2?

 

[DCrake]

try

temp("analyst") = analyst

 

[ddsnydersnyder]

Thanks Dcrake!

That didn't quite work but got me started in the right direction. The change that fixed it was:

temp("analyst").Value = analyst

 

[boblarson]

Thanks Dcrake!

That didn't quite work but got me started in the right direction. The change that fixed it was:

temp("analyst").Value = analyst

You could have also used:

temp!analyst = analyst