Greying Out and Locking a Field, Based on Another - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Greying Out and Locking a Field, Based on Another

Amir Ouranus

4 hours ago

As mentioned in a previous post, our organization has two types of employees:

AA employees: Employed under a contract.
AE employees: Employed without a contract.

On my employee form, I have two fields: Hire Date and Contract Date.

I would like to implement the following functionality:

When an AE account number is entered, the Contract Date field should automatically become disabled (grayed out) and locked to prevent editing.
If the AE account number is removed, the Contract Date field should return to its normal state (white background) and become editable again.

Here's my code that I have right now:

Private Sub UpdateContractDateLock()

    ' If the emplyoee has an AE Account then the Contract Date Field
    '   will be greyed out and will be locked.

    If Me.AAorAE.Value = "AE" Then
        'AE Employees will never need a Contract Date
        'Me.ContractDate.Value = Null
        Me.ContractDate.Locked = True
        Me.ContractDate.BackColor = RGB(191, 191, 191)

    Else
        ' AA Employees will have a Contract Date and may have a Hire Date
        Me.ContractDate.Locked = False
        Me.ContractDate.BackColor = RGB(255, 255, 255)

    End If

The issue is the "Contract Date" field will grey out and lock only after I close the form and reopen.
Any help would be greatly appreciated!

	Matt Hall @Reply 4 hours ago
You might try me.Refresh after the IF block.

	Kevin Robertson @Reply 3 hours ago
Where is UpdateContractDateLock being called?

Donald Blackwell @Reply

3 hours ago

How is your UpdateContractDateLock() procedure being called. For instance, is it in the Open or Load event? That would be why it's only updating when the form opens.

As long as the sub is in the same form, just call it from the AfterUpdate Event of the control named AAorAE. That way, every time you change it, it runs the code.

Also, unless you are going for a specific shade of grey, you could simply use the "Enabled" property. If you set enabled to false and locked to false, the user can't access the control, can't click into it, can't tab into it, it will be removed from the tab order unless/until re-enabled. Access will automatically grey the control and it's label out and prevent access to it except from code.

So, for instance you could do:



Private Sub UpdateContractDateLock()



    ' If the emplyoee has an AE Account then the Contract Date Field

    '   will be greyed out and will be locked.

    

    If Me.AAorAE.Value = "AE" Then

        'AE Employees will never need a Contract Date

        'Me.ContractDate.Value = Null

        Me.ContractDate.Enabled = False

                      

    Else

        ' AA Employees will have a Contract Date and may have a Hire Date

        Me.ContractDate.Enabled = True                

    End If



'..... Any other code in the procedure

End Sub



Private Sub AAorAE_AfterUpdate()



     UpdateContractDateLock



End Sub

Amir Ouranus OP @Reply

3 hours ago

I have an AAorAE field that is hidden and gets it's information from the first 2 characters of the ID field.
The UpdateContractDateLock is being called from AAorAE AfterUpdate.
Here is the code:

Private Sub AAorAE_AfterUpdate()

    UpdateContractDateLock

End Sub

--------------------------------------------------------------------

Private Sub Form_Current()

    UpdateContractDateLock

End Sub

-------------------------------------------------------------------

Private Sub UpdateContractDateLock()

    ' If the emplyoee has an AE Account then the Contract Date Field
    '   will be greyed out and will be locked.

    If Me.AAorAE.Value = "AE" Then
        'AE Employees will never need a Contract Date
        'Me.ContractDate.Value = Null
        Me.ContractDate.Locked = True
        Me.ContractDate.BackColor = RGB(191, 191, 191)

    Else
        ' AA Employees will have a Contract Date and may have a Hire Date
        Me.ContractDate.Locked = False
        Me.ContractDate.BackColor = RGB(255, 255, 255)

    End If



End Sub

Donald Blackwell @Reply

3 hours ago

Ahh, since it is a hidden field, the AfterUpdate event probably isn't firing when it gets updated from code....

So then you'd probably need to step back to how the AAorAE field is getting its data. For instance, is its control source something like =Left(IDField,2) or is there some other mechanism that changes its value?

Whatever sets its value should then call the UpdateContractDateLock procedure once it has updated the field.

	Kevin Robertson @Reply 3 hours ago
If AAorAE is hidden you can't type into it so its After Update event is not going to run. Call `UpdateContractDateLock` from the event that sets the value in the AAorAE field.

	Amir Ouranus OP @Reply 3 hours ago
Hi Donald, It is the =Left(IDField, 2) Hi Kevin, It is hidden. As you said: I'll call UpdateContractDateLock from the event that sets teh value in the AAorAE field.

	John Davy @Reply 3 hours ago
Hi Amir, Could you simply hide the button ? John

	Amir Ouranus OP @Reply 2 hours ago
Ok, just out of curiosity I brought in the AAorAE field in the form, and the code still doesn't work.

	Amir Ouranus OP @Reply 2 hours ago
Hi John, Yes, but hidden or not it still doesn't work.

Donald Blackwell @Reply

2 hours ago

So, the control source of AAorAE is =Left(IDField,2) so it's not an updatable field in any circumstance.

If you're trying to run it in the AfterUpdate event of the IDField (whatever that is) and it's not working, it could be that the the AAorAE field hasn't updated yet.

So you might try something like:

Private Sub IDField_AfterUpdate()

AAorAE.Requery
UpdateContractDateLock

End Sub

Other options instead of just requerying the control might be to use Matt's Me.Refresh before the calling the procedure. But I'm betting the reason you have to close and open the form is that AAorAE hasn't updated when you're calling it.

	Amir Ouranus OP @Reply 103 minutes ago
It still doesn't work. Is there a better way to do this? I just don't want the Contract Date to be available if it is an AE employee.

Donald Blackwell @Reply

87 minutes ago

You could modify the UpdateContractDateLock procedure to just extract the AA/AE from the IDField itself:

Private Sub UpdateContractDateLock()



    Dim AAAE as string

    AAAE = Left(IDField,2)



    ' If the employee has an AE Account then the Contract Date Field

    '   will be greyed out and will be locked.

    

    With ContractDate

        If AAAE = "AE" Then

            'AE Employees will never need a Contract Date

            '.Value = Null

            .Locked = True

            .BackColor = RGB(191, 191, 191)

        Else

            ' AA Employees will have a Contract Date and may have a Hire Date

            .Locked = False

            .BackColor = RGB(255, 255, 255)

        End If

    End With

    

End Sub



Private Sub IDField_AfterUpdate()



    UpdateContractDateLock



End Sub

Just remember to replace "IDField" everywhere with the actual name of the control holding the ID. Also, double check the AfterUpdate Event property in your form for that field and make sure the it shows [Event Procedure], otherwise it's never calling your code. Has happened to me before where I add something in the VB Editor and forget to make sure it links in the properties. If that's missing, you can either use the drop down to select it or double click the field until it appears.

Next Unseen

New Feature: Comment Live View