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

Bound text boxes problem

P: 759
Hello !
I am in a little trouble with 2 text boxes and a button.
Can you help me ?

I have a bound form
In this form there are 2 text boxes bound to 2 fields and a Command Button.
The visibilities of the Command Button is set to FALSE at the beginning.

What I wish to do (Access 2007):
Expand|Select|Wrap|Line Numbers
  1. cmdButton.visible = ((txt1 <> "") AND (txt2 <> ""))
This is not a true code because it don't work.
Some advices ?
Thank you !
Nov 21 '11 #1
Share this Question
Share on Google+
9 Replies

P: 759
Just I found a solution (just after I post the question)
Expand|Select|Wrap|Line Numbers
  1. Private Sub txt1_Change()
  2.     cmdButton.Visible = (txt1.Text <> "") And (Not IsNull(txt2))
  3. End Sub
  5. Private Sub txt2_Change()
  6.     cmdButton.Visible = (Not IsNull(txt1)) And (txt2.Text <> "")
  7. End Sub
But something tell me that is not a very good one.
If I have 3,4,5, ... n text boxes I need 2*3*4*5*...*n combination after "=".

Ugly. Very ugly.
Nov 21 '11 #2

Expert Mod 15k+
P: 31,712
What's the logic you're after Mihail (exactly)? Do you want the Command Button to be visible when any of the TextBoxes have values in, or only when all of them have values in?
Nov 21 '11 #3

Expert Mod 15k+
P: 31,712
If we use the logic that the name of all the TextBox controls start with "txt" but only they do then the initial part of the code is standard :
Expand|Select|Wrap|Line Numbers
  1. Private Sub txt1_AfterUpdate()
  2.     Call SetVisibility()
  3. End Sub
  5. Private Sub txt2_AfterUpdate()
  6.     Call SetVisibility()
  7. End Sub
  9. ...
  11. Private Sub txtn_AfterUpdate()
  12.     Call SetVisibility()
  13. End Sub
For any being set the SetVisibility() code would be :

Expand|Select|Wrap|Line Numbers
  1. Private Sub SetVisibility()
  2.     Dim blnVis As Boolean
  3.     Dim ctlThis As Control
  5.     For Each ctlThis In Me.Controls
  6.         With ctlThis
  7.             If Left(.Name, 3) = "txt" Then
  8.                 If .Value > "" Then
  9.                     blnVis = True
  10.                     Exit For
  11.                 End If
  12.             End If
  13.         End With
  14.     Next ctlThis
  15.     cmdButton.Visible = blnVis
  16. End Sub
On the other hand, for all being set the SetVisibility() code would be :

Expand|Select|Wrap|Line Numbers
  1. Private Sub SetVisibility()
  2.     Dim blnVis As Boolean
  3.     Dim ctlThis As Control
  5.     blnVis = True
  6.     For Each ctlThis In Me.Controls
  7.         With ctlThis
  8.             If Left(.Name, 3) = "txt" Then
  9.                 If IsNull(.Value) Then
  10.                     blnVis = False
  11.                     Exit For
  12.                 End If
  13.             End If
  14.         End With
  15.     Next ctlThis
  16.     cmdButton.Visible = blnVis
  17. End Sub
Nov 21 '11 #4

P: 759
Thank you for reply, NeoPa.
I'll try to explain better:

A bound text box can contain data from database including NULL.
When the text box contain NULL it appear as an empty text box.

Now, I start to type in an empty (bound) text box.
After I enter first character, if ALL other (bound) text boxes NOT CONTAIN NULL, my command button must become visible. (in this way the user know that he type the last required information and he can use the command button any time).
But the user can type some characters (the command button is visible now) then he (the user) can delete characters. When the last character is deleted (the text box is empty) the command button must become invisible. If the user type again, after the first character entered, the command button must become visible, and so on.

For a certain text box I found a solution (see my second post).

But this solution can't be generalized.
I can't check (using NOT IsNull(ActiveTextBox) ) because the UPDATE event is not triggered yet so the active text box will contain NULL even I type a thousands of characters. More: When enter, the text box can contain a not NULL value. If the user delete all the characters, the command button must become invisible (I am sure you know that, in this case, the result of IsNull(ActiveTextBox)= FALSE)

More: I can't know at design time what text box will be the last one edited by user.

You know: My English is not very good. I learned English only from the computer's help files. So, with rarely exceptions, I speak ONLY at the present time. And my grammar... Thank you for your patience.
Nov 22 '11 #5

Expert Mod 15k+
P: 31,712
From that I understand that you need the all option specified below, but also that you need the granularity set on each keystroke (rather than each field being updated - which occurs after the field update has been completed btw).

The code would be fundamentally the same, but the current control value may not be available in its half-updated state. Also, being bound controls, the procedure would also need to be called from the Form_Current() event procedure. Give me a chance to check things out and I'll come back to you.

PS. Can I assume then, that all the TextBox controls you're interested in are the ones that start "txt"?
Nov 22 '11 #6

Expert Mod 15k+
P: 31,712
This was a lot more fiddly indeed. It doesn't handle (immediately) values being changed by mouse (EG. Cutting / Pasting) but handles keystrokes.

I tried using the form's KeyUp() event procedure but the .Text property is only accessible for the current control and that is not known or straightforwardly determinable (as far as I could find at least) so would necessarily trigger errors. This restriction also explains why the eventual code is so fiddly as great care had to be taken to refer to the .Text property for the current control but not for any of the others. Anyway, here's my example. It's specific this time rather than general as I had to develop it to test it :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Private Sub Form_Current()
  5.     Call SetVisibility
  6. End Sub
  8. Private Sub txt1_KeyUp(KeyCode As Integer, Shift As Integer)
  9.     Call SetVisibility(Me.txt1)
  10. End Sub
  12. Private Sub txt1_AfterUpdate()
  13.     Call SetVisibility
  14. End Sub
  16. Private Sub txt3_KeyUp(KeyCode As Integer, Shift As Integer)
  17.     Call SetVisibility(Me.txt2)
  18. End Sub
  20. Private Sub txt2_AfterUpdate()
  21.     Call SetVisibility
  22. End Sub
  24. Private Sub txt3_KeyUp(KeyCode As Integer, Shift As Integer)
  25.     Call SetVisibility(Me.txt3)
  26. End Sub
  28. Private Sub txt3_AfterUpdate()
  29.     Call SetVisibility
  30. End Sub
Expand|Select|Wrap|Line Numbers
  1. Private Sub SetVisibility(Optional ByRef ctlMe As TextBox)
  2.     Dim strText As String
  3.     Dim blnVis As Boolean
  4.     Dim ctlThis As Control
  6.     blnVis = True
  7.     For Each ctlThis In Me.Controls
  8.         With ctlThis
  9.             If Left(.Name, 3) = "txt" Then
  10.                 strText = Nz(.Value, "")
  11.                 If Not IsMissing(ctlMe) Then _
  12.                     If ctlMe.Name = .Name Then strText = .Text
  13.                 If strText = "" Then
  14.                     blnVis = False
  15.                     Exit For
  16.                 End If
  17.             End If
  18.         End With
  19.     Next ctlThis
  20.     cmdButton.Visible = blnVis
  21. End Sub
Nov 22 '11 #7

P: 759
With a very good idea from you, NeoPa (thank you ! ), and a little work from me:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     Call SetVisibility
  3. End Sub
  5. Private Sub txt_V1_Enter()
  6.     Call SetVisibility
  7.     'Assuming this is first control _
  8.     which recive focus when open the form
  9. End Sub
  11. Private Sub txt_V1_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  12.     Call SetVisibility
  13. End Sub
  15. Private Sub txt_V2_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  16.     SetVisibility
  17. End Sub
  19. Private Sub txt_V3_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  20.     SetVisibility
  21. End Sub
  24. Private Sub SetVisibility()
  25. On Error GoTo ErrorHandler
  26. Dim blnVis As Boolean
  27. Dim ctlThis As TextBox
  29.     blnVis = True
  30.     For Each ctlThis In Me.Controls
  31.         With ctlThis
  32.             If Left(.Name, 3) = "txt" Then
  33.                 If ctlThis.Name = Me.ActiveControl.Name Then
  34.                     If ctlThis.Text = "" Then
  35.                         blnVis = False
  36.     Exit For
  37.                     End If
  38.                 Else
  39.                     If IsNull(ctlThis) Then
  40.                         blnVis = False
  41.     Exit For
  42.                     End If
  43.                 End If
  44.             End If
  45.         End With
  46. NextControl:
  47.     Next ctlThis
  48.     cmdBtn.Visible = blnVis
  49. Exit Sub
  51. ErrorHandler:
  52.     Select Case Err.Number
  53.         Case 13 'Type mismatch - ctlThis is not a TextBox
  54.             Resume NextControl
  55.         Case 2474 'The expression you entered requires the control to be in the active window.
  56.                     'This error raise before the form is open
  57.                     'No action is required, just avoid code to stop
  58.         Case Else
  59.             MsgBox ("Error in ""SetVisibility""" & Err.Number & " " & Err.Description)
  60. '            Debug.Print Err.Number & " " & Err.Description
  61. '            Stop
  62.     End Select
  63. End Sub
Thank you again !
Nov 24 '11 #8

Expert Mod 15k+
P: 31,712
Excellent. I was looking for ActiveControl for ages but couldn't find it. That's a better solution Mihail :-)

PS. Some events are missing though. You will not capture key changes that change a field from empty to non-empty (and vice versa) unless you capture KeyUp for all relevant controls (although your MouseUp events should handle everything that AfterUpdate handled adequately) ;-)
Nov 24 '11 #9

P: 759
Thank you, NeoPa, for congratulations.
It is so rarely from you :)
Nov 24 '11 #10

Post your reply

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