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

Hi there,

I have a problem that I hope someone can assist me with.

Within Access 2007 I have a form[Contractors], within that form is a subform[SubContractors] and within the subform is a datasheet form[Payroll].

The Payroll datasheet consists of the following fileds; date, gross, materials, tax, fee, net.

At present the user can enter data into the fileds and if they need to make an adjustment at a later date they go back to the form and edit any fields they require. I have this already setup and working fine.

However, what I would really like to achieve is the following;
When the user is satisfied the data they have entered into a row is correct, could I put an extra field in the datasheet or a button within the form that allows the user to lock the row in question. I must point out that once the row is locked it must not able to be unlocked.

Is this possible?

Many thanks
jeff09Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
peter57rConnect With a Mentor Commented:
The code you have posted does exactly the opposite of what you said you wanted to achieve.

"when "Yes" is selected will disable/lock/not allow edits to "
Your code Unlocks for Yes and Locks for No.

Also I had assumed the following error was a typo in your post  but as you have now posted it twice I need to check.

If Me.Locked = Yes Then

should be
If Me.LockedYN = Yes Then

(assuming the lock field in the table is called LockedYN)

The locking and unlocking of either the form or of controls on the form has to be controlled in the Form_Current event procedure.  If the purpose is to lock the whole record there is nothing to be gained from locking individual controls.

I am attaching a sample db which does what I understand you want.
If you open frmSample you will see a LockedYN checkbox.  You can edit the record if the checkbox is false and you cannot edit the record if the checkbox is true.


 
db1.mdb
0
 
peter57rCommented:
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.
0
 
jeff09Author Commented:
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?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
jeff09Author Commented:
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
0
 
peter57rCommented:
"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)
0
 
jeff09Author Commented:
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?
0
 
peter57rCommented:
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.
0
 
jeff09Author Commented:
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
0
 
peter57rCommented:
Can you modify the code to...

Me.AllowEdits = False
Else
Me.AllowEdits = True

and re-test, please.  (Still with the msgboxes in place)
0
 
jeff09Author Commented:
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?
0
 
jeff09Author Commented:
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
0
 
peter57rCommented:
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
0
 
jeff09Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.