Hide text boxes with no value 1

[cneill]

Hi can anyone help?

I have an unbound form with 25 Text Boxes, 25 Labels and 25 Text boxes in the form footer on a Pop up form.
The query QryVenueAttendees1 is a crosstab query, the code will dynamically populate all the unbound controls.
25 is the maximum number that I need.
I am trying to hide any controls that are unpopulated
I am testing the last section of the code re the me.controls on the Text boxes in the detail section of the form, it works but not fully, it only hides the first unpopulated text box it finds, leaving the remaining still visible.
does anyone have any suggestions?

Public Sub Form_Load()

Me.RecordSource = "QryVenueAttendees1"

DoCmd.MoveSize 720, 1440, 17500, 9360

Dim mydb As Database
Dim rst As Recordset
Dim ctr As Integer
Dim lblObj As Label
Dim txtObj As TextBox
Dim txtObj1 As TextBox

Set mydb = CurrentDb

Set rst = Me.Form.RecordsetClone

For ctr = 0 To rst.Fields.Count - 1

Set lblObj = Me("Label" & ctr)
Set txtObj = Me("text" & ctr)
Set txtObj1 = Me("Sumtext" & ctr)

lblObj.Caption = rst.Fields(ctr).Name
txtObj.ControlSource = rst.Fields(ctr).Name
txtObj1.ControlSource = ("=Sum([" & rst.Fields(ctr).Name & "])")
Recalc

Next

Dim ct As Control

For Each ct In Me.Controls
Set txtObj = Me("text" & ctr)
If IsNull([txtObj]) Or txtObj = "" Or txtObj < 0 Then
txtObj.Visible = False
Else
txtObj.Visible = True

End If
Next ct
Set rst = Nothing
End Sub

 

[vbajock]

For Each ct In Me.Controls
If ct.ControlType = acTextBox Then

If isnull(ct.Value) Then
ct.visible=false
End If
End If
Next ct

me.repaint

 

[vbajock]

If you need to qualify the name

For Each ct In Me.Controls
If ct.ControlType = acTextBox Then
if mid$(ct.name,1,4)="text"
If isnull(ct.Value) Then
ct.visible=false
End If
End If
end if
Next ct

me.repaint

Also, you can use the Tag property for chores like these, you might want to look into it

 

[cneill]

Hi vbaLock
Yes I did need to qualify the name, so your second solution works a treat
Thanks very much

 

[PHV]

Why not simply this ?
For ctr = rst.Fields.Count To 25
Me("text" & ctr).Visible = False
Next


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cneill]

PHV
Works a treat and is faster, many thanks for everyones help

PHV/VBAJOCK - I have another minor problem you might be able to fix for me, I have had to put a Recalc in the code, because the txtObj1 - (this is the SumText boxes in the form footer), returns an error if I don't put it in, so makes the code longer to run

any thoughts on why I get an error without the recalc?

thanks
neill

 

[vbajock]

What error does it return?

 

[cneill]

it returns #Error

Thanks

 

[vbajock]

Try "=Sum(" & rst.Fields(ctr).Name & ")

 

[cneill]

Sorry gives me

Run time error 2434
The expression you entered contains invalid syntax

any other thoughts?

 

[vbajock]

Look up the DSUM function and apply it to your QryVenueAttendees1

 

[cneill]

thanks I will look at this later tonight and let you know

 

[cneill]

With the DSUM it slowed everything down, so I fixed the problem by removing the txtObj1.ControlSource = ("=Sum([" & rst.Fields(ctr).Name & "])")
section and creaing a sub form, again a crosstab query is used but this time with just totals, modefied code, super fast no errors

Thanks for your help