|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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?
|
|
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
<kaioptera[ at ]gmail.com> wrote in message
news:1153507023.727761.232910[ at ]s13g2000cwa.googlegroups.com...
[Quoted Text] >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?
>
|
|
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 news:%23SQ%23u6RrGHA.1848[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > 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> > > <kaioptera[ at ]gmail.com> wrote in message > news:1153507023.727761.232910[ at ]s13g2000cwa.googlegroups.com... >>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? >> > >
|
|
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:
[Quoted Text] > 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 news:%23SQ%23u6RrGHA.1848[ at ]TK2MSFTNGP02.phx.gbl... > > 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> > > > > > <kaioptera[ at ]gmail.com> wrote in message > > news:1153507023.727761.232910[ at ]s13g2000cwa.googlegroups.com... > >>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? > >> > > > >
|
|
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
<kaioptera[ at ]gmail.com> wrote in message news:1153835442.681517.275180[ at ]i42g2000cwa.googlegroups.com...
[Quoted Text] > 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 news:%23SQ%23u6RrGHA.1848[ at ]TK2MSFTNGP02.phx.gbl... >> > 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>> > >> > >> > <kaioptera[ at ]gmail.com> wrote in message >> > news:1153507023.727761.232910[ at ]s13g2000cwa.googlegroups.com... >> >>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? >> >> >> > >> > >
|
|
I tried both of your suggestions and unfortunately they did not work.
Thanks for the help, though.
Malcolm Cook wrote:
[Quoted Text] > 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 > > > <kaioptera[ at ]gmail.com> wrote in message news:1153835442.681517.275180[ at ]i42g2000cwa.googlegroups.com... > > 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 news:%23SQ%23u6RrGHA.1848[ at ]TK2MSFTNGP02.phx.gbl... > >> > 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> >> > > >> > > >> > <kaioptera[ at ]gmail.com> wrote in message > >> > news:1153507023.727761.232910[ at ]s13g2000cwa.googlegroups.com... > >> >>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? > >> >> > >> > > >> > > >
|
|
ok, but, still
[Quoted Text] >> 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?
--Malcolm
<kaioptera[ at ]gmail.com> wrote in message news:1154093427.575546.168190[ at ]s13g2000cwa.googlegroups.com...
>I tried both of your suggestions and unfortunately they did not work.
> Thanks for the help, though.
>
> Malcolm Cook wrote:
>> 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
>>
>>
>> <kaioptera[ at ]gmail.com> wrote in message news:1153835442.681517.275180[ at ]i42g2000cwa.googlegroups.com...
>> > 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 news:%23SQ%23u6RrGHA.1848[ at ]TK2MSFTNGP02.phx.gbl...
>> >> > 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
>> >> >
>> >> >
>> >> > <kaioptera[ at ]gmail.com> wrote in message
>> >> > news:1153507023.727761.232910[ at ]s13g2000cwa.googlegroups.com...
>> >> >>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?
>> >> >>
>> >> >
>> >> >
>> >
>
|
|
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
Malcolm Cook wrote:
[Quoted Text] > ok, but, still > > >> 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? > > --Malcolm > > <kaioptera[ at ]gmail.com> wrote in message news:1154093427.575546.168190[ at ]s13g2000cwa.googlegroups.com... > >I tried both of your suggestions and unfortunately they did not work. > > Thanks for the help, though. > > > > Malcolm Cook wrote: > >> 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 > >> > >> > >> <kaioptera[ at ]gmail.com> wrote in message news:1153835442.681517.275180[ at ]i42g2000cwa.googlegroups.com... > >> > 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 news:%23SQ%23u6RrGHA.1848[ at ]TK2MSFTNGP02.phx.gbl... > >> >> > 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> >> >> > > >> >> > > >> >> > <kaioptera[ at ]gmail.com> wrote in message > >> >> > news:1153507023.727761.232910[ at ]s13g2000cwa.googlegroups.com... > >> >> >>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? > >> >> >> > >> >> > > >> >> > > >> > > >
|
|
|