Form Field Auto Disable

[Ivan Howard]

Hi All,

I would really appreciate some help with this issue.

I have a form, fed by one table and tt contains employee information, including a status flag to show if they are currently employed or not.

What I would like to do is when this flag is changed to "Inactive" on a particular record, I would like the rest of the fields on the form to show as disabled/formatted greyed out. Obviously when you move onto another "Active" record, all fields should become enabled again.

Any ideas?

Thanks very much.
Ivan

 

[Andrew Fergus]

Hi Ivan

You will need to create a VBA event to do this for you, as follows: open the form in design view, right click the status flag indicator > 'Properties' > 'Event' tab > 'On Click' event > click the 3 dots to the side > 'Code Builder' > 'Ok' > this will open the VBA screen and create the beginnings of an 'on click' event for your check box.

Enter code similar to the following :

Private Sub MyStatusBox_Click()
    If Me.MyStatusBox.Value = -1 Then
        Me.MyField.Enabled = True
        Me.MyField2.Enabled = True
        'etc.
    Else
        Me.MyField.Enabled = False
        Me.MyField2.Enabled = False
        'etc.
    End If
End Sub

I used a check box called 'MyStatusBox' (yours will have a different name) and I was enabling/disabling 2 fields called 'MyField' and 'MyField2' (again your names will differ). Add more lines to suit and you may want to check the logic is the right way around.

The next thing will be getting the form to update itself when you scroll through different records. Once again get into the design view of the form, select menu option 'Edit' > 'Select Form' > menu option 'View' > 'Properties' > 'Event' tab > ''On Current' event (same deal as before to get into the VBA window with the dots etc) > create a procedure like this:

Private Sub Form_Current()

   Call MyStatusBox_Click

End Sub

This will call the other routine every time you select differing records via the form - this assumes you are only viewing one record at a time. Make sure you use the name of the first routine(as I have).

HTH, Andrew

 

[Ivan Howard]

Hi Andrew,

Thanks very much. This looks like exactly what I need. I will put this in over the weekend.

Have a great weekend.

Take care
Ivan

 

[Ivan Howard]

Thanks Andrew.

The code works very well! I also learnt a lot in the process.

Take care.
Ivan