Hi, Lize,.
I would suggest that you create a function that will do the work of locking
or unlocking the desired fields. Here is a starting point:
Just copy the function below and paste it into the VBA code window of your
main form.
Function SetCtrlsStatus(Status as boolean)
'if the control you want to lock or unlock is on the main form
'use the statement below
Me.NameOfControl.Locked = Status
'you would add one of the above statements for each control on the
'main form that you want to lock or unlock
'if the control that want to lock or unlock is on a subform
'use the statement below
Forms![MainFormName]![subformName].Form![NameOfControl].locked = Status
'you would add one of the above statements for each control on a subform
'that you want to lock or unlock
End Function
Next, with your main form in design mode, locate the combo box where your
users can select the "Closed" option. Select it. Right click on it and
select the "Properties" option to displaya the Properties of your form.
Click on the Events tab. Click in the After Update event row. Locate the
down arrow at the end of that row and select the [Event Prodedure] option
from the list. Next, click the button just to the right of the down arrow
(the one with the three dots). The VBA window will be displayed and the
followin should be visble:
Private Sub NameOfYourComboBox_AfterUpdate()
End Sub
Where you see "NameOfYourComboBox_AfterUpdate" in your VBA window you will
see the actual name of your combo box. That is want you should see.
Copy and paste the followin code in between the two existing lines:
If Me.NameOfCombo= "Closed" Then
SetCtrlsStatus True
Else
SetCtrlsStatus False
End If
Next, go back to your main form and click on the small square located in the
upper left corner or your main form to select the main form. The Properites
dialog box will be displaying the properties of your main form. Click on the
Events tab to display the list of events. Click in the On Current event row.
Again, click the down arrow at the right end of that row and select the
[Event Proceedure] option. Now click on the button with the three dots
located to the right of the down arrow. You will be taken to the VBA window
again and this time you will see:
Private Sub Form_Current()
End Sub
Paste the same code in between these two lines of code that you pasted into
the After Update event of the combo box:
If Me.NameOfCombo= "Closed" Then
SetCtrlsStatus True
Else
SetCtrlsStatus False
End If
The code that you now have in the After Update event of the combo box will
cause the controls that you specify in the "SetCtrlsStatus" function to be
locked only if the combo box has "Closed" selected. Other wise the controls
will be unlocked. This action will even work if the user first selects
"Closed" and then changes their mind and then selects another value other
than "Closed"
The code that you now have in the On Current event of your main form will
check for the value of your combo box each time the form is opened or moved
to another record and set the controls specified in the "SetCtrlsStatus"
function to be locked only if the combo box has "Closed" selected.
When you have completed all of these steps, your entire VBA code window
should look like this.
Option Compare Database
Option Explicit
Function SetCtrlsStatus(Status as boolean)
'if the control you want to lock or unlock is on the main form
'use the statement below
Me.NameOfControl.Locked = Status
'you would add one of the above statements for each control on the
'main form that you want to lock or unlock
'if the control that want to lock or unlock is on a subform
'use the statement below
Forms![MainFormName]![subformName].Form![NameOfControl].locked = Status
'you would add one of the above statements for each control on a subform
'that you want to lock or unlock
End Function
Private Sub NameOfYourComboBox_AfterUpdate()
If Me.NameOfCombo= "Closed" Then
SetCtrlsStatus True
Else
SetCtrlsStatus False
End If
End Sub
Private Sub Form_Current()
If Me.NameOfCombo= "Closed" Then
SetCtrlsStatus True
Else
SetCtrlsStatus False
End If
End Sub
Please just remember to modify the SetCtrlsStatus function by adding lines
for each control that you need to lock or unlock and the provide the actual
name of your controls where I have noted that these changes are needed.
Good luck with your project.
