I am attempting to lock fields in an Access form from future editing after a submit button is clicked.
The criteria for which records is the user's department which i can use a DLookup to find.
Any help is appreciated.
I am attempting to lock fields in an Access form from future editing after a submit button is clicked.
The criteria for which records is the user's department which i can use a DLookup to find.
Any help is appreciated.
Single record view form? Datasheet or continuous? Subform involved? etc.
Pretty sure it would have to be a single view form that you lock the required fields on the form Current Event. Locking is usually done by looping through the control set, and if it needs to be selective, then the tag property is usually used to identify them. This is a common topic.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Actually, using the Locked Property is the one thing you can do in the Form_Current
Event for all three types of Forms...Single View, Datasheet View and Continuous View...because using Locked doesn't change the appearance of the Controls.
All Controls on all Forms will be Locked or UnLocked...depending of the Current Record...but will change to the appropriate state when another Record is selected.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
If you're saying control locking in datasheet or continuous form view works independently per record, then that's a surprise considering you can't do other things because there's only one instance of a control. I take it this means you can lock but not disable or alter visibility based on selected record. Would be nice if I can remember that when I need it, but who knows when that might be...
As far as I know, it's the only type of Formatting you can do, on all types of Forms, using VBA code.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
Thanks for the input.
Still not sure how to proceed. Let me get a bit more detailed...
Have a user form that only shows records for that user's department (i.e. Marketing). The job of the user is to update several fields in those records and when completed click a "Submit" button.
That triggers a query that update those values to the matching records.
Here is where i am stuck...
We need to lock down those fields permanently that the user (for their department only) was originally able to update. The user can still look at the records but not change.
Any examples of VBA that you can provide me with would be greatly appreciated.
A button is optional; not necessary as there are other things that might happen to commit changes to a record. That would include moving off the record (even if just going from one record to another on a datasheet).
As per previous statement, not necessary. In fact, if the form is bound to a table or updatable query, doing any action that commits changes to a record is immediate. A query isn't necessary either. All of that depends on whether or not your form is bound to anything. If not, then yes you will need something to commit the changes, and would need either a query or vba generated sql.That triggers a query that update those values to the matching records.
Here's where we get to the meat of the matter. Problem is the unknowns. This means if a field was edited, even just once, then no one can edit that field ever again but other fields in that record could be because they contain the original data? Or the whole record is locked because one field was edited? Or it doesn't matter anyway because there's only one field they can edit anyway? Any of those seem like odd ideas to me. Care to share more of the logic behind this in case there's a better way?We need to lock down those fields permanently that the user (for their department only) was originally able to update. The user can still look at the records but not change. Any examples of VBA that you can provide me with would be greatly appreciated.
BTW, I don't see where you clarified anything I asked about the form...
Consider Conditional Formatting for textbox and combobox. Exactly what is the condition that should cause fields to be locked?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Per the OP...the 'submit button' being clicked...presumably declaring the Record 'complete.' There may not be set in stone 'conditions.' For instance a Form may have Controls for types of insurance, for a given customer. Having checked 'home owners' may be a completed Record for some customers....while having checked 'home owners' and 'auto insurance' may be a completed Record for other customers. Only the user can decide if a Record is complete.
It would appear that the user can populate several fields...and not necessarily all at once. When all fields needed have been populated, he clicks the 'submit' button to declare the Record complete.
As has been stated...you don't need a button to commit the Record (assuming that this is a Bound Form) as access will do this automatically. Rather than a Command Button...I'd simply have a Checkbox and code like this...with Submitted as the Checkbox name:
Code:Private Sub Form_Current() If Me.Submitted = -1 Then Me.AllowEdits = False Else Me.AllowEdits = True End If End Sub
If you want the Record to be locked immediately it's 'submitted' you'd have to use the Submitted_AfterUpdate Event...but this time you'd have to Lock each Control individually...which could be a hassle. A Form's 'Allow' Properties, when a given Record is opened...cannot be changed, i.e. if
Me.AllowEdits = True
is in effect when the Form_Current event fires...trying to set it using
Me.AllowEdits = False
will not work...AllowEdits = True will still remain in effect...hence you'd have to Lock each Control independently.
My experience has shown that immediately someone declares a Record complete...and 'submits' it...they'll discover they forgot something...too late! So I simply allow for this by not locking the Record until it's left.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
First... thanks to all of you for your replies.
I found a solution with the example that missinglinq placed.
I added an option to the underlying query that when the form button was clicked to mark a checkbox field to "Yes"
Then i added some code so that when the form loads if the checkbox is on to make the record locked for editing.
Not sure why i thought it would be more complicated than that.
Here is the code i used...
Private Sub Form_Load()
' When form opens...
' Looks up SOM User's dept and sets filter (using query) to show only those records
Application.Echo False
cboFilterDept = DLookup("strUserDept", "tblUserRoles", "[strUserID]='" & Forms![_frmLoginVerify]!txtLoginName.Value & "'")
DoCmd.SetFilter "qryFilterForDept-_Budgeting_SOM", "", ""
Application.Echo True
If Me.Submitted = True Then
Me.AllowEdits = False
Else
Me.AllowEdits = True
End If
End Sub
Glad we could help!
Good luck with your project!
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007