restrict edits to last record on continuous form

[AkAlan via AccessMonster.com]

I'm looking to restrict editing to the last record of a contiuous sub-form.
I've been playing with creating a recordset clone and moving to the next
record to check for EOF and setting allow edits as appropriate but I can't
seem to get it in the right event. I've tried the forms before update and
before insert but alway have issues. Does anyone have a good method for doing
this? Doesn't seem like it should be that difficult. Here is the code I run.
Thanks for any help

Dim rst As ADODB.Recordset
Set rst = Me.RecordsetClone

rst.MoveNext

If rst.EOF Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If

rst.MovePrevious
Set rst = Nothing-

 

[Allen Browne]

You want to allow edits only if the current record is the last one in the
form's RecordsetClone?

This example assumes a numeric primary key field named "ID":

Dim rs As DAO.Recordset
Dim lngLastID As Long
Dim bAllowEdit As Boolean

If Not Me.NewRecord Then
Set rs = Me.RecorsetClone
rs.MoveLast
lngLastID = rs!ID
rs.MoveNext
If rs.EOF Then
bAllowEdit = (Me.ID = lngLastID)
End If

If Me.AllowEdits <> bAllowEdit Then
Me.AllowEdits = bAllowEdit
End If
Set rs = Nothing
End If

Not sure why you have this requirement, but could a user could defeat your
intentions by filtering or sorting their form?

 

[AkAlan via AccessMonster.com]

Allen, Thanks for replying to my post. I do have a couple of questions though.
If you move to the last record of a recordset then move next it seems to me
that you will always get an EOF, is that not true? My biggest problem is
actually not the code but which event to use to trigger it. As for why I
need it, I'm tracking work date & times, action taken and current status on
equipment in the form of a job ticket and I can't have the worker bees going
back and altering on a job from previously entered records. I only want them
to put in new lines of work. It's not like I don't trust the users integrity
(who does) just want to keep them from making mistakes and altering reports.
Thanks again for your help and good job on your site, lots of good stuff
there.

You want to allow edits only if the current record is the last one in the
form's RecordsetClone?

This example assumes a numeric primary key field named "ID":

Dim rs As DAO.Recordset
Dim lngLastID As Long
Dim bAllowEdit As Boolean

If Not Me.NewRecord Then
Set rs = Me.RecorsetClone
rs.MoveLast
lngLastID = rs!ID
rs.MoveNext
If rs.EOF Then
bAllowEdit = (Me.ID = lngLastID)
End If

If Me.AllowEdits <> bAllowEdit Then
Me.AllowEdits = bAllowEdit
End If
Set rs = Nothing
End If

Not sure why you have this requirement, but could a user could defeat your
intentions by filtering or sorting their form?

I'm looking to restrict editing to the last record of a contiuous
sub-form.

[quoted text clipped - 20 lines]

rst.MovePrevious
Set rst = Nothing-

 

[Allen Browne]

You're right. The test for EOF is unnecessary.

Setting the form's AllowEdits to No and AllowAdditions to Yes would permit
new entries, but not editing only ones. Presumably that's not enough for
what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, Thanks for replying to my post. I do have a couple of questions
though.
If you move to the last record of a recordset then move next it seems to
me
that you will always get an EOF, is that not true? My biggest problem is
actually not the code but which event to use to trigger it. As for why I
need it, I'm tracking work date & times, action taken and current status
on
equipment in the form of a job ticket and I can't have the worker bees
going
back and altering on a job from previously entered records. I only want
them
to put in new lines of work. It's not like I don't trust the users
integrity
(who does) just want to keep them from making mistakes and altering
reports.
Thanks again for your help and good job on your site, lots of good stuff
there.

You want to allow edits only if the current record is the last one in the
form's RecordsetClone?

This example assumes a numeric primary key field named "ID":

Dim rs As DAO.Recordset
Dim lngLastID As Long
Dim bAllowEdit As Boolean

If Not Me.NewRecord Then
Set rs = Me.RecorsetClone
rs.MoveLast
lngLastID = rs!ID
rs.MoveNext
If rs.EOF Then
bAllowEdit = (Me.ID = lngLastID)
End If

If Me.AllowEdits <> bAllowEdit Then
Me.AllowEdits = bAllowEdit
End If
Set rs = Nothing
End If

Not sure why you have this requirement, but could a user could defeat your
intentions by filtering or sorting their form?

I'm looking to restrict editing to the last record of a contiuous
sub-form.

[quoted text clipped - 20 lines]

rst.MovePrevious
Set rst = Nothing

 

[AkAlan via AccessMonster.com]

Thanks Allen, You are correct, i need users to be able to edit only the last
record so setting allow edits to no prevents that. I just need to check to
see if the user is on the last record before allowing them to edit. I'm not
able to determine which event to trigger the code on. I've tried the before
update but once I set the allow edits to no I can't get that event to trigger
again even when the user moves to the last record. I also looked at the On
Current event but it runs at least 10 times every time the form is loaded so
I don't want to go that route. I'm sure I'll figure it out after I pull the
rest of my hair out. Thanks again.

 

[missinglinq via AccessMonster.com]

I ran this code in a continuous form and it works as per your requirements:

Private Sub Form_Current()
If CurrentRecord = RecordsetClone.RecordCount Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If
End Sub

 

[AkAlan via AccessMonster.com]

Thanks, it does work. One thing I don't understand though is why the On
Current event is triggered a dozen times in a row while the form is loading.
I am going to use it since there does not seem to be a performance hit.

 

[Allen Browne]

The event can run several times. Lots of things affect that, including the
nature of the recordsource (particularly if it refers to anything on the
form), conditional formatting, what code you have in the events, and so on.