At the end of my rope: can't change first record and move away

[kaioptera]

I have a form in an ADP connected to a SQL Server 2005 backend, with a
record source that is a view that includes two joins but is still
updateable.

The form has a list box control in the header section that is supposed
to allow the user to select a record to show in the detail section, set
up by the list box wizard, which created the following code to
implement this functionality:

Private Sub List52_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.recordSet.Clone
rs.Find "[cp_trust] = '" & Me![List52] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

For all records except one, I can change the record and then select a
different record in the list box, and have the record I changed be
updated as I move away to a different record.

BUT if the *first record* in the list box is selected, and I change a
value in that record, when I move to a different record, I get
"Run-time error 2001: You canceled the previous operation.", on the
line that assigns Me.Bookmark. I know from SQL Profiler that the record
has already been updated before this error pops up. If I click "End" to
get rid of the error window and select a different record, the error
pops up again, unless (and this is weird) there is another record in
the list box with the same value of cp_trust as the first record, in
which case the error doesn't come up, but the current record doesn't
actually change, either. If I change the rs.Find criterion from
cp_trust to a unique ID, we still get the error (2001), but the
behavior of having a record we can move to without throwing an error
after having changed the first record disappears.

If after getting one of these errors I move back to the first record
and press Escape, the little pencil icon that indicates a record has
changed goes away, the change is NOT rolled back in the database or on
the form, and I can now switch records without any errors.

If I add a line to call Me.Recordset.Update at the beginning of the
AfterUpdate sub, the new line causes the update *only in the case of
the first record having changed*, the line has no effect for changes to
other records, in which case the update goes through when the line that
assigns Me.Bookmark is executed. (I know this from stepping through the
code while watching SQL Profiler)

As a final control, if I make a form that doesn't have the list box
control, I can modify the first record and move to a different record
without causing the error.

I've gathered all this behavioral information in the hopes of finding a
workaround, to no avail.

So I ask you: what ON EARTH is going on here?

 

[Sylvain Lafontaine]

First, it's not clear from your explanation if this combobox is a bound or
unbound control to a field.

Second, I don't know the difference between Me.RecordSet.Clone and
Me.RecordsetClone but you can give it a try.

Third, you can also try testing the IsDirty property and save any pending
change using DoCmd.RunCommand acCmdSaveRecord instead of
Me.Recordset.Update.

Finally, I'm not sure if your find method will find anything if you are
located past the searching value, so you should repeat the search from the
beginning of the list if the next record is not found.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have a form in an ADP connected to a SQL Server 2005 backend, with a
record source that is a view that includes two joins but is still
updateable.

The form has a list box control in the header section that is supposed
to allow the user to select a record to show in the detail section, set
up by the list box wizard, which created the following code to
implement this functionality:

Private Sub List52_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.recordSet.Clone
rs.Find "[cp_trust] = '" & Me![List52] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

For all records except one, I can change the record and then select a
different record in the list box, and have the record I changed be
updated as I move away to a different record.

BUT if the *first record* in the list box is selected, and I change a
value in that record, when I move to a different record, I get
"Run-time error 2001: You canceled the previous operation.", on the
line that assigns Me.Bookmark. I know from SQL Profiler that the record
has already been updated before this error pops up. If I click "End" to
get rid of the error window and select a different record, the error
pops up again, unless (and this is weird) there is another record in
the list box with the same value of cp_trust as the first record, in
which case the error doesn't come up, but the current record doesn't
actually change, either. If I change the rs.Find criterion from
cp_trust to a unique ID, we still get the error (2001), but the
behavior of having a record we can move to without throwing an error
after having changed the first record disappears.

If after getting one of these errors I move back to the first record
and press Escape, the little pencil icon that indicates a record has
changed goes away, the change is NOT rolled back in the database or on
the form, and I can now switch records without any errors.

If I add a line to call Me.Recordset.Update at the beginning of the
AfterUpdate sub, the new line causes the update *only in the case of
the first record having changed*, the line has no effect for changes to
other records, in which case the update goes through when the line that
assigns Me.Bookmark is executed. (I know this from stepping through the
code while watching SQL Profiler)

As a final control, if I make a form that doesn't have the list box
control, I can modify the first record and move to a different record
without causing the error.

I've gathered all this behavioral information in the hopes of finding a
workaround, to no avail.

So I ask you: what ON EARTH is going on here?

 

[Sylvain Lafontaine]

I forgot to mention that there have been many reports in the past about bugs
associated with RecordsetClone operations and the 2000 and 2002 versions of
Access ADP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

First, it's not clear from your explanation if this combobox is a bound or
unbound control to a field.

Second, I don't know the difference between Me.RecordSet.Clone and
Me.RecordsetClone but you can give it a try.

Third, you can also try testing the IsDirty property and save any pending
change using DoCmd.RunCommand acCmdSaveRecord instead of
Me.Recordset.Update.

Finally, I'm not sure if your find method will find anything if you are
located past the searching value, so you should repeat the search from the
beginning of the list if the next record is not found.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have a form in an ADP connected to a SQL Server 2005 backend, with a
record source that is a view that includes two joins but is still
updateable.

The form has a list box control in the header section that is supposed
to allow the user to select a record to show in the detail section, set
up by the list box wizard, which created the following code to
implement this functionality:

Private Sub List52_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.recordSet.Clone
rs.Find "[cp_trust] = '" & Me![List52] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

For all records except one, I can change the record and then select a
different record in the list box, and have the record I changed be
updated as I move away to a different record.

BUT if the *first record* in the list box is selected, and I change a
value in that record, when I move to a different record, I get
"Run-time error 2001: You canceled the previous operation.", on the
line that assigns Me.Bookmark. I know from SQL Profiler that the record
has already been updated before this error pops up. If I click "End" to
get rid of the error window and select a different record, the error
pops up again, unless (and this is weird) there is another record in
the list box with the same value of cp_trust as the first record, in
which case the error doesn't come up, but the current record doesn't
actually change, either. If I change the rs.Find criterion from
cp_trust to a unique ID, we still get the error (2001), but the
behavior of having a record we can move to without throwing an error
after having changed the first record disappears.

If after getting one of these errors I move back to the first record
and press Escape, the little pencil icon that indicates a record has
changed goes away, the change is NOT rolled back in the database or on
the form, and I can now switch records without any errors.

If I add a line to call Me.Recordset.Update at the beginning of the
AfterUpdate sub, the new line causes the update *only in the case of
the first record having changed*, the line has no effect for changes to
other records, in which case the update goes through when the line that
assigns Me.Bookmark is executed. (I know this from stepping through the
code while watching SQL Profiler)

As a final control, if I make a form that doesn't have the list box
control, I can modify the first record and move to a different record
without causing the error.

I've gathered all this behavioral information in the hopes of finding a
workaround, to no avail.

So I ask you: what ON EARTH is going on here?

 

[kaioptera]

The control is unbound, and we tried RecordSet.Clone and RecordsetClone
without success. The dirty flag seems to be reliable in all cases, so
we might try acCmdSaveRecord. We're basically just working around it by
writing all the queries ourselves, which sucks, but that's access for
you. Thanks very much for your help.

I forgot to mention that there have been many reports in the past about bugs
associated with RecordsetClone operations and the 2000 and 2002 versions of
Access ADP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

First, it's not clear from your explanation if this combobox is a bound or
unbound control to a field.

Second, I don't know the difference between Me.RecordSet.Clone and
Me.RecordsetClone but you can give it a try.

Third, you can also try testing the IsDirty property and save any pending
change using DoCmd.RunCommand acCmdSaveRecord instead of
Me.Recordset.Update.

Finally, I'm not sure if your find method will find anything if you are
located past the searching value, so you should repeat the search from the
beginning of the list if the next record is not found.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I have a form in an ADP connected to a SQL Server 2005 backend, with a
record source that is a view that includes two joins but is still
updateable.

The form has a list box control in the header section that is supposed
to allow the user to select a record to show in the detail section, set
up by the list box wizard, which created the following code to
implement this functionality:

Private Sub List52_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.recordSet.Clone
rs.Find "[cp_trust] = '" & Me![List52] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

For all records except one, I can change the record and then select a
different record in the list box, and have the record I changed be
updated as I move away to a different record.

BUT if the *first record* in the list box is selected, and I change a
value in that record, when I move to a different record, I get
"Run-time error 2001: You canceled the previous operation.", on the
line that assigns Me.Bookmark. I know from SQL Profiler that the record
has already been updated before this error pops up. If I click "End" to
get rid of the error window and select a different record, the error
pops up again, unless (and this is weird) there is another record in
the list box with the same value of cp_trust as the first record, in
which case the error doesn't come up, but the current record doesn't
actually change, either. If I change the rs.Find criterion from
cp_trust to a unique ID, we still get the error (2001), but the
behavior of having a record we can move to without throwing an error
after having changed the first record disappears.

If after getting one of these errors I move back to the first record
and press Escape, the little pencil icon that indicates a record has
changed goes away, the change is NOT rolled back in the database or on
the form, and I can now switch records without any errors.

If I add a line to call Me.Recordset.Update at the beginning of the
AfterUpdate sub, the new line causes the update *only in the case of
the first record having changed*, the line has no effect for changes to
other records, in which case the update goes through when the line that
assigns Me.Bookmark is executed. (I know this from stepping through the
code while watching SQL Profiler)

As a final control, if I make a form that doesn't have the list box
control, I can modify the first record and move to a different record
without causing the error.

I've gathered all this behavioral information in the hopes of finding a
workaround, to no avail.

So I ask you: what ON EARTH is going on here?

 

[Malcolm Cook]

Hmmm... a few observations...

with ADP you want to use me.recordset.clone (as the wizard wrote) to get the ADO recordset. me.recordsetclone is a DAO method.

I wonder what causes your changes to become commited at all, ever, before you started adding 'Me.Recordset.Update ' and or
'DoCmd.RunCommand acCmdSaveRecord '. I think we need to understand that before we suggest adding it in or otherwise changing the
method for saving your results. Is there another snippet somewhere forcing a save?

Nonetheless, I find that `me.IsDirty = False` is the most reliable way to save a dirty record in both AC2002 and AC2003 (spoiler:
warnings that this is an undocumented feature will be ignored by me - it works - I use it - when it no longer works, I'll have to
accomdate and find a better method).

Finally, I wonder if you should add a (gratuitous) movefirst before the find. THis from ADO 2.8 API Reference, "Note An error
will occur if a current row position is not set before calling Find. Any method that sets row position, such as MoveFirst, should be
called before calling Find."

Good luck - keep us posted.

--
Malcolm Cook -
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

The control is unbound, and we tried RecordSet.Clone and RecordsetClone
without success. The dirty flag seems to be reliable in all cases, so
we might try acCmdSaveRecord. We're basically just working around it by
writing all the queries ourselves, which sucks, but that's access for
you. Thanks very much for your help.

I forgot to mention that there have been many reports in the past about bugs
associated with RecordsetClone operations and the 2000 and 2002 versions of
Access ADP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

First, it's not clear from your explanation if this combobox is a bound or
unbound control to a field.

Second, I don't know the difference between Me.RecordSet.Clone and
Me.RecordsetClone but you can give it a try.

Third, you can also try testing the IsDirty property and save any pending
change using DoCmd.RunCommand acCmdSaveRecord instead of
Me.Recordset.Update.

Finally, I'm not sure if your find method will find anything if you are
located past the searching value, so you should repeat the search from the
beginning of the list if the next record is not found.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I have a form in an ADP connected to a SQL Server 2005 backend, with a
record source that is a view that includes two joins but is still
updateable.

The form has a list box control in the header section that is supposed
to allow the user to select a record to show in the detail section, set
up by the list box wizard, which created the following code to
implement this functionality:

Private Sub List52_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.recordSet.Clone
rs.Find "[cp_trust] = '" & Me![List52] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

For all records except one, I can change the record and then select a
different record in the list box, and have the record I changed be
updated as I move away to a different record.

BUT if the *first record* in the list box is selected, and I change a
value in that record, when I move to a different record, I get
"Run-time error 2001: You canceled the previous operation.", on the
line that assigns Me.Bookmark. I know from SQL Profiler that the record
has already been updated before this error pops up. If I click "End" to
get rid of the error window and select a different record, the error
pops up again, unless (and this is weird) there is another record in
the list box with the same value of cp_trust as the first record, in
which case the error doesn't come up, but the current record doesn't
actually change, either. If I change the rs.Find criterion from
cp_trust to a unique ID, we still get the error (2001), but the
behavior of having a record we can move to without throwing an error
after having changed the first record disappears.

If after getting one of these errors I move back to the first record
and press Escape, the little pencil icon that indicates a record has
changed goes away, the change is NOT rolled back in the database or on
the form, and I can now switch records without any errors.

If I add a line to call Me.Recordset.Update at the beginning of the
AfterUpdate sub, the new line causes the update *only in the case of
the first record having changed*, the line has no effect for changes to
other records, in which case the update goes through when the line that
assigns Me.Bookmark is executed. (I know this from stepping through the
code while watching SQL Profiler)

As a final control, if I make a form that doesn't have the list box
control, I can modify the first record and move to a different record
without causing the error.

I've gathered all this behavioral information in the hopes of finding a
workaround, to no avail.

So I ask you: what ON EARTH is going on here?

 

[kaioptera]

I tried both of your suggestions and unfortunately they did not work.
Thanks for the help, though.

Hmmm... a few observations...

with ADP you want to use me.recordset.clone (as the wizard wrote) to get the ADO recordset. me.recordsetclone is a DAO method.

I wonder what causes your changes to become commited at all, ever, before you started adding 'Me.Recordset.Update ' and or
'DoCmd.RunCommand acCmdSaveRecord '. I think we need to understand that before we suggest adding it in or otherwise changing the
method for saving your results. Is there another snippet somewhere forcing a save?

Nonetheless, I find that `me.IsDirty = False` is the most reliable way to save a dirty record in both AC2002 and AC2003 (spoiler:
warnings that this is an undocumented feature will be ignored by me - it works - I use it - when it no longer works, I'll have to
accomdate and find a better method).

Finally, I wonder if you should add a (gratuitous) movefirst before the find. THis from ADO 2.8 API Reference, "Note An error
will occur if a current row position is not set before calling Find. Any method that sets row position, such as MoveFirst, should be
called before calling Find."

Good luck - keep us posted.

--
Malcolm Cook -
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

The control is unbound, and we tried RecordSet.Clone and RecordsetClone
without success. The dirty flag seems to be reliable in all cases, so
we might try acCmdSaveRecord. We're basically just working around it by
writing all the queries ourselves, which sucks, but that's access for
you. Thanks very much for your help.

I forgot to mention that there have been many reports in the past about bugs
associated with RecordsetClone operations and the 2000 and 2002 versions of
Access ADP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message First, it's not clear from your explanation if this combobox is a bound or
unbound control to a field.

Second, I don't know the difference between Me.RecordSet.Clone and
Me.RecordsetClone but you can give it a try.

Third, you can also try testing the IsDirty property and save any pending
change using DoCmd.RunCommand acCmdSaveRecord instead of
Me.Recordset.Update.

Finally, I'm not sure if your find method will find anything if you are
located past the searching value, so you should repeat the search from the
beginning of the list if the next record is not found.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I have a form in an ADP connected to a SQL Server 2005 backend, with a
record source that is a view that includes two joins but is still
updateable.

The form has a list box control in the header section that is supposed
to allow the user to select a record to show in the detail section, set
up by the list box wizard, which created the following code to
implement this functionality:

Private Sub List52_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.recordSet.Clone
rs.Find "[cp_trust] = '" & Me![List52] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

For all records except one, I can change the record and then select a
different record in the list box, and have the record I changed be
updated as I move away to a different record.

BUT if the *first record* in the list box is selected, and I change a
value in that record, when I move to a different record, I get
"Run-time error 2001: You canceled the previous operation.", on the
line that assigns Me.Bookmark. I know from SQL Profiler that the record
has already been updated before this error pops up. If I click "End" to
get rid of the error window and select a different record, the error
pops up again, unless (and this is weird) there is another record in
the list box with the same value of cp_trust as the first record, in
which case the error doesn't come up, but the current record doesn't
actually change, either. If I change the rs.Find criterion from
cp_trust to a unique ID, we still get the error (2001), but the
behavior of having a record we can move to without throwing an error
after having changed the first record disappears.

If after getting one of these errors I move back to the first record
and press Escape, the little pencil icon that indicates a record has
changed goes away, the change is NOT rolled back in the database or on
the form, and I can now switch records without any errors.

If I add a line to call Me.Recordset.Update at the beginning of the
AfterUpdate sub, the new line causes the update *only in the case of
the first record having changed*, the line has no effect for changes to
other records, in which case the update goes through when the line that
assigns Me.Bookmark is executed. (I know this from stepping through the
code while watching SQL Profiler)

As a final control, if I make a form that doesn't have the list box
control, I can modify the first record and move to a different record
without causing the error.

I've gathered all this behavioral information in the hopes of finding a
workaround, to no avail.

So I ask you: what ON EARTH is going on here?

 

[Malcolm Cook]

ok, but, still

--Malcolm

I tried both of your suggestions and unfortunately they did not work.
Thanks for the help, though.

Hmmm... a few observations...

with ADP you want to use me.recordset.clone (as the wizard wrote) to get the ADO recordset. me.recordsetclone is a DAO method.

I wonder what causes your changes to become commited at all, ever, before you started adding 'Me.Recordset.Update ' and or
'DoCmd.RunCommand acCmdSaveRecord '. I think we need to understand that before we suggest adding it in or otherwise changing the
method for saving your results. Is there another snippet somewhere forcing a save?

Nonetheless, I find that `me.IsDirty = False` is the most reliable way to save a dirty record in both AC2002 and AC2003 (spoiler:
warnings that this is an undocumented feature will be ignored by me - it works - I use it - when it no longer works, I'll have to
accomdate and find a better method).

Finally, I wonder if you should add a (gratuitous) movefirst before the find. THis from ADO 2.8 API Reference, "Note An error
will occur if a current row position is not set before calling Find. Any method that sets row position, such as MoveFirst, should
be
called before calling Find."

Good luck - keep us posted.

--
Malcolm Cook -
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

The control is unbound, and we tried RecordSet.Clone and RecordsetClone
without success. The dirty flag seems to be reliable in all cases, so
we might try acCmdSaveRecord. We're basically just working around it by
writing all the queries ourselves, which sucks, but that's access for
you. Thanks very much for your help.

Sylvain Lafontaine (fill the blanks, no spam please) wrote:
I forgot to mention that there have been many reports in the past about bugs
associated with RecordsetClone operations and the 2000 and 2002 versions of
Access ADP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message First, it's not clear from your explanation if this combobox is a bound or
unbound control to a field.

Second, I don't know the difference between Me.RecordSet.Clone and
Me.RecordsetClone but you can give it a try.

Third, you can also try testing the IsDirty property and save any pending
change using DoCmd.RunCommand acCmdSaveRecord instead of
Me.Recordset.Update.

Finally, I'm not sure if your find method will find anything if you are
located past the searching value, so you should repeat the search from the
beginning of the list if the next record is not found.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I have a form in an ADP connected to a SQL Server 2005 backend, with a
record source that is a view that includes two joins but is still
updateable.

The form has a list box control in the header section that is supposed
to allow the user to select a record to show in the detail section, set
up by the list box wizard, which created the following code to
implement this functionality:

Private Sub List52_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.recordSet.Clone
rs.Find "[cp_trust] = '" & Me![List52] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

For all records except one, I can change the record and then select a
different record in the list box, and have the record I changed be
updated as I move away to a different record.

BUT if the *first record* in the list box is selected, and I change a
value in that record, when I move to a different record, I get
"Run-time error 2001: You canceled the previous operation.", on the
line that assigns Me.Bookmark. I know from SQL Profiler that the record
has already been updated before this error pops up. If I click "End" to
get rid of the error window and select a different record, the error
pops up again, unless (and this is weird) there is another record in
the list box with the same value of cp_trust as the first record, in
which case the error doesn't come up, but the current record doesn't
actually change, either. If I change the rs.Find criterion from
cp_trust to a unique ID, we still get the error (2001), but the
behavior of having a record we can move to without throwing an error
after having changed the first record disappears.

If after getting one of these errors I move back to the first record
and press Escape, the little pencil icon that indicates a record has
changed goes away, the change is NOT rolled back in the database or on
the form, and I can now switch records without any errors.

If I add a line to call Me.Recordset.Update at the beginning of the
AfterUpdate sub, the new line causes the update *only in the case of
the first record having changed*, the line has no effect for changes to
other records, in which case the update goes through when the line that
assigns Me.Bookmark is executed. (I know this from stepping through the
code while watching SQL Profiler)

As a final control, if I make a form that doesn't have the list box
control, I can modify the first record and move to a different record
without causing the error.

I've gathered all this behavioral information in the hopes of finding a
workaround, to no avail.

So I ask you: what ON EARTH is going on here?

 

[kaioptera]

Before the explicit save was added, the changes were committed by the
rs.Find line - I assume because this changed the current record in the
RecordSet.

Thanks,
Seth

ok, but, still

--Malcolm

I tried both of your suggestions and unfortunately they did not work.
Thanks for the help, though.

Hmmm... a few observations...

with ADP you want to use me.recordset.clone (as the wizard wrote) to get the ADO recordset. me.recordsetclone is a DAO method.

I wonder what causes your changes to become commited at all, ever, before you started adding 'Me.Recordset.Update ' and or
'DoCmd.RunCommand acCmdSaveRecord '. I think we need to understand that before we suggest adding it in or otherwise changing the
method for saving your results. Is there another snippet somewhere forcing a save?

Nonetheless, I find that `me.IsDirty = False` is the most reliable way to save a dirty record in both AC2002 and AC2003 (spoiler:
warnings that this is an undocumented feature will be ignored by me - it works - I use it - when it no longer works, I'll have to
accomdate and find a better method).

Finally, I wonder if you should add a (gratuitous) movefirst before the find. THis from ADO 2.8 API Reference, "Note An error
will occur if a current row position is not set before calling Find. Any method that sets row position, such as MoveFirst, should
be
called before calling Find."

Good luck - keep us posted.

--
Malcolm Cook -
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA


The control is unbound, and we tried RecordSet.Clone and RecordsetClone
without success. The dirty flag seems to be reliable in all cases, so
we might try acCmdSaveRecord. We're basically just working around it by
writing all the queries ourselves, which sucks, but that's access for
you. Thanks very much for your help.

Sylvain Lafontaine (fill the blanks, no spam please) wrote:
I forgot to mention that there have been many reports in the past about bugs
associated with RecordsetClone operations and the 2000 and 2002 versions of
Access ADP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message First, it's not clear from your explanation if this combobox is a bound or
unbound control to a field.

Second, I don't know the difference between Me.RecordSet.Clone and
Me.RecordsetClone but you can give it a try.

Third, you can also try testing the IsDirty property and save any pending
change using DoCmd.RunCommand acCmdSaveRecord instead of
Me.Recordset.Update.

Finally, I'm not sure if your find method will find anything if you are
located past the searching value, so you should repeat the search from the
beginning of the list if the next record is not found.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I have a form in an ADP connected to a SQL Server 2005 backend, with a
record source that is a view that includes two joins but is still
updateable.

The form has a list box control in the header section that is supposed
to allow the user to select a record to show in the detail section, set
up by the list box wizard, which created the following code to
implement this functionality:

Private Sub List52_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.recordSet.Clone
rs.Find "[cp_trust] = '" & Me![List52] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

For all records except one, I can change the record and then select a
different record in the list box, and have the record I changed be
updated as I move away to a different record.

BUT if the *first record* in the list box is selected, and I change a
value in that record, when I move to a different record, I get
"Run-time error 2001: You canceled the previous operation.", on the
line that assigns Me.Bookmark. I know from SQL Profiler that the record
has already been updated before this error pops up. If I click "End" to
get rid of the error window and select a different record, the error
pops up again, unless (and this is weird) there is another record in
the list box with the same value of cp_trust as the first record, in
which case the error doesn't come up, but the current record doesn't
actually change, either. If I change the rs.Find criterion from
cp_trust to a unique ID, we still get the error (2001), but the
behavior of having a record we can move to without throwing an error
after having changed the first record disappears.

If after getting one of these errors I move back to the first record
and press Escape, the little pencil icon that indicates a record has
changed goes away, the change is NOT rolled back in the database or on
the form, and I can now switch records without any errors.

If I add a line to call Me.Recordset.Update at the beginning of the
AfterUpdate sub, the new line causes the update *only in the case of
the first record having changed*, the line has no effect for changes to
other records, in which case the update goes through when the line that
assigns Me.Bookmark is executed. (I know this from stepping through the
code while watching SQL Profiler)

As a final control, if I make a form that doesn't have the list box
control, I can modify the first record and move to a different record
without causing the error.

I've gathered all this behavioral information in the hopes of finding a
workaround, to no avail.

So I ask you: what ON EARTH is going on here?