By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

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

P: 7
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub ReviewComplete_Exit(Cancel As Integer)
  2. If Not IsNull(ReviewComplete.Value) And Borrower.Value = "" Then
  3.     MsgBox "no borrower", vbOKOnly
  4.     Else
  5. End If
  6. End Sub
  7.  
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.
Jan 9 '08 #1
Share this Question
Share on Google+
18 Replies


ADezii
Expert 5K+
P: 8,623
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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub SetStatusOfTextBox()
    2. 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
    3.   Me![txtLocked].Locked = True
    4. Else
    5.   Me![txtLocked].Locked = False
    6. End If
    7. End Sub
  3. In the AfterUpdate() Event of each of the 7 Text Boxes, place the following single line of code:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub txtBox2_AfterUpdate()
    2.   Call SetStatusOfTextBox
    3. 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.
Jan 10 '08 #2

P: 7
Perfect!!!! Thank you sooo much. I'll be able to use the same logic to solve another issue I was having!
Jan 10 '08 #3

P: 41
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.
Jan 10 '08 #4

P: 7
I pretty sure it would. Just replace .locked with .hidden
Jan 10 '08 #5

Minion
Expert 100+
P: 108
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:
Expand|Select|Wrap|Line Numbers
  1. yourTextBox.visible = False
  2.  
That should work to hide the element using the previously posted code.

- Minion -
Jan 10 '08 #6

ADezii
Expert 5K+
P: 8,623
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.
Jan 10 '08 #7

ADezii
Expert 5K+
P: 8,623
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.
Jan 10 '08 #8

P: 41
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.
Jan 10 '08 #9

P: 41
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.
Jan 10 '08 #10

P: 41
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.
Jan 10 '08 #11

P: 41
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.
Jan 11 '08 #12

ADezii
Expert 5K+
P: 8,623
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.
Jan 11 '08 #13

Minion
Expert 100+
P: 108
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub POL_Actions_Enter()
  2. If IsNull(Me!Change_Withdrawn) Then
  3.   Me!Reason_for_Withdrawal.Visible = False
  4. Else
  5.   Me!Reason_for_Withdrawal.Visible = True
  6. End If
  7. End Sub
  8.  
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.
Jan 11 '08 #14

Minion
Expert 100+
P: 108
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub POL_Actions_After_Update()
  2. If IsNull(Me!Change_Withdrawn) Then
  3.   Me!Reason_for_Withdrawal.Enabled = False
  4. Else
  5.   Me!Reason_for_Withdrawal.Enabled = True
  6. End If
  7. End Sub
  8.  
Jan 11 '08 #15

Minion
Expert 100+
P: 108
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub POL_Actions_After_Update()
  2. If IsNull(Me!Change_Withdrawn) Then
  3.   Me!Reason_for_Withdrawal.Enabled = False
  4. Else
  5.   Me!Reason_for_Withdrawal.Enabled = True
  6. End If
  7. End Sub
  8.  
Just a thought.

- Minion -
Jan 11 '08 #16

P: 41
Just thought of something ...........

Expand|Select|Wrap|Line Numbers
  1. Private Sub POL_Actions_After_Update()
  2. If IsNull(Me!Change_Withdrawn) Then
  3.   Me!Reason_for_Withdrawal.Enabled = False
  4. Else
  5.   Me!Reason_for_Withdrawal.Enabled = True
  6. End If
  7. End Sub
  8.  
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?
Jan 14 '08 #17

P: 41
Thanks for all your help with this sorry to be such a pain.
Jan 14 '08 #18

P: 41
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.
Jan 14 '08 #19

Post your reply

Sign in to post your reply or Sign up for a free account.