How do I lock a form field based on its value?
I'm locking a form field based on its value. I want the field to be unlocked when it is empty and on the words Open and Closed. Any other value should be locked. This is because there are other statuses that are chosen in a linked database that I don't want changed once they are chosen.
Private Sub cboStatus_GotFocus()
If (Me.cboStatus = "Closed") Then
Me.cboStatus.Locked = False
ElseIf (Me.cboStatus = "Open") Then
Me.cboStatus.Locked = False
ElseIf IsEmpty(Me.cboStatus) Then
Me.cboStatus.Locked = False
End If
End Sub
Besides the need for a lock when it doesn't meet the above criteria, it could probably be written cleaner, as well. Currently the field is not set to default lock. Would that make for cleaner code if it were?
Private Sub cboStatus_GotFocus()
If (Me.cboStatus = "Closed") Then
Me.cboStatus.Locked = False
ElseIf (Me.cboStatus = "Open") Then
Me.cboStatus.Locked = False
ElseIf IsEmpty(Me.cboStatus) Then
Me.cboStatus.Locked = False
End If
End Sub
Besides the need for a lock when it doesn't meet the above criteria, it could probably be written cleaner, as well. Currently the field is not set to default lock. Would that make for cleaner code if it were?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi David
Try setting a breakpoint on the "Select Case" line when the status is "Open" or "Closed" and when the code stops on that line, type this in the Immediate window:
?Nz(Me.cboStatus, "")
Does your combo box have multiple columns and, if so, is the bound column the one that contains this text?
PS: Nz() isn't interfering with the Locked property. It is simply a function that converts a null value into an empty string so that it can be compared.
Try setting a breakpoint on the "Select Case" line when the status is "Open" or "Closed" and when the code stops on that line, type this in the Immediate window:
?Nz(Me.cboStatus, "")
Does your combo box have multiple columns and, if so, is the bound column the one that contains this text?
PS: Nz() isn't interfering with the Locked property. It is simply a function that converts a null value into an empty string so that it can be compared.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi David
I'm glad it's all working. I think you might have inadvertently requested that the question be closed (which leaves it marked unsolved) instead of marking it solved.
By the way, I wasn't talking about navigating to a new form, but to a new record in the same form. I think if you do so while the status combo has the focus, then cboStatus_GotFocus will not be executed a second time, so the lock state will not reflect the requirements of the new record.
Best wishes,
Graham
I'm glad it's all working. I think you might have inadvertently requested that the question be closed (which leaves it marked unsolved) instead of marking it solved.
By the way, I wasn't talking about navigating to a new form, but to a new record in the same form. I think if you do so while the status combo has the focus, then cboStatus_GotFocus will not be executed a second time, so the lock state will not reflect the requirements of the new record.
Best wishes,
Graham
ASKER
I'm not sure why it went to Close Request Pending when I clicked on Accept Multiple Solutions. It does show that you have 500 points coming, 250 per two comments. I suspect it is "close pending" because I marked it in a way that seems to indicate multiple experts receiving credit and it gives experts a chance to challenge it if they feel errors were made in the credits.
You're right, I said that wrong about the status field. When I go to a new record, the status parameters are working.
You're right, I said that wrong about the status field. When I go to a new record, the status parameters are working.
ASKER
I made the select case a private function and then called it from the status combo box. Navigating to a new form doesn't seem to nullify the functionality of the select case parameters on the status combo box, so I didn't put it on the Form_Current properties, though I have the option now to call it if users find a need for it.
ASKER
Hmm. That's unlocking the field when it's empty, but locking everything else, including Open and Closed. I did concurrently put the code on the field for GotFocus and for Form_Current. Is the Nz with "" interfering with the lock?