Make a text box visible if a different text box has text in it

[Richard]

Question for Marsh if possible.
I have a text box named P1Bill1Name and...
Another text box named P1Bill1Paid.
If P1Bill1Name has text in it I want P1Bill1Paid
to be visible. I can do it easily with a line of code.
..P1Bill1Name.SetFocus: If .P1Bill1Name.Text = "" Then .P1Bill1Paid.Visible =
False
The problem is I have 50 Paid textboxes. I want to use a function
with a variable for the Paid text box....

Public Function MakeVisible()
Dim ctlCurrentControl As Control
Dim ctlPaid As Control
Set ctlCurrentControl = Screen.ActiveControl
Set ctlPaid = TextBox
Select Case ctlCurrentControl.Text
Case Is <> ""
'ctlCurrentControl.Name = P1Bill1Name
ctlPaid = Left(ctlCurrentControl.Name, 7) + "Paid"
'ctlPaid.Name = P1Bill1Paid
Me(ctlPaid).Visible = True
Case Is = ""
Me(ctlPaid).Visible = False
End Select
End Function

The Screen.ActiveControl works great but the
ctlPaid doesn't seem to work. I get the error "Object Required."
What am I doing wrong?
Please Help!

 

[Graham Mandeno]

Hi Richard

You refer to a control by way of a string expression using the following
syntax:
Me.Controls( <string expression> )
or, more simply:
Me( <string expression> )

You are constructing a valid string, but you are then assigning that string
to a control variable, not a string variable. Also, to assign something to
a control variable you must use Set, because a control is an object. So,
what you need is:

Set ctlPaid = Me( Left(ctlCurrentControl.Name, 7) & "Paid" )

(Note the more correct use of the string concatenation operator "&", instead
of "+")

Also, "Set ctlPaid = TextBox" is not correct. If you wish, you can Dim your
variables "As TextBox" rather than "As Control".

So, your function can be simplified to:
Dim ctlCurrentControl As TextBox
Dim ctlPaid As TextBox
Set ctlCurrentControl = Me.ActiveControl
Set ctlPaid = Me( Left(ctlCurrentControl.Name, 7) & "Paid" )
ctlPaid.Visible = Len(ctlCurrentControl.Text) > 0

Note that if you have as many textboxes as you say, you might need to adjust
your string expression. With "P22Bill22Name", Left(.Name,7) will leave you
with "P22Bill". If the textbox names always end with "Name" and "Paid", you
could use:
Set ctlPaid = Me( Left(ctlCurrentControl.Name, _
Len(ctlCurrentControl.Name) - 4) & "Paid" )

 

[Richard]

Thank you Graham, it worked Perfectly!!
You're amazing!!
I have another question below.

Public Function MakeVisible()
Dim ctlCurrentControl As TextBox
Dim ctlPaid As TextBox
Set ctlCurrentControl = Me.ActiveControl
Set ctlPaid = Me(Left(ctlCurrentControl.Name,
Len(ctlCurrentControl.Name) - 4) & "Paid")
ctlPaid.Visible = Len(ctlCurrentControl.Text) > 0
Set ctlCurrentControl = Nothing: Set ctlPaid = Nothing
End Function

The code above works when I type or delete in the box as I want it to.
But I want to have these P1Bill1Paid boxes to be visible when I load the
form or
go to a previous or new record. Visible only if P1Bill1Name has text in it.
I have 5 columns of text boxes. P1, P2, P3, etc. Each column has 10 rows,
Bill1, Bill2, Bill3, etc. So P1Bill1Name, P1Bill2Name, etc. and the
corresponding
P1Bill1Paid boxes. Hence the 50 mentioned before. What is the easiest way to
accomplish this.....
..P1Bill1Name.SetFocus: If .P1Bill1Name.Text = "" Then .P1Bill1Paid.Visible =
False
in the Sub Form_Current? With 5 Loops?

 

[Graham Mandeno]

Hi Richard

This definitely does not sound like a normalized database, but let's not go
there just now!

You can do this with two nested loops - one for the row and one for the
column.

Dim iRow as integer, iCol as integer, s as string
For iRow = 1 to 10
For iCol = 1 to 5
s = "P" & iCol & "Bill" & iRow
Me( s & "Paid" ) .Visible = Len( Me( s & "Name" ) & "" ) > 0
Next iCol
Next iRow

Note that you can't use the Text property when the control does not have the
focus, so instead we are using the default property (Value) and appending an
empty string to allow for the fact that it could be Null.

 

[Richard]

Thank you Graham, again it worked Perfectly!
As for my database, it's just a small program I use to plan out my budget
every month at home.
I can view the current month, past months and create a new month and new
year as time rolls on.
I have three tables, one main form, a subform for past months and a few
custom dialog boxes.(and alot of bills)!

G'day Graham
Thanks Again,

Hi Richard

This definitely does not sound like a normalized database, but let's not
go there just now!

You can do this with two nested loops - one for the row and one for the
column.

Dim iRow as integer, iCol as integer, s as string
For iRow = 1 to 10
For iCol = 1 to 5
s = "P" & iCol & "Bill" & iRow
Me( s & "Paid" ) .Visible = Len( Me( s & "Name" ) & "" ) > 0
Next iCol
Next iRow

Note that you can't use the Text property when the control does not have
the focus, so instead we are using the default property (Value) and
appending an empty string to allow for the fact that it could be Null.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand