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?
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamMandenoCommented:
Hi David

What about:
Select Case Nz(Me.cboStatus, "")
    Case "Open", "Closed", ""
        Me.cboStatus.Locked = False
    Case Else
        Me.cboStatus.Locked = True
End Select

Open in new window


You possibly want to call this code from Form_Current as well.

-- Graham Mandeno [Access MVP since 1996]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David BigelowStaff Operations SpecialistAuthor Commented:
@Graham,
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?
0
GrahamMandenoCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David BigelowStaff Operations SpecialistAuthor Commented:
When I input ?Nz(Me.cboStatus, "") and clicked on Create, it said, "sub or function not defined."

Ah, you are right, the combo box has two columns and the bound one is the primary key. Column 1 (0 to 1) contains the text. I modified your answer as such and it's working, now. I forgot about the bound columns portion.

Select Case Nz(Me.cboStatus.Column(1), "")
    Case "Open", "Closed", ""
        Me.cboStatus.Locked = False
    Case Else
        Me.cboStatus.Locked = True
End Select

How would adding the same code to the Form_Current property help?
0
GrahamMandenoCommented:
Hi David

I'm not sure what you mean by " clicked on Create" but, no matter, you have it working :)

I suspected that the combo was probably displaying something other than the bound column.

Adding the code to Form_Current would ensure that the control is locked or unlocked as required when navigating to a new record.  Otherwise, if the control has the focus and is unlocked, and you navigate to the next record which should be locked, I don't think the GotFocus event will fire again, so the control will remain unlocked.

You could put the code in a private function like this:
Private Function SetStatusLock()
Select Case Nz(Me.cboStatus.Column(1), "")
    Case "Open", "Closed", ""
        Me.cboStatus.Locked = False
    Case Else
        Me.cboStatus.Locked = True
End Select
End Function

Open in new window


Then you can Call SetStatusLock from both Form_Current and cboStatus_GotFocus, rather than repeating the same code in multiple places.

Good luck!
0
GrahamMandenoCommented:
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
0
David BigelowStaff Operations SpecialistAuthor Commented:
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.
0
David BigelowStaff Operations SpecialistAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.