Bound text boxes problem

[Mihail]

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):

cmdButton.visible = ((txt1 <> "") AND (txt2 <> ""))

This is not a true code because it don't work.
Some advices ?
Thank you !

[Mihail]

Just I found a solution (just after I post the question)

Private Sub txt1_Change()
    cmdButton.Visible = (txt1.Text <> "") And (Not IsNull(txt2))
End Sub

Private Sub txt2_Change()
    cmdButton.Visible = (Not IsNull(txt1)) And (txt2.Text <> "")
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.

[NeoPa]

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?

[NeoPa]

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 :

Private Sub txt1_AfterUpdate()
    Call SetVisibility()
End Sub

Private Sub txt2_AfterUpdate()
    Call SetVisibility()
End Sub

...

Private Sub txtn_AfterUpdate()
    Call SetVisibility()
End Sub

For any being set the SetVisibility() code would be :

Private Sub SetVisibility()
    Dim blnVis As Boolean
    Dim ctlThis As Control

    For Each ctlThis In Me.Controls
        With ctlThis
            If Left(.Name, 3) = "txt" Then
                If .Value > "" Then
                    blnVis = True
                    Exit For
                End If
            End If
        End With
    Next ctlThis
    cmdButton.Visible = blnVis
End Sub

On the other hand, for all being set the SetVisibility() code would be :

Private Sub SetVisibility()
    Dim blnVis As Boolean
    Dim ctlThis As Control

    blnVis = True
    For Each ctlThis In Me.Controls
        With ctlThis
            If Left(.Name, 3) = "txt" Then
                If IsNull(.Value) Then
                    blnVis = False
                    Exit For
                End If
            End If
        End With
    Next ctlThis
    cmdButton.Visible = blnVis
End Sub

[Mihail]

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(ActiveTe xtBox) ) 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(ActiveTe xtBox)= 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.

[NeoPa]

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"?

[NeoPa]

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 straightforward ly 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 :

Option Compare Database
Option Explicit

Private Sub Form_Current()
    Call SetVisibility
End Sub

Private Sub txt1_KeyUp(KeyCode As Integer, Shift As Integer)
    Call SetVisibility(Me.txt1)
End Sub

Private Sub txt1_AfterUpdate()
    Call SetVisibility
End Sub
 
Private Sub txt3_KeyUp(KeyCode As Integer, Shift As Integer)
    Call SetVisibility(Me.txt2)
End Sub

Private Sub txt2_AfterUpdate()
    Call SetVisibility
End Sub
 
Private Sub txt3_KeyUp(KeyCode As Integer, Shift As Integer)
    Call SetVisibility(Me.txt3)
End Sub

Private Sub txt3_AfterUpdate()
    Call SetVisibility
End Sub

Private Sub SetVisibility(Optional ByRef ctlMe As TextBox)
    Dim strText As String
    Dim blnVis As Boolean
    Dim ctlThis As Control
 
    blnVis = True
    For Each ctlThis In Me.Controls
        With ctlThis
            If Left(.Name, 3) = "txt" Then
                strText = Nz(.Value, "")
                If Not IsMissing(ctlMe) Then _
                    If ctlMe.Name = .Name Then strText = .Text
                If strText = "" Then
                    blnVis = False
                    Exit For
                End If
            End If
        End With
    Next ctlThis
    cmdButton.Visible = blnVis
End Sub

[Mihail]

With a very good idea from you, NeoPa (thank you ! ), and a little work from me:

Private Sub Form_Current()
    Call SetVisibility
End Sub

Private Sub txt_V1_Enter()
    Call SetVisibility
    'Assuming this is first control _
    which recive focus when open the form
End Sub

Private Sub txt_V1_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Call SetVisibility
End Sub

Private Sub txt_V2_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    SetVisibility
End Sub

Private Sub txt_V3_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    SetVisibility
End Sub


Private Sub SetVisibility()
On Error GoTo ErrorHandler
Dim blnVis As Boolean
Dim ctlThis As TextBox

    blnVis = True
    For Each ctlThis In Me.Controls
        With ctlThis
            If Left(.Name, 3) = "txt" Then
                If ctlThis.Name = Me.ActiveControl.Name Then
                    If ctlThis.Text = "" Then
                        blnVis = False
    Exit For
                    End If
                Else
                    If IsNull(ctlThis) Then
                        blnVis = False
    Exit For
                    End If
                End If
            End If
        End With
NextControl:
    Next ctlThis
    cmdBtn.Visible = blnVis
Exit Sub

ErrorHandler:
    Select Case Err.Number
        Case 13 'Type mismatch - ctlThis is not a TextBox
            Resume NextControl
        Case 2474 'The expression you entered requires the control to be in the active window.
                    'This error raise before the form is open
                    'No action is required, just avoid code to stop
        Case Else
            MsgBox ("Error in ""SetVisibility""" & Err.Number & " " & Err.Description)
'            Debug.Print Err.Number & " " & Err.Description
'            Stop
    End Select
End Sub

Thank you again !

[NeoPa]

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) ;-)

[Mihail]

Thank you, NeoPa, for congratulations .
It is so rarely from you :)