By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

replace Rs.AddNew with INSERT

P: n/a
I replaced AddNew statments with INSERT INTO, but am unable to
write to the same tables. Foreign keys violations are
the main errors. Why are these occuring ?

Before:

set rs3= db.OpenRecordset("tblMat", dbOpenDynaset)
rs3.AddNew
rs3!Mat = Rs!Mat
rs3.Update

After:

set rs3= db.OpenRecordset("tblMat", dbOpenDynaset)
rs3.AddNew
rs3!Mat = Rs!Mat
sql_str = "insert into tblMat(Sample_Mat) values (" & rs3!Mat & ")"
conn.Execute sql_str

Thanks,
Phil Miller

Jan 5 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Just guessing here,

The first item appears to be DAO. The second one, judging by the conn
object, may be ADO. ADO doesn't require the Update statement. According to
the help file, it will update for you if you call AddNew again or call Edit.
I suspect it may be doing the Update for you whenever you move to a
different record. So, when you execute the query, you are once again moving
to a new record and are adding the same value that was already added with
the AddNew and the rs3!Mat = Rs!Mat lines, hence the duplicate value.

If the Update statement is required, then I would expect an error along the
lines of "AddNew without Update".

If the second recordset is DAO (and by the syntax of the OpenRecordset
statement it appears to be) then I would definitely expect the "AddNew
without Update" error. If you have the Update statement after what you have
posted here, then I would suspect the duplicate value as mentioned above.
The timing on this may adjust the error you get, depending on when the data
gets written to the table from the memory buffer. Since you are writing the
data two different ways, they my not complete the write to the table in the
order they're written.

--
Wayne Morgan
MS Access MVP
"pillmill" <pm*****@gnf.org> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
I replaced AddNew statments with INSERT INTO, but am unable to
write to the same tables. Foreign keys violations are
the main errors. Why are these occuring ?

Before:

set rs3= db.OpenRecordset("tblMat", dbOpenDynaset)
rs3.AddNew
rs3!Mat = Rs!Mat
rs3.Update

After:

set rs3= db.OpenRecordset("tblMat", dbOpenDynaset)
rs3.AddNew
rs3!Mat = Rs!Mat
sql_str = "insert into tblMat(Sample_Mat) values (" & rs3!Mat & ")"
conn.Execute sql_str

Thanks,
Phil Miller

Jan 6 '06 #2

P: n/a
rkc
pillmill wrote:
After:

set rs3= db.OpenRecordset("tblMat", dbOpenDynaset)
rs3.AddNew
rs3!Mat = Rs!Mat
sql_str = "insert into tblMat(Sample_Mat) values (" & rs3!Mat & ")"
conn.Execute sql_str


You don't need .Addnew to add a new record using sql.
You don't need to open a recordset at all actually.
But your problem probably has to do with the value of rs3!mat.
Jan 6 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.