Record lock when Checkbox (Yes/No) is Yes

southwestgooner

southwestgooner

Registered User.
Local time
Today, 21:47
Joined
May 14, 2009
Messages
28
Hi All,
I have a ECN table which contains many fields including ECNID it's related via relationships to another table containing part numbers linked to the ECN record in the ECN table.
The ECN table contains a 'Closed' checkbox (Yes/No) field.

What I want to do is lock the ECN table record and the related Part Number record(s) when the checkbox is ticked (Yes).
Can anyone let me know (a) if this is possible and (b) what I need to do ?

Many thanks in anticipation of a resolution to my problem.

Regards,
Steve.
 
You can't lock an individual record in a table. Basically tables are the "programmers domain" and should not be exposed to the user, therefore the necessity of locking something should never arise.

If you are accessing the record through a form then there are various ways you could achieve this "locking" you're after.
 
Uncle Gizmo said:
You can't lock an individual record in a table. Basically tables are the "programmers domain" and should not be exposed to the user, therefore the necessity of locking something should never arise.

If you are accessing the record through a form then there are various ways you could achieve this "locking" you're after.
Click to expand...


I can confirm the checkbox (yes/no) is accesed via a Form.
What are my options ?

Cheers,
Steve.
 
In the form's On Current event put
Code: 
Me.AllowEdits = Not Me!CheckBoxFieldNameHere
Me.AllowDeletions = Not Me!CheckBoxFieldNameHere
 
boblarson said:
In the form's On Current event put
Code: 
Me.AllowEdits = Not Me!CheckBoxFieldNameHere
Me.AllowDeletions = Not Me!CheckBoxFieldNameHere
Click to expand...

Hi,
I am sorry to say that didn't work as I want it to !

Let me give you some more info on the database I am trying to administer.
The database is a multi user database and the user access is controlled by the first 2 digits of their allocated login username.
The 'Closed' checkbox (Yes/No) is on the Approval form.
The following code runs from the Module on loading the 'Approval' form:

Code: 
Public Function HideOrShowApproveControls()
 
' EDIT TO SUIT frm_Approve
' WHERE: Run from from OnLoad event of frm_Approve form
'
' WHAT: Enables/disables required controls in frm_Approve form
'
' HOW: Changes the properties of controls depending on the group to which the user belongs
 
Dim frm As Form
Dim strGroup As String
 
On Error GoTo Err_HideOrShowApproveControls
 
' Trying to make this object known at compile time
Set frm = Forms!frm_Approve.Form
 
' Force username and edited date into tbl_ECN_List
frm!DateEdited = Date
frm!EditedBy = CurrentUser
 
' Assign form object to var
Set frm = Forms!frm_Approve
 
' Change Form caption to reflect user name
frm.Caption = frm.Caption & " . . . . . logged on as " & CurrentUser
 
' Capture the users group name from the first two letters of their username
strGroup = Left(CurrentUser, 2)
 
' Ensure that Close button is enabled to start
frm!Close.Enabled = True
 
' Set reqd values to false as appropriate
Select Case strGroup
 
Case "ap": frm!ECNNo.Enabled = True
frm!DateSubmitted.Enabled = True
frm!OriginatorID.Enabled = True
frm!RequestByID.Enabled = True
frm!PriorityID.Enabled = True
frm!Drawing.Enabled = True
frm!Process.Enabled = True
frm!Specification.Enabled = True
frm!Other.Enabled = True
frm!ChangeRequest.Enabled = True
frm!BatchNo.Enabled = True
frm!OpNo.Enabled = True
frm!Info1.Enabled = False
frm!Info2.Enabled = False
frm!Info3.Enabled = False
frm!RootCause.Enabled = True
frm!Investigation.Enabled = True
frm!ECNMeetingDate.Enabled = False
frm!ApprovalID.Enabled = False
frm!HeldByID.Enabled = False
frm!ApproverDOID.Enabled = False
frm!DateDO.Enabled = False
frm!ApproverMEID.Enabled = False
frm!DateME.Enabled = False
frm!ApproverQUID.Enabled = False
frm!DateQU.Enabled = False
frm!ApproverAPID.Enabled = False
frm!DateAP.Enabled = False
frm!ApproverSCID.Enabled = False
frm!DateSC.Enabled = False
frm!ApproverCHID.Enabled = False
frm!DateCH.Enabled = False
frm!ApproverCMID.Enabled = False
frm!DateCM.Enabled = False
frm!ApproverPUID.Enabled = False
frm!DatePU.Enabled = False
frm!Action.Enabled = True
frm!ECNComments.Enabled = True
frm!Closed.Enabled = False
frm!DateClosed.Enabled = False
frm!EditedBy.Enabled = True
frm!DateEdited.Enabled = True
 
frm!ApprovalStatus.Enabled = False
frm!FullName.Enabled = False
frm!FullName1.Enabled = False
frm!EmailAddress.Enabled = False
frm!EmailAddress1.Enabled = False
frm!DrawingsPulled.Enabled = False
frm!ProcessStatus1.Enabled = False
 
Case "ch": frm!ECNNo.Enabled = True
frm!DateSubmitted.Enabled = True
frm!OriginatorID.Enabled = True
frm!RequestByID.Enabled = True
frm!PriorityID.Enabled = True
frm!Drawing.Enabled = True
frm!Process.Enabled = True
frm!Specification.Enabled = True
frm!Other.Enabled = True
frm!ChangeRequest.Enabled = True
frm!BatchNo.Enabled = True
frm!OpNo.Enabled = True
frm!Info1.Enabled = False
frm!Info2.Enabled = False
frm!Info3.Enabled = False
frm!RootCause.Enabled = True
frm!Investigation.Enabled = True
frm!ECNMeetingDate.Enabled = False
frm!ApprovalID.Enabled = False
frm!HeldByID.Enabled = False
frm!ApproverDOID.Enabled = False
frm!DateDO.Enabled = False
frm!ApproverMEID.Enabled = False
frm!DateME.Enabled = False
frm!ApproverQUID.Enabled = False
frm!DateQU.Enabled = False
frm!ApproverAPID.Enabled = False
frm!DateAP.Enabled = False
frm!ApproverSCID.Enabled = False
frm!DateSC.Enabled = False
frm!ApproverCHID.Enabled = False
frm!DateCH.Enabled = False
frm!ApproverCMID.Enabled = False
frm!DateCM.Enabled = False
frm!ApproverPUID.Enabled = False
frm!DatePU.Enabled = False
frm!Action.Enabled = True
frm!ECNComments.Enabled = True
frm!Closed.Enabled = False
frm!DateClosed.Enabled = False
frm!EditedBy.Enabled = True
frm!DateEdited.Enabled = True
 
frm!ApprovalStatus.Enabled = False
frm!FullName.Enabled = False
frm!FullName1.Enabled = False
frm!EmailAddress.Enabled = False
frm!EmailAddress1.Enabled = False
frm!DrawingsPulled.Enabled = False
frm!ProcessStatus1.Enabled = False
 
Case "do": frm!ECNNo.Enabled = True
frm!DateSubmitted.Enabled = True
frm!OriginatorID.Enabled = True
frm!RequestByID.Enabled = True
frm!PriorityID.Enabled = True
frm!Drawing.Enabled = True
frm!Process.Enabled = True
frm!Specification.Enabled = True
frm!Other.Enabled = True
frm!ChangeRequest.Enabled = True
frm!BatchNo.Enabled = True
frm!OpNo.Enabled = True
frm!Info1.Enabled = False
frm!Info2.Enabled = False
frm!Info3.Enabled = False
frm!RootCause.Enabled = True
frm!Investigation.Enabled = True
frm!ECNMeetingDate.Enabled = False
frm!ApprovalID.Enabled = False
frm!HeldByID.Enabled = True
frm!ApproverDOID.Enabled = False
frm!DateDO.Enabled = False
frm!ApproverMEID.Enabled = False
frm!DateME.Enabled = False
frm!ApproverQUID.Enabled = False
frm!DateQU.Enabled = False
frm!ApproverAPID.Enabled = False
frm!DateAP.Enabled = False
frm!ApproverSCID.Enabled = False
frm!DateSC.Enabled = False
frm!ApproverCHID.Enabled = False
frm!DateCH.Enabled = False
frm!ApproverCMID.Enabled = False
frm!DateCM.Enabled = False
frm!ApproverPUID.Enabled = False
frm!DatePU.Enabled = False
frm!Action.Enabled = True
frm!ECNComments.Enabled = True
frm!Closed.Enabled = True
frm!DateClosed.Enabled = True
frm!EditedBy.Enabled = True
frm!DateEdited.Enabled = True
 
frm!ApprovalStatus.Enabled = False
frm!FullName.Enabled = False
frm!FullName1.Enabled = False
frm!EmailAddress.Enabled = False
frm!EmailAddress1.Enabled = False
frm!DrawingsPulled.Enabled = False
frm!ProcessStatus1.Enabled = False
 
Case "me": frm!ECNNo.Enabled = True
frm!DateSubmitted.Enabled = True
frm!OriginatorID.Enabled = True
frm!RequestByID.Enabled = True
frm!PriorityID.Enabled = True
frm!Drawing.Enabled = True
frm!Process.Enabled = True
frm!Specification.Enabled = True
frm!Other.Enabled = True
frm!ChangeRequest.Enabled = True
frm!BatchNo.Enabled = True
frm!OpNo.Enabled = True
frm!Info1.Enabled = False
frm!Info2.Enabled = False
frm!Info3.Enabled = False
frm!RootCause.Enabled = True
frm!Investigation.Enabled = True
frm!ECNMeetingDate.Enabled = False
frm!ApprovalID.Enabled = False
frm!HeldByID.Enabled = False
frm!ApproverDOID.Enabled = False
frm!DateDO.Enabled = False
frm!ApproverMEID.Enabled = False
frm!DateME.Enabled = False
frm!ApproverQUID.Enabled = False
frm!DateQU.Enabled = False
frm!ApproverAPID.Enabled = False
frm!DateAP.Enabled = False
frm!ApproverSCID.Enabled = False
frm!DateSC.Enabled = False
frm!ApproverCHID.Enabled = False
frm!DateCH.Enabled = False
frm!ApproverCMID.Enabled = False
frm!DateCM.Enabled = False
frm!ApproverPUID.Enabled = False
frm!DatePU.Enabled = False
frm!Action.Enabled = True
frm!ECNComments.Enabled = True
frm!Closed.Enabled = True
frm!DateClosed.Enabled = True
frm!EditedBy.Enabled = True
frm!DateEdited.Enabled = True
 
frm!ApprovalStatus.Enabled = False
frm!FullName.Enabled = False
frm!FullName1.Enabled = False
frm!EmailAddress.Enabled = False
frm!EmailAddress1.Enabled = False
frm!DrawingsPulled.Enabled = False
frm!ProcessStatus1.Enabled = False
 
Case "mg": frm!ECNNo.Enabled = True
frm!DateSubmitted.Enabled = True
frm!OriginatorID.Enabled = True
frm!RequestByID.Enabled = True
frm!PriorityID.Enabled = True
frm!Drawing.Enabled = True
frm!Process.Enabled = True
frm!Specification.Enabled = True
frm!Other.Enabled = True
frm!ChangeRequest.Enabled = True
frm!BatchNo.Enabled = True
frm!OpNo.Enabled = True
frm!Info1.Enabled = True
frm!Info2.Enabled = True
frm!Info3.Enabled = True
frm!RootCause.Enabled = True
frm!Investigation.Enabled = True
frm!ECNMeetingDate.Enabled = True
frm!ApprovalID.Enabled = True
frm!HeldByID.Enabled = True
frm!ApproverDOID.Enabled = True
frm!DateDO.Enabled = True
frm!ApproverMEID.Enabled = True
frm!DateME.Enabled = True
frm!ApproverQUID.Enabled = True
frm!DateQU.Enabled = True
frm!ApproverAPID.Enabled = True
frm!DateAP.Enabled = True
frm!ApproverSCID.Enabled = True
frm!DateSC.Enabled = True
frm!ApproverCHID.Enabled = True
frm!DateCH.Enabled = True
frm!ApproverCMID.Enabled = True
frm!DateCM.Enabled = True
frm!ApproverPUID.Enabled = True
frm!DatePU.Enabled = True
frm!Action.Enabled = True
frm!ECNComments.Enabled = True
frm!Closed.Enabled = True
frm!DateClosed.Enabled = True
frm!EditedBy.Enabled = True
frm!DateEdited.Enabled = True
 
frm!ApprovalStatus.Enabled = False
frm!FullName.Enabled = False
frm!FullName1.Enabled = False
frm!EmailAddress.Enabled = False
frm!EmailAddress1.Enabled = False
frm!DrawingsPulled.Enabled = False
frm!ProcessStatus1.Enabled = False
 
Case "qu": frm!ECNNo.Enabled = True
frm!DateSubmitted.Enabled = True
frm!OriginatorID.Enabled = True
frm!RequestByID.Enabled = True
frm!PriorityID.Enabled = True
frm!Drawing.Enabled = True
frm!Process.Enabled = True
frm!Specification.Enabled = True
frm!Other.Enabled = True
frm!ChangeRequest.Enabled = True
frm!BatchNo.Enabled = True
frm!OpNo.Enabled = True
frm!Info1.Enabled = False
frm!Info2.Enabled = False
frm!Info3.Enabled = False
frm!RootCause.Enabled = True
frm!Investigation.Enabled = True
frm!ECNMeetingDate.Enabled = False
frm!ApprovalID.Enabled = False
frm!HeldByID.Enabled = False
frm!ApproverDOID.Enabled = False
frm!DateDO.Enabled = False
frm!ApproverMEID.Enabled = False
frm!DateME.Enabled = False
frm!ApproverQUID.Enabled = False
frm!DateQU.Enabled = False
frm!ApproverAPID.Enabled = False
frm!DateAP.Enabled = False
frm!ApproverSCID.Enabled = False
frm!DateSC.Enabled = False
frm!ApproverCHID.Enabled = False
frm!DateCH.Enabled = False
frm!ApproverCMID.Enabled = False
frm!DateCM.Enabled = False
frm!ApproverPUID.Enabled = False
frm!DatePU.Enabled = False
frm!Action.Enabled = True
frm!ECNComments.Enabled = True
frm!Closed.Enabled = False
frm!DateClosed.Enabled = False
frm!EditedBy.Enabled = True
frm!DateEdited.Enabled = True
 
frm!ApprovalStatus.Enabled = False
frm!FullName.Enabled = False
frm!FullName1.Enabled = False
frm!EmailAddress.Enabled = False
frm!EmailAddress1.Enabled = False
frm!DrawingsPulled.Enabled = False
frm!ProcessStatus1.Enabled = False
 
Case "sc": frm!ECNNo.Enabled = True
frm!DateSubmitted.Enabled = True
frm!OriginatorID.Enabled = True
frm!RequestByID.Enabled = True
frm!PriorityID.Enabled = True
frm!Drawing.Enabled = True
frm!Process.Enabled = True
frm!Specification.Enabled = True
frm!Other.Enabled = True
frm!ChangeRequest.Enabled = True
frm!BatchNo.Enabled = True
frm!OpNo.Enabled = True
frm!Info1.Enabled = False
frm!Info2.Enabled = False
frm!Info3.Enabled = False
frm!RootCause.Enabled = True
frm!Investigation.Enabled = True
frm!ECNMeetingDate.Enabled = False
frm!ApprovalID.Enabled = False
frm!HeldByID.Enabled = False
frm!ApproverDOID.Enabled = False
frm!DateDO.Enabled = False
frm!ApproverMEID.Enabled = False
frm!DateME.Enabled = False
frm!ApproverQUID.Enabled = False
frm!DateQU.Enabled = False
frm!ApproverAPID.Enabled = False
frm!DateAP.Enabled = False
frm!ApproverSCID.Enabled = False
frm!DateSC.Enabled = False
frm!ApproverCHID.Enabled = False
frm!DateCH.Enabled = False
frm!ApproverCMID.Enabled = False
frm!DateCM.Enabled = False
frm!ApproverPUID.Enabled = False
frm!DatePU.Enabled = False
frm!Action.Enabled = True
frm!ECNComments.Enabled = True
frm!Closed.Enabled = False
frm!DateClosed.Enabled = False
frm!EditedBy.Enabled = True
frm!DateEdited.Enabled = True
 
frm!ApprovalStatus.Enabled = False
frm!FullName.Enabled = False
frm!FullName1.Enabled = False
frm!EmailAddress.Enabled = False
frm!EmailAddress1.Enabled = False
frm!DrawingsPulled.Enabled = False
frm!ProcessStatus1.Enabled = False
 
Case "ec": frm!ECNNo.Enabled = True
frm!DateSubmitted.Enabled = True
frm!OriginatorID.Enabled = True
frm!RequestByID.Enabled = True
frm!PriorityID.Enabled = True
frm!Drawing.Enabled = True
frm!Process.Enabled = True
frm!Specification.Enabled = True
frm!Other.Enabled = True
frm!ChangeRequest.Enabled = True
frm!BatchNo.Enabled = True
frm!OpNo.Enabled = True
frm!Info1.Enabled = True
frm!Info2.Enabled = True
frm!Info3.Enabled = True
frm!RootCause.Enabled = True
frm!Investigation.Enabled = True
frm!ECNMeetingDate.Enabled = True
frm!ApprovalID.Enabled = True
frm!HeldByID.Enabled = True
frm!ApproverDOID.Enabled = True
frm!DateDO.Enabled = True
frm!ApproverMEID.Enabled = True
frm!DateME.Enabled = True
frm!ApproverQUID.Enabled = True
frm!DateQU.Enabled = True
frm!ApproverAPID.Enabled = True
frm!DateAP.Enabled = True
frm!ApproverSCID.Enabled = True
frm!DateSC.Enabled = True
frm!ApproverCHID.Enabled = True
frm!DateCH.Enabled = True
frm!ApproverCMID.Enabled = True
frm!DateCM.Enabled = True
frm!ApproverPUID.Enabled = True
frm!DatePU.Enabled = True
frm!Action.Enabled = True
frm!ECNComments.Enabled = True
frm!Closed.Enabled = True
frm!DateClosed.Enabled = True
frm!EditedBy.Enabled = True
frm!DateEdited.Enabled = True
 
frm!ApprovalStatus.Enabled = False
frm!FullName.Enabled = False
frm!FullName1.Enabled = False
frm!EmailAddress.Enabled = False
frm!EmailAddress1.Enabled = False
frm!DrawingsPulled.Enabled = False
frm!ProcessStatus1.Enabled = False
 
Case Else: MsgBox "Shurely Shome Mishtake!!!!"
End Select
 
' Release vars
Set frm = Nothing
 
Exit_HideOrShowApproveControls:
Exit Function
 
Err_HideOrShowApproveControls:
MsgBox Err.Description
Resume Exit_HideOrShowApproveControls
End Function
The controls on the form are enabled/disabled dependant on the first 2 digits of the users login username.

Another potential issue is the 'Approval' form contains a 'PartNo' sub form.
Each record in the 'approval' form can have multiple records in the 'PartNo' subform.
What I want to do is lock/disable all the record fields in both the 'Approval' form and 'PartNo' subform from all users when the 'Closed' checkbox is ticked (Yes).
What I am basically saying is once the 'Closed' box is ticked none of the fields in both forms can be edited or new PartNos added.

I hope this info makes my predicament clearer and someone out there can help me resolve it.

Best Regards,
Steve.
 
Last edited by a moderator:
You must log in or register to reply here.

Similar threads

C
Cant Update Record
Replies
19
Views
506
Pat Hartman
P
T
Display text in calculated query field based on multiple checkboxes
Replies
4
Views
406
timbits35
T
J
Unable to delete record due to record lock
Replies
6
Views
409
The_Doc_Man
The_Doc_Man
B
Test if a compound. (two or more joined fields) exists
Replies
7
Views
462
Pat Hartman
P
A
How many similar fields should be in each table?
Replies
12
Views
748
access2010
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom