Yup. Definitely the way to go.
I appreciate your help!
Cheers!
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:41edb16a$0$31095$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=blue]
> My personal preference is to loop through all the controls on the form to
> set their Locked property also.
>
> Below is the actual function I use. To lock all the controls in the
> current form, use:
> LockBoundContorls(Me, True)
> and to unlock them again:
> LockBoundContorls(Me, False)
>
> The interesting parts are:
> - only bound controls are locked;
> - subforms are locked also;
> - you can specify controls not to be locked.
>
> ------------------code starts--------------------
> Public Function LockBoundControls(frm As Form, bLock As Boolean,
> ParamArray avarExceptionList())
> On Error GoTo Err_Handler
> 'Purpose: Lock the bound controls and prevent deletes on the form any
> its subforms.
> 'Arguments frm = the form to be locked
> ' bLock = Trur to lock, False to unlock.
> ' avarExceptionList: Names of the controls NOT to lock
> (variant array of strings).
> 'Usage: Call LockBoundControls(Me. True)
> Dim ctl As Control 'Each control on the form
> Dim lngI As Long 'Loop controller.
> Dim bSkip As Boolean
>
> 'Save any edits.
> If frm.Dirty Then
> frm.Dirty = False
> End If
> 'Block deletions.
> frm.AllowDeletions = Not bLock
>
> For Each ctl In frm.Controls
> Select Case ctl.ControlType
> Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox,
> acOptionButton, acToggleButton
> 'Lock/unlock these controls if bound to fields.
> bSkip = False
> For lngI = LBound(avarExceptionList) To
> UBound(avarExceptionList)
> If avarExceptionList(lngI) = ctl.Name Then
> bSkip = True
> Exit For
> End If
> Next
> If Not bSkip Then
> If HasProperty(ctl, "ControlSource") Then
> If Len(ctl.ControlSource) > 0& And Not
> ctl.ControlSource Like "=*" Then
> If ctl.Locked <> bLock Then
> ctl.Locked = bLock
> End If
> End If
> End If
> End If
>
> Case acSubform
> 'Recursive call to handle all subforms.
> bSkip = False
> For lngI = LBound(avarExceptionList) To
> UBound(avarExceptionList)
> If avarExceptionList(lngI) = ctl.Name Then
> bSkip = True
> Exit For
> End If
> Next
> If Not bSkip Then
> If Len(Nz(ctl.SourceObject, vbNullString)) > 0& Then
> ctl.Form.AllowDeletions = Not bLock
> ctl.Form.AllowAdditions = Not bLock
> Call LockBoundControls(ctl.Form, bLock)
> End If
> End If
>
> Case acLabel, acLine, acRectangle, acCommandButton, acTabCtl,
> acPage, acPageBreak, acImage, acObjectFrame
> 'Do nothing
>
> Case Else
> 'Includes acBoundObjectFrame, acCustomControl
> Debug.Print ctl.Name & " not handled on " & conMod & " at " &
> Now()
> End Select
> Next
>
> Exit_Handler:
> Set ctl = Nothing
> Exit Function
>
> Err_Handler:
> Call LogError(Err.Number, Err.Description, conMod &
> "LockBoundControls")
> Resume Exit_Handler
> End Function
> ------------------code ends--------------------
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users -
http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> "MS" <Email@Myemail.com> wrote in message
> news:aZeHd.123527$K7.62925@news-server.bigpond.net.au...[color=green]
>> Unfortunately I've never been able to get it work that way.
>>
>> I'm back to looping through each control, and setting them to "locked"
>> depending on whether the bound checkbox result is true or false - of
>> course the bound check box is left unlocked. This effectly makes the
>> record "uneditable".
>>
>> I've always thought this was a bit messy, and it would be much neater if
>> I could get Me.AllowEdits = False to work if the check box result is
>> false on the Form Current event - but i've never had any joy there. I
>> must be missing something!
>>
>> Cheers!
>>
>>
>> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
>> news:41eccf81$0$16185$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=darkred]
>>> Okay: so the bound check box is appropriate.
>>>
>>> You should find that the Form_Current event works fine.
>>>
>>> --
>>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>>> Tips for Access users -
http://allenbrowne.com/tips.html
>>> Reply to group, rather than allenbrowne at mvps dot org.
>>>
>>> "MS" <Email@Myemail.com> wrote in message
>>> news:EO_Gd.122622$K7.27916@news-server.bigpond.net.au...
>>>> Thanks for the reply Allen. BTW, love your site - truely an unselfish
>>>> act of public service :-)
>>>>
>>>> I should have said originally that the check box is bound to a field in
>>>> the forms table, so that when the user returns to the record, if all
>>>> fields are not yet completed, the record is still "editable" if the
>>>> check box is false. When all fields are complete, the user clicks the
>>>> check box (suposedly) rendering the record "uneditable".
>>>>
>>>> I was expecting the OnCurrent event of the form to "read" the status of
>>>> the check box, and make each record editable or non editable on a
>>>> record to record basis depending on whether the checkbox result was
>>>> true or false.
>>>>
>>>> Any thoughts?
>>>>
>>>> Cheers!
>>>>
>>>>
>>>> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
>>>> news:41ec6b15$0$16183$5a62ac22@per-qv1-newsreader-01.iinet.net.au...
>>>>> You need both the AfterUpdate of the check box, and also the Current
>>>>> event of the form.
>>>>>
>>>>> Simplest way to maintain that is to add this line to Form_Current:
>>>>> Call chkedit_AfterUpdate
>>>>>
>>>>> Other suggestions:
>>>>> 1. Be sure to set the Default Value for the unbound check box, so it
>>>>> does not default to Null.
>>>>>
>>>>> 2. If the record is dirty at the time you click this box, Access will
>>>>> allow the edit to continue until the record is saved. This might
>>>>> confuse the user, so you may want to explicitly save before locking.
>>>>>
>>>>> 3. Did you want to prevent deletions as well?
>>>>>
>>>>> 4. Once you set AllowEdits to No, you won't be able to change even the
>>>>> unbound controls. Therefore it might be better to use a command button
>>>>> than a check box.
>>>>>
>>>>> The result will be something like this:
>>>>>
>>>>> Private Sub cmdLock_Click()
>>>>> Dim bAllow As Boolean
>>>>>
>>>>> If Me.Dirty Then Me.Dirty = False
>>>>> bAllow = Not Me.AllowEdits
>>>>> Me.AllowEdits = bAllow
>>>>> Me.AllowDeletions = bAllow
>>>>> Me.cmdLock.Caption = IIf(bAllow, "&Lock", "Un&lock")
>>>>> End Sub
>>>>>
>>>>> --
>>>>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>>>>> Tips for Access users -
http://allenbrowne.com/tips.html
>>>>> Reply to group, rather than allenbrowne at mvps dot org.
>>>>>
>>>>> "MS" <Email@Myemail.com> wrote in message
>>>>> news:tnWGd.122187$K7.52230@news-server.bigpond.net.au...
>>>>>> Access 97 here.
>>>>>>
>>>>>> I want a simple way to "lock" certain records on a form. Some records
>>>>>> remain "live" until all data is available which happens over time.
>>>>>> When all the fields are complete, I want the record to be able to be
>>>>>> "locked", or intentionally unlocked.
>>>>>>
>>>>>> I thought of having a check box called "chkedit" that can be clicked
>>>>>> when the data entry for the record is complete. I have the following
>>>>>> code in the OnCurrent event for the form...
>>>>>>
>>>>>> If Me.chkedit = True Then
>>>>>> Me.Allowedits = False
>>>>>> Else
>>>>>> Me.Allowedits = True
>>>>>> End if
>>>>>>
>>>>>> This doesn't work. But when I have the same code in the in the
>>>>>> AfterUpdate event for the checkbox, it "locks" the record while the
>>>>>> record is current, but when moving off the record and returning to it
>>>>>> the record is "unlocked" again inspite of the CheckBox being true.
>>>>>>
>>>>>> Ok, obviously there is a better way of "locking" a record. In the
>>>>>> past I have used the CheckBox method, and set each control to Locked
>>>>>> if the checkbox is true on the forms OnCurrent event. This works, but
>>>>>> it is cumbersome.
>>>>>>
>>>>>> Any thoughts?
>>>>>>
>>>>>> Cheers!
>>>
>>>[/color]
>>
>>[/color]
>
>[/color]
