Solved

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

Posted on 2011-02-24
13
399 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:jeff09
  • 7
  • 6
13 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 34972488
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
 

Author Comment

by:jeff09
ID: 34972759
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
 

Author Comment

by:jeff09
ID: 34974239
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
 
LVL 77

Expert Comment

by:peter57r
ID: 34977683
"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
 

Author Comment

by:jeff09
ID: 34978134
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
 
LVL 77

Expert Comment

by:peter57r
ID: 34978232
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:jeff09
ID: 34978294
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
 
LVL 77

Expert Comment

by:peter57r
ID: 34979213
Can you modify the code to...

Me.AllowEdits = False
Else
Me.AllowEdits = True

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

Author Comment

by:jeff09
ID: 34979300
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
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 34979759
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
 

Author Comment

by:jeff09
ID: 34980656
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
 
LVL 77

Expert Comment

by:peter57r
ID: 34981743
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
 

Author Comment

by:jeff09
ID: 34995903
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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now