UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Fields Locked Or Read Only Automatically After Update Query    
 
   
amy
post Jul 12 2018, 07:41 AM
Post#1



Posts: 17
Joined: 16-April 18



Hi,
I have some fields like Time ID,Employee,Date,Order,Oper Numb etc in Time tracking table.
I am using form and subform in which the main form uses employee table and subform uses Time tracking tbl.
I have another form for managers who can view the employee's timesheet and approves the timesheet.
For approval button i use update query to run. Ex: When the approve button is clicked all the "Not Approved" will change to "Approved".
Here i want to use some vb code or any method to make the fields locked or read only for the timetracking table fields(see first line) automatically after the managers Approves it.So that no one can make changes after the approval.
Can some one help with this.


Thanks
Go to the top of the page
 
theDBguy
post Jul 12 2018, 09:41 AM
Post#2


Access Wiki and Forums Moderator
Posts: 72,726
Joined: 19-June 07
From: SunnySandyEggo


Hi,

One way is to use the Form's Before Update event to check if the current record has already been approved or not and cancel any changes as necessary.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
BruceM
post Jul 12 2018, 09:49 AM
Post#3


UtterAccess VIP
Posts: 7,502
Joined: 24-May 10
From: Downeast Maine


How about using the form's Current event:
CODE
If Me.Approved = True Then
    Me.AllowEdits = False
  Else
    Me.AllowEdits = True
End If


or a shorter version:
CODE
Me.AllowEdits = (Me.Approved = False)


You may want to do similar for AllowDeletions and AllowAdditions.

Also, consider that it may be necessary from time to time for an administrator to go into a completed record to fix something. If so, there are several ways to go about it depending on the specific situation, but I'll leave it here for now.
Go to the top of the page
 
amy
post Jul 12 2018, 01:24 PM
Post#4



Posts: 17
Joined: 16-April 18



hi,
am not sure where to give the VBA code.

I tries in timesheetsub which is the subform for the event Form _current,

it showed error.

Attached is the screenshot.

here i want to give the allow edits to false for date,order#,Operation#,Nightshift,roadbonus,hours and approval fields.

This should happen after the Not Approved turns Approved(where i am running an update query )

Once it change to Approved state no one should be able to edit or add or delete.

Pls let me know if you have any questions.

Thanks
Attached File(s)
Attached File  err18.PNG ( 14.43K )Number of downloads: 2
 
Go to the top of the page
 
BruceM
post Jul 12 2018, 01:55 PM
Post#5


UtterAccess VIP
Posts: 7,502
Joined: 24-May 10
From: Downeast Maine


AFAIK query fields cannot be locked. However, forms can be locked, either the entire form, individual records, or selected fields. This could be done in the form's Current event. The Current event runs as soon as you arrive at a record. The graphic you posted seems to be datasheet view of a form. The fields you described are most of the fields on the form. I can't imagine you would want to change the TimeID or the Employee, so it looks as if you want to lock the record.

QUOTE
I tries in timesheetsub which is the subform for the event Form _current

I don't know what you mean. Please post the code you used.
Go to the top of the page
 
amy
post Jul 13 2018, 10:27 AM
Post#6



Posts: 17
Joined: 16-April 18



Hi..
Thanks for your response.
I got it worked.
The datasheet i used it as subform.and i added in form current event..

Private Sub Form_Current()
If Me.approval Like "Approved" Then
Me.Form.AllowEdits = False
Me.Form.AllowAdditions = False
Me.Form.AllowDeletions = False

ElseIf Me.approval Like "Not Approved" Then

Me.Form.AllowEdits = True
Me.Form.AllowAdditions = True
Me.Form.AllowDeletions = True

End If

End Sub.


"I tries in timesheetsub which is the subform for the event Form _current".......Sorry..This is Typo..I tried*

Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th July 2018 - 07:31 AM