Greying Out Fields - Access Developers


	Back to Access Developers Comments List

Greying Out Fields

Amir Ouranus

16 hours ago

Hello,

I'm building a database for work and have the following fields on one of my forms:

IDemp
ContractType
ContractDate

We have two types of employees:

Employees with AE user IDs
Employees with AA user IDs

Employees with AE user IDs are regular hourly or salaried employees and do not have contracts. Employees with AA user IDs are contract employees.

I have already written code that checks the employee ID. If an AE ID is entered, the ContractType and ContractDate fields are automatically disabled (grayed out and locked), preventing users from entering data. This portion is working correctly.

My issue involves contract employees (AA IDs). These employees can have one of two contract types: Contract A or Contract B. I have created a combo box that allows the user to select the ContractType.

What I would like to happen is the following:

If Contract A is selected, the ContractDate field should be grayed out and locked because that field is not applicable to Contract A employees.
If Contract B is selected, the ContractDate field should remain available for data entry.

I have written code that successfully disables the ContractDate field when Contract A is selected. However, the behavior only works while I am actively in the form. If I close the form and later reopen an existing record with Contract A selected, the ContractDate field is no longer disabled.

Has anyone encountered this issue before or can suggest the best way to ensure the field remains disabled when reopening the form and navigating between records?

As always, any help would be greatly appreciated. If it would be helpful to review the code, I would be happy to post it.

Thank you.

Amir Ouranus OP @Reply

16 hours ago

Here's the Code:

Private Sub UpdateContractDateState()

    Select Case Nz(Me.ContractTypeComboBox, 0)
        Case 2, 3, 16
            ' Unlock and show as normal
            Me.ContractDate.Locked = False
            Me.ContractDate.BackColor = RGB(255, 255, 255)
        Case Else
            ' Lock and grey out
            Me.ContractDate.Locked = True
            Me.ContractDate.BackColor = RGB(191, 191, 191)
    End Select


End Sub

	Amir Ouranus OP @Reply 16 hours ago
One last thing: I have tried that code on every type of event: On Load On Current On Open However it does not stay greyed out; only when you change the Combo Box to Contract B.

Richard Rost @Reply

15 hours ago

Amir, you're on the right track putting this in one reusable procedure. The code you've posted defines that procedure, but doesn't show what calls it.

Since you've already tried On Current, I'd check the event wiring next. In the form's property sheet, On Current should be [Event Procedure]. Open it with the build button and make sure Form_Current calls UpdateContractDateState. Call that same routine from the combo box's After Update event. Current handles opening the form and moving between records, while After Update handles changing the selection.

Put a breakpoint on the Select Case line, reopen the form, and move between records. If it doesn't stop there, the routine isn't being called. If it does, check the combo's actual value and which Case runs. Your code allows editing for 2, 3, and 16, so confirm those are the stored values you intend, not just the displayed descriptions.

Also check whether your employee-ID routine subsequently unlocks or recolors ContractDate. Keep both rules together so the employee-type check and contract-type check don't undo each other. If it's still misbehaving, post your Form_Current and combo After Update procedures, plus the employee-ID code that changes ContractDate. That should help us narrow it down.

	Donald Blackwell @Reply 15 hours ago
Odd, If it is a form bound to data, calling UpdateContractDateState in the Form_Current() event should work. I would try testing the value of the combo box in the current event just to make sure it's not a timing thing (i.e. the data hasn't fully refreshed yet when you call your sub). For example: `Private Sub Form_Current() MsgBox ContractTypeComboBox UpdateContractDateState End Sub`

Amir Ouranus OP @Reply

14 hours ago

A few points for clarification:

The code is working as intended initially. When I select Contract A, the ContractDate field becomes grayed out and locked. When I select Contract B, the field changes back to white and becomes editable.

The issue occurs after navigating away from the record and then returning to it (moving forward or backward between records). The field does not retain its expected state. For example, if the record is set to Contract A, the ContractDate field should remain grayed out and locked. However, after leaving the record and returning, the field is no longer grayed out and can be edited.

One additional clarification regarding my second point: the values 2, 3, and 16 represent different Contract B options. I simplified my original explanation to keep it shorter, but these numbers are actually the IDs for the various Contract B selections within the combo box.

Amir Ouranus OP @Reply

13 hours ago

Very interesting!

I originally placed the code in the Form - On Current event, but it did not work. Looking back, I realized the code was positioned before another routine that locks the contract fields (ContractType and ContractDate). That locking code was part of the AA and AE logic I mentioned earlier.

After moving the UpdateContractDateState procedure to run after the LockContractFields code, everything worked perfectly.

Donald Blackwell @Reply

13 hours ago

It's important to keep in mind that without seeing your form's properties, and especially form even properties for the form and combo box(es) and any other controls that may be calling your procedure, we're trying to see what's not working while wearing a blindfold.

That's why I suggested putting the msgbox in the on current event of the form so it would verify if the form recognizes a value in the combo box. That is also why Richard suggested verifying that the form current event shows [Event Procedure] in the events tab of the form when you were trying to call your code from there. It can happen easily, something gets changed in code or in the form and that part disappears in the property window.

And, one question from outer space: Is your combo box a regular combo box or are you making use of the new cascading combo box features rolled out to 365 users?

	Richard Rost @Reply 12 hours ago
Glad you tracked it down, Amir. The order of those calls was the missing piece. Thanks for posting the fix, and thanks, Donald, for helping troubleshoot. When two routines change the same control, checking which one runs last is a useful debugging step.

	Amir Ouranus OP @Reply 12 hours ago
It's a regular Combo Box.

Next Unseen