Need to lock all fields except the search box

[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

 

[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)>

 

[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.

 

[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

 

[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!

 

[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

 

[Uncle Gizmo]

Thanks @Sodslaw - love the user name BTW

Ha! Hadn't spotted that!

 

[Uncle Gizmo]

If a subform has the focus, ActiveForm refers to the main form. If no form or subform has the focus when you use the ActiveForm property, an error occurs.

This is a problem I solved using my Call/Called Class Module:-

CallCalled Class Module - Nifty Access

CallCalled Class Module I will be adding information about my "CallCalled" Class Module here, as it becomes available. The most recent example of it is on Access World Forums here:-VBA to reuse data entry popup form Class Module - Overview Class Module - Overview Video 1 (01:09) You can see how...

www.niftyaccess.com

(VBA Code at Bottom of the Web Page) ...

When you are handling controls with "Screen.ActiveControl" it doesn't matter if the control is on the main form or the subform you still maintain the link between your code and the original object. However I wanted to trigger code in the subform, code to simulate the after update event of the "Screen.ActiveControl" and as you have indicated Pat, the subform doesn't actually exist as a separate form, you only get access to the main form.

This is the code that finds the subform:-

Set mfrmCallingForm = fGetParentForm(ctrlActiveControl)     'Store the Form the Control is on, (not always/necessarily the active Form).

 

[avincent61]

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

This was absolutely perfect for my application! I only used the "'lock" portion of the Private Sub. In the Public Sub, I used Case"" for the fields to remain unlocked so I didn't have to apply tags to all of my controls (there's a lot). Lastly I commented out the command button portion for later use. You rock!

 

[avincent61]

@avincent61 Although likes are always welcome, they are not very useful in identifying whether a post solved a problem since there is no way to distinguish between a "thanks for trying" from a "that clarified the code for me and I'll fix my app to use the code correctly".

My apologies...To clarify per your previous response: All of my form fields are unlocked by default. OnCurrent, my code loops through the form fields and locks all of them, then unlocks one specific field by name, then based on higher level user security level, unlocks the fields that have the tag "QCLock" (referenced in the module code), then, based on highest level of user security, allows edits/deletions etc., which unlocks any fields that are still locked. At least that's how I was able to achieve my particular goal. I have multiple user security levels to get through, which is why I start by locking everything, then unlocking based on the user. I do have error handling, but I have more code after the one below, so I didn't include it here.

Dim intnewrec As Integer
 
    intnewrec = Form.NewRecord
        If intnewrec = False And IsNull(Me.CreatedRecord) Then
            Me.CreatedRecord.Value = Forms!frmlogin!txtLogin
'            MsgBox "You're in a new record." This was to test the code
        Else
'            MsgBox "This is NOT a new record" This was to test the code
        End If
        
        
    Dim LoginType As Integer
    Dim User As String
    Dim ctrl As Control
        
    '   set user = a value of the txtLogin textbox on the Navigation Form
    User = Forms![frmlogin]!txtLogin
    '   Assign the UserSecurity from the user login to LoginType
    LoginType = DLookup("UserSecurity", "tblPersonnel", "UserLogin = '" & User & "'")
    
        '   Lock all controls if user login type is less than allowed level
        If LoginType < 5 Or LoginType = 10 Or LoginType = 11 Or LoginType = 8 Then
            '   Loop through all controls on form and lock them
            On Error Resume Next
            For Each ctrl In Me.Controls
                ctrl.Locked = True
            Next ctrl
            On Error GoTo 0
        ElseIf LoginType = 5 Or LoginType = 7 Or LoginType = 9 Then
            '   Lock controls meant only for Administrator
            Call LockControlsQC(Me, True) 'lock
        Else
            Me.AllowDeletions = True
            Me.AllowEdits = True
            Me.AllowAdditions = True
        End If

        '  Unlock one specific control
        Me.SIR_Status.Locked = False

 

[avincent61]

I'm glad you have a solution. When you have to manage sets of controls based on login type, the solution is more complex.

I would rethink the LogInType concept so that each type belongs to a group. That means a table that defines all valid values of LogInType and for each, adds a groupID. That way, instead of hardcoding multiple type values in multiple forms, you can make a single procedure that you pass in a group in addition to the form reference. Over time, this will save you a lot of coding and also minimize future code changes if you end up having to add more types. If you notice, the code I posted is held in a standard module therefore it does NOT exist in every single form as your code needs to. If you have 50 forms, which isn't a lot, you have to repeat the code 50 times. If you have to change it, you have to change it in 50 places. In my app, I have to change the code in one, single module. HUGE difference.

Considering I use public codes in my modules that check users security level, I'm disappointed that I didn't think to go the route you're suggesting for the forms themselves. The user security level isn't the same for all of my forms, but there are groups of forms that could use the same code. Thanks for this.