MS Access 2007 can I allow a user to lock records in a datasheet after updating

Yes.

Assuming that you have a button that is the 'Done' button and this button is in the form you are trying to lock, you use that button to do two things..
- set a yes/no field to True to indicate that the record should now be locked.
 (This means that the table must have such a field in it - I have assumed it called LockedYN and it should default to False)
- set the form_allowedits property to No.

Me.LockedYN = True
me.dirty = False  ' Save the record
me.Allowedits=No


You must also add code to the Form_current event procedure to check the Locked flag..

if Me.lockedYN = true then
me.Allowedits=No
else
me.Allowedits=Yes
end if

Note that the success of this depends absolutely on there being no other way to access these records.
If they are presented in any other form you must use the same Form_ Current technique there if you want to prevent changes.

Hi,

Thanks for your answer. However, as the payroll form is a datasheet form (which appears at the bottom of a subform) if i add a button to the datasheet, it doesnt show when i open the form.

Should I create the extra field you suggest and put your code in the events for that field?

I have put a field into the table named LockedYN and defaulted this to False in table design view.
I have included the extra field to be shown in the datasheet form when opened and have shown this as a Yes/No box.

I have put the following code into the form event properties. However all records i all rows are locked at all times.

Am i doing something wrong?

Private Sub Form_Current()
If Me.LockedYN = True Then
Me.AllowEdits = No
Else
Me.AllowEdits = Yes
End If
End Sub

Private Sub Locked_AfterUpdate()
Me.LockedYN = True
Me.Dirty = False  ' Save the record
Me.AllowEdits = No
End Sub

"I have put the following code into the form event properties."

Where exactly have you put the code ?
Does the Form current event  property show [Event procedure]? (It should do)

Hi Peter,

I have put the code as follows:

Opended the datasheet in design view.
Selected the property sheet,
Selection Type: Form
Select "Event" Tab
Placed code in the following two areas:

On Current [Event Proceedure]
After Update [Event Proceedure]

Have you any ideas?

I hadn't realised that this was a datasheet , although I can see now that you had said so in your Q.  
So there won't be a button on the record.  The user will change the value from No to Yes in the dropdown?

If that's the case, I'm not sure that the second bit of code is worth bothering with and might even be counter-productive if the user changes their mind about locking the record.

However, I can't understand why all records should be locked irrespective of the LcckedYN setting.

Can we check that the code is running by adding a msgbox..

Private Sub Form_Current()
msgbox "YN:" & me.LockedYN
If Me.LockedYN = True Then
Me.AllowEdits = No
Else
Me.AllowEdits = Yes
End If
msgbox "Edits: " & me.allowedits
End Sub

If you click on different records in the datasheet you should get two msgboxes each time telling you value of LockedYN and then the value of Allowedits  They should always have opposite values.

Two msg boxes displayed ok:

Messages shown when clicking an existing row was:
YN: -1
Edits: False

Messages shown when clicking a new row was:
YN: 0
Edits: False


Perhaps I should re-iterate what i am trying to achieve.

What i am trying to do is have a Yes/No drop down box (Named Lock for instance) in a datasheet that when "Yes" is selected will disable/lock/not allow edits to the row that the drop down box refers to. Thus, you cant change any of the data in a row that is "Locked" unless you select "No" from the drop down box to enable changes.

Thanks in advance

Can you modify the code to...

Me.AllowEdits = False
Else
Me.AllowEdits = True

and re-test, please.  (Still with the msgboxes in place)

i still get the two msg boxes after changing the code.

Im not sure where we are going with this so in the mean time I have made the folloing changes.

Changed the form to a Continuous Form and arranged the fields to look like a datasheet.

This way i have can lock indivual rows/records as opposed to locking the whole form.

I have added the following code to the event CURRENT & AFTER_UPDATE and all seems to work fine. This code allows me to lock individual controls that I choose in the form.

If Me.Locked = Yes Then

Me.Gross.Locked = False
Me.Materials.Locked = False

Else

Me.Gross.Locked = True
Me.Materials.Locked = True

End If

The only thing I am stuck on now is:

1) Once "Yes" is selected i would prefer the action to not be reversable and to show a msg prompt alerting the user of this

Can you help with this instead?

Thanks for the sample db.

Even though I had found my own workaround I was not that happy with it. Mainly because, there was a lot more code in there than yours and that indeed you are correct, the only way to get my code working was to reverse the code ie Unlocks for Yes Locks or No.

I have copied your sample db and all works fine now. However, I have put an onchange alert in to prompt the user before they lock the record.

Private Sub LockedYN_Change()
Dim iResponse As Integer

   ' Specify the message to display.
   strMsg = "You are about to PERMANENTLY LOCK this record. Please confirm you wish to continue." & Chr(10) & Chr(10)
   strMsg = strMsg & "Click Yes to Continue or No to cancel."

   ' Display the message box.
   iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
   
   ' Check the user's response.
   If iResponse = vbNo Then
       ' Undo the change.
       Me.Undo
       'DoCmd.RunCommand acCmdUndo
   Else
    ' Cancel the update.
    Me.LockedYN.SetFocus
    Cancel = True
    [Forms]![frmOpenContractors]![SubDetail].[Form].Refresh
   End If
End Sub

Thanks for your help

I'm not sure what you intend with the code below...At this point the user has clicked Yes to continue so I don't understand why this is labelled 'cancel the update'

 But Cancel = True is doing nothing and should be producing an error.  It should be removed.

Me.LockedYN.SetFocus is not required because the control already has focus. - otherwise you would not be in its Change event procedure.
I assume the Refresh is there to force a save of the record.


  Else
    ' Cancel the update.
    Me.LockedYN.SetFocus
    Cancel = True
    [Forms]![frmOpenContractors]![SubDetail].[Form].Refresh
   End If

Yes the Refresh is there to force a save of the record.

I have taken out the two lines of code below and all runs fine, so thanks for that
Me.LockedYN.SetFocus
Cancel = True