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 !!!
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 !!!