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.

Private Sub ReviewComplete_Exit(Cancel As Integer)
If Not IsNull(ReviewComplete.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.

[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(ReviewComplete.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 SetStatusOfTextBox.
   

   Private Sub SetStatusOfTextBox()
   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

3. In the AfterUpdate() Event of each of the 7 Text Boxes, place the following single line of code:
   

   Private Sub txtBox2_AfterUpdate()
     Call SetStatusOfTextBox
   End Sub

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:

yourTextBox.visible = False
 

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_withdrawn 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_withdrawn 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_Enter I clicked on properties of combo box Reason_For_Withdrawal

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


Private Sub POL_Actions_Enter()
If IsNull(Me![Change_Withdrawn])
Me![Reason_for_Withdrawal].Visible = False
Else
Me![Reason_for_Withdrawal].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_Enter()
If IsNull(Me!Change_Withdrawn) Then
Me!Reason_for_Withdrawal.Visible = False
Else
Me!Reason_for_Withdrawal.Visible = 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_Enter()
If IsNull(Me!Change_Withdrawn) Then
Me!Reason_for_Withdrawal.Visible = False
Else
Me!Reason_for_Withdrawal.Visible = 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_Withdrawn.

[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_Enter()
If IsNull(Me!Change_Withdrawn) Then
Me!Reason_for_Withdrawal.Visible = False
Else
Me!Reason_for_Withdrawal.Visible = True
End If
End Sub

If you place this within the code tags as described it looks like.

Private Sub POL_Actions_Enter()
If IsNull(Me!Change_Withdrawn) Then
  Me!Reason_for_Withdrawal.Visible = False
Else
  Me!Reason_for_Withdrawal.Visible = True
End If
End Sub
 

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_Enter()
If IsNull(Me!Change_Withdrawn) Then
Me!Reason_for_Withdrawal.Visible = False
Else
Me!Reason_for_Withdrawal.Visible = 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.

Private Sub POL_Actions_After_Update()
If IsNull(Me!Change_Withdrawn) Then
  Me!Reason_for_Withdrawal.Enabled = False
Else
  Me!Reason_for_Withdrawal.Enabled = True
End If
End Sub
 

[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.

Private Sub POL_Actions_After_Update()
If IsNull(Me!Change_Withdrawn) Then
  Me!Reason_for_Withdrawal.Enabled = False
Else
  Me!Reason_for_Withdrawal.Enabled = True
End If
End Sub
 

Just a thought.

- Minion -

[jambonjamasb]

Just thought of something ...........

Private Sub POL_Actions_After_Update()
If IsNull(Me!Change_Withdrawn) Then
  Me!Reason_for_Withdrawal.Enabled = False
Else
  Me!Reason_for_Withdrawal.Enabled = True
End If
End Sub
 

Just a thought.

- Minion -

Hi Min,

I highlighted Change_Withdrawn
Selected after event and entered into the code builder
Private Sub Change_Withdrawn_AfterUpdate()
If IsNull(Me!Change_Withdrawn) Then
Me!Reason_for_Withdrawal.Enabled = False
Else
Me!Reason_for_Withdrawal.Enabled = True
End If
End Sub

I also tried to replace enabled with visable, both returned error code 438. I read through the help in access and didn't really understand it. Is this due to how I created the private sub or how I named it?

[jambonjamasb]

Thanks for all your help with this sorry to be such a pain.

[jambonjamasb]

I have also been on the microsoft website and again it was talking about binary updates as you are aware I only have a basic knowledge of access, but thanks to you guys it is growing.