I am using a subform and having users check off items in a schedule as
complete in a check box. Afterupdate, code executes that bookmarks the
current record and scans through the list of tasks to verify which task was
the last completed item in the list. The bookmark then returns the user to
the starting location where he just checked off the item. The bookmark
crashes bout 1 in 5 times with "No current record".

I noticed that when I use the scroll bar on the subform to scan down the
list of tasks, the focus kicks over to the check box column. Whenever I
click the check box where the focus is set, I get the above stated error.
What am I missing?

If you cannot figure it out, is there a way to automate moving the focus to
another record or field that would cheat around the problem?

Ryan

When you say, "scans through the list of tasks" - there are two ways to
scan through the records displayed by a form.

(1) Use GoToNextRecord (or whatever it is). This has the same effect as
the user pressing next-record to go to the next record. Then you could
use bookmarks to return to the original record.

(2) Use the form's recrdsetclone. Navigating through that recordset,
does not cause the form to move its current record. So there is no need
to return to the original current record - you never moved off it.

Which are you using? It sounds like (1). If so, why? (2) is just as
easy *and* it does not change the current record.

HTH,
TC (MVP Access)
http://tc2.atspace.com

Thanks TC.

I did not understand the recordset clone. I will look into it and adjust my
code. That should help in this case, but I do have one other case where I
move down the recordset and change date values based on a date entered in the
record above. Can you change field values using the recordset clone? Or does
it just work for reading in values?

"TC" wrote:

> When you say, "scans through the list of tasks" - there are two ways to
> scan through the records displayed by a form.
>
> (1) Use GoToNextRecord (or whatever it is). This has the same effect as
> the user pressing next-record to go to the next record. Then you could
> use bookmarks to return to the original record.
>
> (2) Use the form's recrdsetclone. Navigating through that recordset,
> does not cause the form to move its current record. So there is no need
> to return to the original current record - you never moved off it.
>
> Which are you using? It sounds like (1). If so, why? (2) is just as
> easy *and* it does not change the current record.
>
> HTH,
> TC (MVP Access)
> http://tc2.atspace.com
>
>

Ryan, I'm actually not sure if recordsetclone is read/write, or
read-only. But it would be very easy to test. Try something like this
in a command button:

with me.recordsetclone
.movefirst
.edit
![xxxx] = "!!"
.update
end with
me.requery

That test code assumes that the form does have at least one record.
Replace xxxx with the name of a text field (in the underlying recordset
of the form) which is displayed on the form *and* which would accept
the value of "!!" if that value were entered using normal means. Then
open the form, view the first record, click the button, & see if that
field changes to "!!".

HTH,
TC (MVP Access)
http://tc2.atspace.com

TC

This works great... only one problem, I have this triggered by "Afterupdate"
of the "Complete" check box. However, the recordset clone does not pick up
the check box update. So I am always one record off on figuring out what the
last item checked off is. Is there a way to ensure that the recordset clone
is current?


This is a task check list. Anytime someone checks off an item, and the
check offs may not be in the order of the actual list, the program reads
through the recordset and finds the last item in the list that is checked as
complete.

My code is below:


Dim LastCompleteTask As String ' Description of the task completed
Me.RecordsetClone.MoveFirst
Do While Me.RecordsetClone.EOF = False
If Me.RecordsetClone.Complete.Value = -1 Then '
LastCompleteTask = Me.RecordsetClone.Task.Value
End If
Me.RecordsetClone.MoveNext
Loop

'Set last complete task value in form
Forms![SCHEDULE]![Child38].Form![LastCompTask] = LastCompleteTask

"TC" wrote:

> Ryan, I'm actually not sure if recordsetclone is read/write, or
> read-only. But it would be very easy to test. Try something like this
> in a command button:
>
> with me.recordsetclone
> .movefirst
> .edit
> ![xxxx] = "!!"
> .update
> end with
> me.requery
>
> That test code assumes that the form does have at least one record.
> Replace xxxx with the name of a text field (in the underlying recordset
> of the form) which is displayed on the form *and* which would accept
> the value of "!!" if that value were entered using normal means. Then
> open the form, view the first record, click the button, & see if that
> field changes to "!!".
>
> HTH,
> TC (MVP Access)
> http://tc2.atspace.com
>
>

So, ignoring the "one record off" problem for the moment: updating a
record in recordsetclone, *does* update the record in the database
table? You can confirm that to me? (I wasn't sure if it would, or it
wouldn't.)

As for the "one record off" problem, recordsetclone is not
automatically synchronized to the current record of the form. But you
can easily do that, like this:

me.recordsetclone.bookmark = me.bookmark

At that point, recordsetclone *is* positioned to the current record of
the form. Check up the Bookmark property in F1 help.

HTH,
TC (MVP Access)
http://tc2.atspace.com