Locking a single record or row in access subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

M.John - 05 Dec 2007 18:43 GMT

Hi,

I Hope you guys can help me. I need to lock a single record in a suborm. The
code I have is (in the subform)
I basically need to allow only one addition to the table, after this they
should only be allowed to edit the record, no further additions. The problem
is that it locks all of the records as it is locking the table and not the
individual row for each subform record.
Private Sub Form_Current()
Dim rs As Recordset
rs.Clone
rs.MoveLast
If rs.RecordCount = 0 Then
Me.AllowAdditions = True
Me.AllowEdits = True
Else
Me.AllowAdditions = False
Me.AllowEdits = True
End Sub

What am I doing wrong, is it possible to lock down just a single row ?.

Thanks in advance

AMJ

Reply to this Message

Albert D. Kallal - 05 Dec 2007 19:26 GMT

Why not just turn off the allow additions, and then add one record via code?

there is number of ways to add the record.

You could use the sub-forms *CONTROL*'s on-enter event...

eg:

Dim rst As DAO.Recordset

Set rst = Me.child1_test.Form.RecordsetClone

If rst.RecordCount = 0 Then
rst.AddNew
rst!contact_id = Me.ContactID
rst.Update
Me.child1_test.Requery
End If

remember, when you use code to add a record, the forms settings are complete
ignored. So, just turn off the sub-forms ability to add records, and then
use code.

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message

M.John - 05 Dec 2007 20:26 GMT

Hi, thanks for the reply.

Maybee I have not understood you but the subforms control is not available
when there are no records so I can not use the controls eo enter event to
create a record ?.

Thanks

AMJ

> Why not just turn off the allow additions, and then add one record via
> code?
[quoted text clipped - 19 lines]
> complete ignored. So, just turn off the sub-forms ability to add records,
> and then use code.

Reply to this Message

Albert D. Kallal - 06 Dec 2007 20:00 GMT

> Hi, thanks for the reply.
>
[quoted text clipped - 5 lines]
>
> AMJ

It works for me...I just tired it. When you load up the form, the sub-form
will be blank, and not show any records, but if you click on the sub-form,
then the on-enter code DOES run and fire. Remember, we talking about the
sub-form control...not the actual form used for the sub-form,and NOT any
contrls on the sub-form.

It is possible your setting the sub forms data in code? (or are you using
the link child/master fields to maintain the relationship to the main form).

If you just using a standard main form, and sub-form (both bound to their
respective tables), then this should work. I suppose you could place a
button on the main form to "edit", or run the sample code, but it should
work just fine in the on enter event of the *sub form control*...not the
actual sub-form.

So, I NOT using any events on the actual sub-form, but am using the sub-form
control....

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message