Hiding controls based on non-presence of control.name in a table

[Alp Bekisoglu]

Hi Experts,

A2000, continuous form with 91 textboxes (as squares) based on a query.
Displays and performsproperly. The form displays as a rectangle of 44 (rows)
x 91 (boxes).

Intention is to hide the boxes if their names (A....Z, AA...CM) do not exist
in a table.

What code, if possible, should or could be used? How can I go through all
controls in the detail section only and do a comparison if their name exists
in a table so I can do something like:
.....
.Visible = False (or .Border = Transparent)
if the name is not found in the table?

Any pointers would be highly appreciated. Thanks in advance.

Alp

 

[Dirk Goldgar]

Hi Experts,

A2000, continuous form with 91 textboxes (as squares) based on a
query. Displays and performsproperly. The form displays as a
rectangle of 44 (rows) x 91 (boxes).

Intention is to hide the boxes if their names (A....Z, AA...CM) do
not exist in a table.

What code, if possible, should or could be used? How can I go through
all controls in the detail section only and do a comparison if their
name exists in a table so I can do something like:
....
.Visible = False (or .Border = Transparent)
if the name is not found in the table?

Any pointers would be highly appreciated. Thanks in advance.

How will the name be stored in the table? As a value in a particular
field? You might do something along these lines:

'----- start of sample code -----
Dim ctl As Access.Control

For Each ctl In Me.Section(acDetail).Controls

If ctl.ControlType = acTextBox Then

ctl.Visible = _
IsNull( _
DLookup( _
"ControlName", _
"MyTable", _
"ControlName='" & ctl.Name & "'" _
) & _
)

End If

Next ctl
'----- end of sample code -----

Note that the above code will raise an error if any of the controls it
tries to hide has the focus at that moment. You'll need to ensure that
none of the controls to be hidden has the focus.

 

[Alp Bekisoglu]

Hi Dirk'

The textbox names are stored as alphabetic charactes, i.e.;
A, B, C, D, ...to... Z, AA, AB, AC, AD, .....to... AZ, BA, BB, BC, BD,
......to.... BZ, CA, CB, CC, CD, ....to.... CM
for each record (row). Row 1 shall not show boxes after BS (BT to CM), then
the next row should stop after BT, etc. Then there are boxes to be hidden in
between like at row 4,...
This application is based on Duane's Seating database with the main
difference being Seating is a theater kind of available seats where as mine
is a complete floorplan for one of the halls of an exhibition. My intention
actually is to be able to display the form (the boxes each represent 1 sq
meter) like you could see at http://www.sada.com.tr/itc_lo.html where the
white area should not be visible on the form. This is to make it easier and
understandable for reservation entry. I am now working on the top one, block
A ground floor.
As further info, once the form is displayed user enters reservation (a
company number) by clicking on the relevant location (box).
I will try your suggested code and see if I can manipulate it as I intend.

Thanks,

Alp

 

[Alp Bekisoglu]

Hi Dirk,

I did test the code, which is now as:
'----- start of sample code -----
Dim ctl As Access.Control

For Each ctl In Me.Section(acDetail).Controls

If ctl.ControlType = acTextBox And ctl.Name <> "txtRowNumber" Then

ctl.Visible = Not IsNull(DLookup("seaSeatNumber", "standAlaniTamAalt",
"[seaSeatNumber]='" & ctl.Name & "' And [seaRowNumber]=" & Me.txtRowNumber))

End If

Next ctl
'----- end of sample code -----

It does work for the very first record. But all the rest of the records look
exactly like the first row on this continuous form. I think the code shall
also walk through each record, then through each control. Am I thinking
properly? Then I guess I should enclose the above code in between something
like:
For each record in myRecordset
run the code for controls
Next record

Would this work?

Alp

 

[Dirk Goldgar]

Hi Dirk,

I did test the code, which is now as:
'----- start of sample code -----
Dim ctl As Access.Control

For Each ctl In Me.Section(acDetail).Controls

If ctl.ControlType = acTextBox And ctl.Name <> "txtRowNumber" Then

ctl.Visible = Not IsNull(DLookup("seaSeatNumber",
"standAlaniTamAalt", "[seaSeatNumber]='" & ctl.Name & "' And
[seaRowNumber]=" & Me.txtRowNumber))

End If

Next ctl
'----- end of sample code -----

It does work for the very first record. But all the rest of the
records look exactly like the first row on this continuous form. I
think the code shall also walk through each record, then through each
control. Am I thinking properly? Then I guess I should enclose the
above code in between something like:
For each record in myRecordset
run the code for controls
Next record

Would this work?

I'm afraid not. You didn't mention before that your form is in
continuous forms view. On a continuous form, all instances of a control
(for the multiple records shown) are really the same control, drawn
multiple times. So setting the Visible property for that control is
going to affect all records.

You may be able to do something with Conditional Formatting, though it
won't be as simple as setting the Visible property -- that option isn't
available for CF. Or you may be able to use overlaying text boxes with
controlsource expressions that cover up the original text boxes, hiding
them.

 

[Alp Bekisoglu]

Actually I did mention it was a continuous from at the beginning of my
initial post but that is not important since we got to here. I've got two
main questions left then:
1- It really is not necessary whether the textbox is visible or not as long
as I can indicate it is an aisle i.e. transparent border/not Enabled/Locked/
etc. even fill color difference could do. But I will need to code that in I
think since conditional formatting deals mainly with the content of the
control rather than whether its name is in another table or not.

2- I have not tried yet but could I create a form with all controls on it?
92 textboxes x 44 rows (4048 controls on a single form detail?)

Thanks in advance.

Alp

Hi Dirk,

I did test the code, which is now as:
'----- start of sample code -----
Dim ctl As Access.Control

For Each ctl In Me.Section(acDetail).Controls

If ctl.ControlType = acTextBox And ctl.Name <> "txtRowNumber" Then

ctl.Visible = Not IsNull(DLookup("seaSeatNumber",
"standAlaniTamAalt", "[seaSeatNumber]='" & ctl.Name & "' And
[seaRowNumber]=" & Me.txtRowNumber))

End If

Next ctl
'----- end of sample code -----

It does work for the very first record. But all the rest of the
records look exactly like the first row on this continuous form. I
think the code shall also walk through each record, then through each
control. Am I thinking properly? Then I guess I should enclose the
above code in between something like:
For each record in myRecordset
run the code for controls
Next record

Would this work?

I'm afraid not. You didn't mention before that your form is in
continuous forms view. On a continuous form, all instances of a control
(for the multiple records shown) are really the same control, drawn
multiple times. So setting the Visible property for that control is
going to affect all records.

You may be able to do something with Conditional Formatting, though it
won't be as simple as setting the Visible property -- that option isn't
available for CF. Or you may be able to use overlaying text boxes with
controlsource expressions that cover up the original text boxes, hiding
them.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Actually I did mention it was a continuous from at the beginning of my
initial post

Oh, you're right. I just read right past it. I'm sorry -- I accused
you unjustly.

but that is not important since we got to here. I've got
two main questions left then:
1- It really is not necessary whether the textbox is visible or not
as long as I can indicate it is an aisle i.e. transparent border/not
Enabled/Locked/ etc. even fill color difference could do. But I will
need to code that in I think since conditional formatting deals
mainly with the content of the control rather than whether its name
is in another table or not.

No, you can use conditional formatting to evaluate an expression --
pretty much any expression that yields a true/false result -- and set
the fomatting according to the truth or falsehood of that expression.
You use the "Expression Is" condition type. You ought to be able to use
your DLookup expression there, but it may be rather sluggish. Maybe you
can set the form's recordsource to a query that does the lookup for you.

2- I have not tried yet but could I create a form with all controls
on it? 92 textboxes x 44 rows (4048 controls on a single form detail?)

No, that's too many controls. There's a limit of something like 754
controls that a form can hold. I gather that limit has varied somewhat
from Access version to version, but you're still way over it.

 

[Alp Bekisoglu]

I am now trying the CF on a field to see if I grt to anywhere before I
attempt to CF all and find that it does not work. The lookup I'm trying is:
(DLookUp("ayrilan","qselSeatAvailabilityYeniTamAalt","[RowNumber]=" &
Me.txtRrowNumber & " And [SeatNumber]='" & Me.Name & "'"))=0

I might be coding it wrongly here since it does not work. txtRowNumber is
part of every record (row) and I need to compare the control's name (i.e.
AH) to the SeatNumber in the query. Although the name implies numeric,
SeatNumber in the query is alpha. How could I refer to the present record's
another control value and the present control's name within the Expression
Is of CF?

Alp