Lock TextBox if another TextBox's value is "" or Null

[ryanvb83]

I'm having some trouble with a particular form. I would like to lock a textbox until 7 other textboxes have a value in them. The other 7 textboxes are a mixture of text fields, double numers, currency, and dates. The textbox that I would like to keep locked (until conditions are met) is a date field. This is what I have so far. Keep in mind that this particular vba code only looks at one of the seven fields.
[code=vb]
Private Sub ReviewComplete_ Exit(Cancel As Integer)
If Not IsNull(ReviewCo mplete.Value) And Borrower.Value = "" Then
MsgBox "no borrower", vbOKOnly
Else
End If
End Sub
[/code]

This code does not seem to be working as it does not produce a message box when I tab out or click out of the ReviewComplete textbox. Any help is very much appreciated. Thank you!

Ryan E.

[ADezii]

I'm having some trouble with a particular form. I would like to lock a textbox until 7 other textboxes have a value in them. The other 7 textboxes are a mixture of text fields, double numers, currency, and dates. The textbox that I would like to keep locked (until conditions are met) is a date field. This is what I have so far. Keep in mind that this particular vba code only looks at one of the seven fields.

Private Sub ReviewComplete_ Exit(Cancel As Integer)
If Not IsNull(ReviewCo mplete.Value) And Borrower.Value = "" Then
MsgBox "no borrower", vbOKOnly
Else
End If
End Sub

This code does not seem to be working as it does not produce a message box when I tab out or click out of the ReviewComplete textbox. Any help is very much appreciated. Thank you!

Ryan E.

1. Set the Locked Property of the Text Box to be Locked/Unlocked to Yes. For demo purposes I'll name it txtLocked.
2. Create a Private Sub-Routione in your Form and name it SetStatusOfText Box.
   [CODE=vb]Private Sub SetStatusOfText Box()
   If IsNull(Me![txtBox1]) Or IsNull(Me![txtBox2]) Or IsNull(Me![txtBox3]) Or IsNull(Me![txtBox4]) Or IsNull(Me![txtBox5]) Or IsNull(Me![txtBox6]) Or IsNull(Me![txtBox7]) Then
   Me![txtLocked].Locked = True
   Else
   Me![txtLocked].Locked = False
   End If
   End Sub[/CODE]
3. In the AfterUpdate() Event of each of the 7 Text Boxes, place the following single line of code:
   [CODE=vb]
   Private Sub txtBox2_AfterUp date()
   Call SetStatusOfText Box
   End Sub[/CODE]
4. Replace the Text Box Names and the Sub-Routine name if you like.
5. Any questions, feel free to ask.
6. There is an easier Method but I'm assuming that there are additional Text Boxes on the Form beside the 7 that need to contain values and the 1 which will be Locked/Unlocked.

[ryanvb83]

Perfect!!!! Thank you sooo much. I'll be able to use the same logic to solve another issue I was having!

[jambonjamasb]

Would that code also work to hide a textbox rather than lock it? I have one text box that when another is null I want it to remain hidden.

[ryanvb83]

I pretty sure it would. Just replace .locked with .hidden

[Minion]

I pretty sure it would. Just replace .locked with .hidden

Actually, I believe the property you are looking for is visible not hidden. The code to hide an element would look like:
[code=vb]
yourTextBox.vis ible = False
[/code]

That should work to hide the element using the previously posted code.

- Minion -

[ADezii]

Perfect!!!! Thank you sooo much. I'll be able to use the same logic to solve another issue I was having!

You are quite welcome.

[ADezii]

Would that code also work to hide a textbox rather than lock it? I have one text box that when another is null I want it to remain hidden.

Would that code also work to hide a textbox rather than lock it?

Yes, just replace .Locked with .Visible.

[jambonjamasb]

Do I make the change in the actual table or on my report.

The field change_withdraw n has a tick box and when this is ticked I want the reason_for withdrawal box to appear which has a drop down selection?

I have looked at the fields properties and there is a yes no choice do i need to select this?

Thanks for your help in advance.

[jambonjamasb]

Do I make the change in the actual table or on my report.

The field change_withdraw n has a tick box and when this is ticked I want the reason_for withdrawal box to appear which has a drop down selection?

I have looked at the fields properties and there is a yes no choice do i need to select this?

Thanks for your help in advance.

Sorry just read the bit which stated do a private sub routine on the form.

[jambonjamasb]

Sorry just read the bit which stated do a private sub routine on the form.

On form POL_Actions_Ent er I clicked on properties of combo box Reason_For_With drawal

I then selected Events and before updates line. in there I typed the following.


Private Sub POL_Actions_Ent er()
If IsNull(Me![Change_Withdraw n])
Me![Reason_for_With drawal].Visible = False
Else
Me![Reason_for_With drawal].Visible = True
End If
End Sub

I assumed if change withdrawn is null then reason for withdrawal isnt visible?

For some reason the code wont step forward, but I am not knowledgable enough to understand why. Also not sure if this is the cause, but my whole sub form has vanished from my main form. LOL what do they say, "If it ain't broke, fiddle with it!"

Thanks in advance.

[jambonjamasb]

Right.

I have spotted that I missed a "then" and had a couple of brackets that weren't needed. So my code now reads.


Private Sub POL_Actions_Ent er()
If IsNull(Me!Chang e_Withdrawn) Then
Me!Reason_for_W ithdrawal.Visib le = False
Else
Me!Reason_for_W ithdrawal.Visib le = True
End If
End Sub

End Sub

While this looks right to me it still doesnt work. Am I right that this is an event procedure in properties? Also since I entered this code my subform has vanished. POL_Actions is the name of the form. This is the first database I have properly built so apologies for my ineptness.

[ADezii]

Right.

I have spotted that I missed a "then" and had a couple of brackets that weren't needed. So my code now reads.


Private Sub POL_Actions_Ent er()
If IsNull(Me!Chang e_Withdrawn) Then
Me!Reason_for_W ithdrawal.Visib le = False
Else
Me!Reason_for_W ithdrawal.Visib le = True
End If
End Sub

End Sub

While this looks right to me it still doesnt work. Am I right that this is an event procedure in properties? Also since I entered this code my subform has vanished. POL_Actions is the name of the form. This is the first database I have properly built so apologies for my ineptness.

Place your code in the AfterUpdate() Event of Change_Withdraw n.

[Minion]

Just a little tip for posting on this board. When you're posting any type of code it really helps to put in the brackets ([ code=vb ] <<code>> [ /code ]) (without spaces inside brackets) this will format the code. If you have sql or other language just change the vb to the proper format.

For instance you posted..

Private Sub POL_Actions_Ent er()
If IsNull(Me!Chang e_Withdrawn) Then
Me!Reason_for_W ithdrawal.Visib le = False
Else
Me!Reason_for_W ithdrawal.Visib le = True
End If
End Sub

If you place this within the code tags as described it looks like.
[code=vb]
Private Sub POL_Actions_Ent er()
If IsNull(Me!Chang e_Withdrawn) Then
Me!Reason_for_W ithdrawal.Visib le = False
Else
Me!Reason_for_W ithdrawal.Visib le = True
End If
End Sub
[/code]

Just a little hint to make longer bits of code easier to read.

Thanks.

- Minion -

Right.

I have spotted that I missed a "then" and had a couple of brackets that weren't needed. So my code now reads.


Private Sub POL_Actions_Ent er()
If IsNull(Me!Chang e_Withdrawn) Then
Me!Reason_for_W ithdrawal.Visib le = False
Else
Me!Reason_for_W ithdrawal.Visib le = True
End If
End Sub

End Sub

While this looks right to me it still doesnt work. Am I right that this is an event procedure in properties? Also since I entered this code my subform has vanished. POL_Actions is the name of the form. This is the first database I have properly built so apologies for my ineptness.

[Minion]

Just thought of something when I was reading over the thread again. If you're looking to use the check to activate another control you may wish to use .Enabled instead. This will still show the control on the form so the user will know there is one, but will gray it out until the first control code is activated. So your code would look like.

[code=vb]
Private Sub POL_Actions_Aft er_Update()
If IsNull(Me!Chang e_Withdrawn) Then
Me!Reason_for_W ithdrawal.Enabl ed = False
Else
Me!Reason_for_W ithdrawal.Enabl ed = True
End If
End Sub
[/code]