Access 2000 - Error #3426 on "AddNew" to RecordsetClone

G

Guest

OK, folks, can any of you shed some light on this -- it used to work in
Access 97. I have done a bunch of searching on Google - lots of hits with no
clear resolution to the problem (lots of different causes).

Access 2000 database (mdb).

A form has a query bound to it that is as follows:

SELECT DISTINCTROW [SW Modules].*
FROM [SW Modules]
WHERE ((([SW Modules].Reuse)<>"Deleted"))
OR ((([SW Modules].Reuse) Is Null))
ORDER BY [SW Modules].[Package Name], [SW Modules].[Module Name];

All the fields referenced in the query are valid in the named table. If I
copy the query and run it, it returns the expected results (987 records in
this case).

There is a button on the form to add a new record which runs the following
code segment:

Private Sub goto_new_Click()
Dim mydb As Database
' Dim newrecord As Object ' test - nope
Dim newxrecord As DAO.Recordset
Dim mdlRecord As Recordset
Dim NewPkg As String, NewMdl As String
Dim strMsg As String, sql1 As String
Dim findIndex As Integer

[stuff snipped]

Set newxrecord = Me.RecordsetClone 'get record set linked to form
' add a new record -- copy field info from form to record ...
Debug.Print "My check point"
' newrecord.MoveLast ' see if this changes it ..
newxrecord.AddNew ' <<<<-- this line breaks it with error #3426
newxrecord![Module Name] = NewMdl
newxrecord![Reuse] = "New"
newxrecord![RCS Generation] = "1"

I have looked around in the debugger window and found the following:
- both BOF and EOF are FALSE (some reports indicate if either is true, it is
a problem)
- the field count for the recordset is 23 which matches the table it is
derived from
- number of records is 987 which also matches the expected result
- Some reports indicate the implicit "update" for the "AddNew" can fail if
any of the data is too big for the fields -- all look good.
- I tried doing a .movenext and .movelast before the .addnew to see if that
would make a difference -- still fails.
- the original code was Form.RecordsetClone instead of Me.Recordsetclone
(which also fails)
- the original recordset declaration was "Dim NewRecord as Recordset" -- I
have tried some of the suggestions I found such as declaring it as an object
instead with no change in the results.
- I changed the name from "newrecord" to "newxrecord" since there is also a
newrecord function just to make sure there was not a problem there.
- In the debug window, newxrecord.type returns "2" which I believe is a
snapshot
- I created a test table with just a couple of entries in it just in case
some data was causing the problem -- no change.
- I have looked at the Knowledgebase article 189851 which says:

SYMPTOMS
This article discusses the error message "This action was cancelled by an
associated object." that Visual Basic generates when working with Access
databases and the Data control. This article does not apply to the ADO data
control.

CAUSE
This error is being generated because the AddNew command of a bound
recordset causes Visual Basic to try to save the current record if the data
has changed. Because the data control is currently pointing to a NULL record
and not an empty record, the data cannot be saved so the "action was
cancelled by an associated object" error is reported. This is commonly seen
when using the data form wizard in Visual Basic versions 4.0 and 5.0. The
data form wizard in Visual Basic 6.0 generates code for the ADO data control
so this error is less likely to occur.

RESOLUTION
Check the underlying recordset to see if either the BOF or EOF properties
are True before allowing an implicit save to occur. An implicit save occurs
either when using the data control to navigate off of a record where the
information has changed or adding a record to a bound recordset.

STATUS
This behavior is by design.

- I am running out of hair to pull out !! Anyone solved this one before??
AS near as I can tell, it does do the recordset clone, it just will not do an
AddNew.

Thanks !!!
 
N

Nikos Yannacopoulos

Kristie,

Long shot, but I'll shhot anyway... do you have a DAO 3.6 reference in
your project? I believe a DAO reference was defaulted in in A97, but
isn't in A2K.

HTH,
Nikos

Kristie said:
OK, folks, can any of you shed some light on this -- it used to work in
Access 97. I have done a bunch of searching on Google - lots of hits with no
clear resolution to the problem (lots of different causes).

Access 2000 database (mdb).

A form has a query bound to it that is as follows:

SELECT DISTINCTROW [SW Modules].*
FROM [SW Modules]
WHERE ((([SW Modules].Reuse)<>"Deleted"))
OR ((([SW Modules].Reuse) Is Null))
ORDER BY [SW Modules].[Package Name], [SW Modules].[Module Name];

All the fields referenced in the query are valid in the named table. If I
copy the query and run it, it returns the expected results (987 records in
this case).

There is a button on the form to add a new record which runs the following
code segment:

Private Sub goto_new_Click()
Dim mydb As Database
' Dim newrecord As Object ' test - nope
Dim newxrecord As DAO.Recordset
Dim mdlRecord As Recordset
Dim NewPkg As String, NewMdl As String
Dim strMsg As String, sql1 As String
Dim findIndex As Integer

[stuff snipped]

Set newxrecord = Me.RecordsetClone 'get record set linked to form
' add a new record -- copy field info from form to record ...
Debug.Print "My check point"
' newrecord.MoveLast ' see if this changes it ..
newxrecord.AddNew ' <<<<-- this line breaks it with error #3426
newxrecord![Module Name] = NewMdl
newxrecord![Reuse] = "New"
newxrecord![RCS Generation] = "1"

I have looked around in the debugger window and found the following:
- both BOF and EOF are FALSE (some reports indicate if either is true, it is
a problem)
- the field count for the recordset is 23 which matches the table it is
derived from
- number of records is 987 which also matches the expected result
- Some reports indicate the implicit "update" for the "AddNew" can fail if
any of the data is too big for the fields -- all look good.
- I tried doing a .movenext and .movelast before the .addnew to see if that
would make a difference -- still fails.
- the original code was Form.RecordsetClone instead of Me.Recordsetclone
(which also fails)
- the original recordset declaration was "Dim NewRecord as Recordset" -- I
have tried some of the suggestions I found such as declaring it as an object
instead with no change in the results.
- I changed the name from "newrecord" to "newxrecord" since there is also a
newrecord function just to make sure there was not a problem there.
- In the debug window, newxrecord.type returns "2" which I believe is a
snapshot
- I created a test table with just a couple of entries in it just in case
some data was causing the problem -- no change.
- I have looked at the Knowledgebase article 189851 which says:

SYMPTOMS
This article discusses the error message "This action was cancelled by an
associated object." that Visual Basic generates when working with Access
databases and the Data control. This article does not apply to the ADO data
control.

CAUSE
This error is being generated because the AddNew command of a bound
recordset causes Visual Basic to try to save the current record if the data
has changed. Because the data control is currently pointing to a NULL record
and not an empty record, the data cannot be saved so the "action was
cancelled by an associated object" error is reported. This is commonly seen
when using the data form wizard in Visual Basic versions 4.0 and 5.0. The
data form wizard in Visual Basic 6.0 generates code for the ADO data control
so this error is less likely to occur.

RESOLUTION
Check the underlying recordset to see if either the BOF or EOF properties
are True before allowing an implicit save to occur. An implicit save occurs
either when using the data control to navigate off of a record where the
information has changed or adding a record to a bound recordset.

STATUS
This behavior is by design.

- I am running out of hair to pull out !! Anyone solved this one before??
AS near as I can tell, it does do the recordset clone, it just will not do an
AddNew.

Thanks !!!
 
G

Guest

Hi, Kristie.

You'll get the "Error #3426: This action was cancelled by an associated
object" message on the "AddNew" record operation when something is preventing
the addition of a new record to the record set. If you are going to add a
new record, you need an updatable record set, which a "Snapshot" is not.
Your query produces an updatable record set, so you need to look at the
form's properties to ensure that nothing is interfering. Open the form's
"Properties" sheet and look at the "Data" tab. Ensure that the "Record
Source" specifically names the correct query. "AllowAdditions" should be
marked "Yes" and "Recordset Type" should be marked "Dynaset" to allow you to
add new records to a query based upon a single table, as in your case. You
haven't shown us all of your code, but if these form properties are already
set as I've described, check to ensure that your code isn't altering any of
these three properties unintentionally.

If everything is as it should be on your form, then check the query's
properties. If the "Record Locks" property is set to "All Records," then
make sure that no one else (and no other process) has this query open at the
same time that you are attempting to add a new record. (I would suggest
changing the "Record Locks" property to "Edited Record" or "No Locks" if at
all possible.)

In comparing the "Form.RecordsetClone" vs. "Me.RecordsetClone" in your
original code, remember that the "Me" property always refers to the form or
report in which the code is running, so in your case, these two versions are
interchangeable.

And this next comment is going to spark a debate, but here goes: The
"newxrecord.Type" property that you describe is not referring to the
RecordsetType of the form (in which a value of "2" would indicate that the
record set is a Snapshot). The "newxrecord.Type" property is referring to
the RecordsetType of the DAO RecordsetClone (which is a clone of the form's
Recordset), which has the value of the constant "dbOpenDynaset," or "2."
(Check the Object Browser for the DAO library for "RecordsetTypeEnum" to see
where this value is coming from.)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

- - -
When you see correct answers to your question, please sign in to Microsoft's
Online Community and mark these posts, so that all may benefit by filtering
OK, folks, can any of you shed some light on this -- it used to work in
Access 97. I have done a bunch of searching on Google - lots of hits with no
clear resolution to the problem (lots of different causes).

Access 2000 database (mdb).

A form has a query bound to it that is as follows:

SELECT DISTINCTROW [SW Modules].*
FROM [SW Modules]
WHERE ((([SW Modules].Reuse)<>"Deleted"))
OR ((([SW Modules].Reuse) Is Null))
ORDER BY [SW Modules].[Package Name], [SW Modules].[Module Name];

All the fields referenced in the query are valid in the named table. If I
copy the query and run it, it returns the expected results (987 records in
this case).

There is a button on the form to add a new record which runs the following
code segment:

Private Sub goto_new_Click()
Dim mydb As Database
' Dim newrecord As Object ' test - nope
Dim newxrecord As DAO.Recordset
Dim mdlRecord As Recordset
Dim NewPkg As String, NewMdl As String
Dim strMsg As String, sql1 As String
Dim findIndex As Integer

[stuff snipped]

Set newxrecord = Me.RecordsetClone 'get record set linked to form
' add a new record -- copy field info from form to record ...
Debug.Print "My check point"
' newrecord.MoveLast ' see if this changes it ..
newxrecord.AddNew ' <<<<-- this line breaks it with error #3426
newxrecord![Module Name] = NewMdl
newxrecord![Reuse] = "New"
newxrecord![RCS Generation] = "1"

I have looked around in the debugger window and found the following:
- both BOF and EOF are FALSE (some reports indicate if either is true, it is
a problem)
- the field count for the recordset is 23 which matches the table it is
derived from
- number of records is 987 which also matches the expected result
- Some reports indicate the implicit "update" for the "AddNew" can fail if
any of the data is too big for the fields -- all look good.
- I tried doing a .movenext and .movelast before the .addnew to see if that
would make a difference -- still fails.
- the original code was Form.RecordsetClone instead of Me.Recordsetclone
(which also fails)
- the original recordset declaration was "Dim NewRecord as Recordset" -- I
have tried some of the suggestions I found such as declaring it as an object
instead with no change in the results.
- I changed the name from "newrecord" to "newxrecord" since there is also a
newrecord function just to make sure there was not a problem there.
- In the debug window, newxrecord.type returns "2" which I believe is a
snapshot
- I created a test table with just a couple of entries in it just in case
some data was causing the problem -- no change.
- I have looked at the Knowledgebase article 189851 which says:

SYMPTOMS
This article discusses the error message "This action was cancelled by an
associated object." that Visual Basic generates when working with Access
databases and the Data control. This article does not apply to the ADO data
control.

CAUSE
This error is being generated because the AddNew command of a bound
recordset causes Visual Basic to try to save the current record if the data
has changed. Because the data control is currently pointing to a NULL record
and not an empty record, the data cannot be saved so the "action was
cancelled by an associated object" error is reported. This is commonly seen
when using the data form wizard in Visual Basic versions 4.0 and 5.0. The
data form wizard in Visual Basic 6.0 generates code for the ADO data control
so this error is less likely to occur.

RESOLUTION
Check the underlying recordset to see if either the BOF or EOF properties
are True before allowing an implicit save to occur. An implicit save occurs
either when using the data control to navigate off of a record where the
information has changed or adding a record to a bound recordset.

STATUS
This behavior is by design.

- I am running out of hair to pull out !! Anyone solved this one before??
AS near as I can tell, it does do the recordset clone, it just will not do an
AddNew.

Thanks !!!
 

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