Need to lock all fields except the search box (1 Viewer)

[Gregory Peck]

Hello. I am not an access expert but I have been able to make a nice little access form that shows our products details, prices, sizes etc. I had it set that all the prices and other important data was locked so that no one could accidentally change the data. That worked great but if you wanted to make changes you had to go into the table and edit there which is a pain. So I found some code on google which sets Me.AllowEdits = false until I click an edit button then it allows edits on the whole record. This is great BUT now it has locked my search field as well so now I can not search unless I click edit which opens the whole record to editing thus defeating the whole reason for locking things.



So is there a way to override the Me.AllowEdits = false for one text box? Or a better way to lock all the record except the search field and be able to click a button to allow editing the record.



Thank you in advance for any help.

 

[theDBguy]

Hi. Welcome to the forum. Are you referring to the search box next to the navigation buttons?

 

[Gregory Peck]

Hi. Welcome to the forum. Are you referring to the search box next to the navigation buttons?

I had set this form up a few years back and made a search field and search button. It is basically a filter. So you type the item number in the text box the click the search button and it filters out everything that does not have the text in it leaving you with what your looking for.



This is what I see in the apply filter area for the search button.

Filter name "nothing listed here"

Where condition = [ItemNum] Like "*" & [Forms]![products1]![Text634] & "*" Or [Name] Like "*" & [Forms]![products1]![Text634] & "*" Or [Keywords] Like "*" & [Forms]![products1]![Text634] & "*"

 

[theDBguy]

I had set this form up a few years back and made a search field and search button. It is basically a filter. So you type the item number in the text box the click the search button and it filters out everything that does not have the text in it leaving you with what your looking for.

This is what I see in the apply filter area for the search button.

Filter name "nothing listed here"

Where condition = [ItemNum] Like "*" & [Forms]![products1]![Text634] & "*" Or [Name] Like "*" & [Forms]![products1]![Text634] & "*" Or [Keywords] Like "*" & [Forms]![products1]![Text634] & "*"

Okay, if you're not talking about the search box next to the navigation button but your own textbox, then one workaround you could try is to put something like Me.AllowEdits=True in its GotFocus event and then put Me.AllowEdits=False in its LostFocus event. Hope it helps...

 

[Gregory Peck]

Thank you for the idea. I added that but now any field I click on I can edit. Here is the code that comes up when I click on the text box for my search then click the ... next to "on Got Focus" maybe I am not doing it correctly.



Option Compare Database

Private Sub Command625_Enter()

End Sub

Private Sub Text626_Enter()

End Sub

Private Sub Command888_Click()
DoCmd.GoToRecord , , acPrevious
End Sub

Private Sub Check892_Click()

End Sub

Private Sub Command895_Click()
Me.AllowEdits = True
Me.Command895.Caption = "Editing"
End Sub



Private Sub Form_Click()

End Sub

Private Sub Form_Current()
Me.AllowEdits = False
Me.Command895.Caption = "Edit"
End Sub

Private Sub FormHeader_Click()

End Sub

Private Sub Text630_Click()

End Sub

Private Sub Text634_AfterUpdate()

End Sub

Private Sub Text634_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Text634_Click()

End Sub

Private Sub Text634_Enter()

End Sub


Private Sub Text634_GotFocus()
Me.AllowEdits = True
End Sub

 

[Gregory Peck]

I forgot to put the lost focus part in. Looks like it is working now.



Thank you so much for your help. Been stuck on this for a day. Much appreciated

 

[theDBguy]

I forgot to put the lost focus part in. Looks like it is working now.

Thank you so much for your help. Been stuck on this for a day. Much appreciated

Hi. Congratulations! Glad to hear you got it to work. Good luck with your project.

 

[Gregory Peck]

So I had one more field that I wanted to be able to enter a number into just like the search box. I added the same code for this field as I did for the search field and it works as far as the "on focus Me.AllowEdits = True" but the lost focus part is not working. I.E. I can click into the box and add a number but then the whole form is open for editing until I go to a new record. What am I doing wrong?



In the below code Text634 is the search box that does work. Ship_Quantity is the new box I am working with.





Option Compare Database

Private Sub Command625_Enter()

End Sub

Private Sub Text626_Enter()

End Sub

Private Sub Command888_Click()
DoCmd.GoToRecord , , acPrevious
End Sub

Private Sub Check892_Click()

End Sub

Private Sub Command895_Click()
Me.AllowEdits = True
Me.Command895.Caption = "Editing"
End Sub

Private Sub Form_Current()
Me.AllowEdits = False
Me.Command895.Caption = "Edit"
End Sub

Private Sub Ship_Quantity_GotFocus()
Me.AllowEdits = True
End Sub

Private Sub Ship_Quantity_LostFocus()
Me.AllowEdits = False
End Sub

Private Sub Text634_GotFocus()
Me.AllowEdits = True
End Sub

Private Sub Text634_LostFocus()
Me.AllowEdits = False
End Sub

 

[Pat Hartman]

One very simple way to do this is to make an unbound form with the search fields and a bound subform that can have its AllowEdits property toggled at will. You can use a button on the main form to toggle the AllowEdits property of the subform or put the button on the subform itself.

 

[missinglinq]

In doing this kind of thing...once you set AllowEdits to True, it'll stay True for that Record...even if you explicitly try to set AllowEdits to False, again, until you exit the Record.

The only reasonable way to do this is, to me, using the single Form, is to Lock all Controls except the Controls you want to use (just like your thread title suggested!) instead of using AllowEdits.

Linq ;0)>

 

[Pat Hartman]

This is the code I use when I want everything on the same form. Since my apps have security, I check it in the form's current event and then lock or unlock the form based on what authorization the user has. In your case, use the call to lock or unlock from wherever you want to control the locking.

To make this work, I use the tag property of each control. The tag property needs to be set ONLY for exceptions. So, on a form where normally everything is going to be locked but you want the search fields to be unlocked, mark the search fields as "NoLock". In a form where normally everything is going to be unlocked but you want some fields to always be locked, then tag those fields with "Lock".

Private Sub Form_Current()
    If Forms!frmLogin!txtLevel >= 8 Then
        Call LockControls(Me, False)        'unlock
    Else
        Call LockControls(Me, True)         'lock
    End If
End Sub

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

 

[Sodslaw]

This is the code I use when I want everything on the same form. Since my apps have security, I check it in the form's current event and then lock or unlock the form based on what authorization the user has. In your case, use the call to lock or unlock from wherever you want to control the locking.

To make this work, I use the tag property of each control. The tag property needs to be set ONLY for exceptions. So, on a form where normally everything is going to be locked but you want the search fields to be unlocked, mark the search fields as "NoLock". In a form where normally everything is going to be unlocked but you want some fields to always be locked, then tag those fields with "Lock".

Private Sub Form_Current()
    If Forms!frmLogin!txtLevel >= 8 Then
        Call LockControls(Me, False)        'unlock
    Else
        Call LockControls(Me, True)         'lock
    End If
End Sub

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

Hi @Pat Hartman i have been looking at your code above and would like to use it as it works well for my main form.
How would include subforms to this code?

thanks in advance. Sods.

 

[Pat Hartman]

You need to call the code from each form. So both the main form and the subform, need the code in the Current event. The code that does the actual locking needs to live in a standard module, NOT in any form's class module.

 

[Uncle Gizmo]

I demonstrate a slightly different version here:-

Lock ALL the Controls on a Form With "NOT"

Lock ALL the Controls on a Form - Nifty Access In this video I demonstrate how a True/False "If Statement" can be simplified with the word "NOT"... The "NOT Operator" can reverse the logic in your function. If you precede a value which Returns "TRUE" with the "NOT OPERATOR" then it will...

www.access-programmers.co.uk

 

[Pat Hartman]

To clarify,

The code I posted there shows how to use a user's security level to lock/unlock the controls. It differs from Uncle's solution in that it allows you to use the tag property of a control to permanently set the status of a control to LOCK or NoLOCK. Note that ONLY if you want the control to work independently of the user's security do you need to use the tag property.

For example, if a form has unbound search boxes or combos that you want to remain functional, you would set their tag property to NoLock so that the code will keep them unlocked regardless of whether or not the user's security would prevent him from being able to update the form. And sometimes, you have controls that should be locked regardless of the user's security such as the PK of the record. Once assigned, it can never be changed. PERIOD so you would not want the control to be unlocked.

 

[Sodslaw]

@Pat Hartman @Uncle Gizmo
Thanks guys, i just got around to reading your replies and boom! (i think i was having a blond moment)
Added the private sub to both subforms and BOOM!! its working exactly how i imagined.

thanks again!

 

[Pat Hartman]

Apparently, I didn't make it clear. The

Public Sub LockControls(frm As Form, bLock As Boolean)

belongs in a STANDARD MODULE. It does NOT go into the class module of a form. If you had to place it in each form module, you wouldn't need to pass in the form object. You would just use Me. to reference the controls. The point of putting it into a standard module is to make it available to ANY form without duplicating the code.

You do need to call the procedure from each form where you want to use it. That is the code snippet from the Current event.

 

[isladogs]

Another variation on this idea:

Set Controls

This demonstrates a way of setting the state of a group of controls at the same time using the controls' Tag property

www.isladogs.co.uk

 

[Sodslaw]

Another variation on this idea:

Set Controls

This demonstrates a way of setting the state of a group of controls at the same time using the controls' Tag property

www.isladogs.co.uk

Thanks for sharing! You have a great site BTW great job @isladogs ! I have now bookmarked your site.

 

[isladogs]

Thanks @Sodslaw - love the user name BTW

My new Isladogs website is currently in the process of being updated. Now about 2/3 done.....
I also have a YouTube channel if you are interested...

https://www.youtube.com/channel/UCLqpE3bfxr1gLc_OOM1ToUA/videos