Is it possible?

[Guest]

Hi! I would just like know if it is possible to shrink a row of controls
based on one.

For example, I have 1 label called "Fifth Child" and 3 textboxes called
"Fifth Child Name", "Fifth Child Date of Birth" and "NEWMG Fifth Child
Premium". Could a code be created to shrink the label and 3 textebox if
"NEWMG Fifth Child Premium" is blank? If so, how?

I would really appreciate an answer to this question and any help in
accomplishing the task!

Tandy

 

[Guest]

You can use code
On the on print event of the section where the fields are write the code

me.[Fifth Child].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Name].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Date of Birth].visible = not isnull(me.[NEWMG Fifth Child
Premium])
===================================================
or you can use:
If isnull(me.[NEWMG Fifth Child Premium]) Then
me.[Fifth Child].visible = False
me.[Fifth Child Name].visible = False
me.[Fifth Child Date of Birth].visible = False
Else
me.[Fifth Child].visible = True
me.[Fifth Child Name].visible = True
me.[Fifth Child Date of Birth].visible = True
End if

 

[Guest]

You can do it with VBA using the "On Open" event... the code would be
something like this

if(Nz(me.NEWMG_Fifth_Child_Premium,0) = 0) then
me.Fifth_Child_Name.Width = 2*1440
me.Fifth_Child_Date_Of_Birth.width = 2*1440
me.NEWMG_Fifth_Child_Premium.width = 2*1440
end if

this is obviously and "IF - Then statement...
Nz is used to check if a filed is null, the ",0" means if it is Null, to
temporarly assign a 0. So, if the filed is null, the IF statement is true and
the procedures after the "Then" will happen, if not, it will skip to the "End
IF" part.
The "Me." is refering to the form, the "Fifth_Child..." is refering to the
controls name (if you have a space in the name, you have to use an Underscore
in its place)
The ".widts" refers to the controls width
Finally, the 2*1440 sets the width of the control. this number is in
"twips", and their are 1440 twips in an inch, so if you want the control to
be 2" you multiply 2*1440, if you want it 1/2" you would multiply .5*1440.

It's that simple... I hope this helps!

 

[Guest]

Ofer,

Thank you for the code. I only have one tiny problem. Your code makes
the label and textboxes invisible instead of shrinking them. As a result
there is a blank space in between employee. This wouldn't matter except I am
actually using this code for 5 different rows so it turns into quite a large
space in between employees. Is there a way I can shrink the label and
textboxes if "NEWMG Fifth Child Premium" is blank? I am sorry to bother you
again!

Tandy

You can use code
On the on print event of the section where the fields are write the code

me.[Fifth Child].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Name].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Date of Birth].visible = not isnull(me.[NEWMG Fifth Child
Premium])
===================================================
or you can use:
If isnull(me.[NEWMG Fifth Child Premium]) Then
me.[Fifth Child].visible = False
me.[Fifth Child Name].visible = False
me.[Fifth Child Date of Birth].visible = False
Else
me.[Fifth Child].visible = True
me.[Fifth Child Name].visible = True
me.[Fifth Child Date of Birth].visible = True
End if

Hi! I would just like know if it is possible to shrink a row of controls
based on one.

For example, I have 1 label called "Fifth Child" and 3 textboxes called
"Fifth Child Name", "Fifth Child Date of Birth" and "NEWMG Fifth Child
Premium". Could a code be created to shrink the label and 3 textebox if
"NEWMG Fifth Child Premium" is blank? If so, how?

I would really appreciate an answer to this question and any help in
accomplishing the task!

Tandy

 

[Duane Hookom]

Apparently you were not satisfied with your answers in the other thread that
suggested normalizing your child records with a union query so that you
don't need any convoluted code?

--
Duane Hookom
MS Access MVP
--

Ofer,

Thank you for the code. I only have one tiny problem. Your code makes
the label and textboxes invisible instead of shrinking them. As a result
there is a blank space in between employee. This wouldn't matter except I
am
actually using this code for 5 different rows so it turns into quite a
large
space in between employees. Is there a way I can shrink the label and
textboxes if "NEWMG Fifth Child Premium" is blank? I am sorry to bother
you
again!

Tandy

You can use code
On the on print event of the section where the fields are write the code

me.[Fifth Child].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Name].visible = not isnull(me.[NEWMG Fifth Child
Premium])
me.[Fifth Child Date of Birth].visible = not isnull(me.[NEWMG Fifth Child
Premium])
===================================================
or you can use:
If isnull(me.[NEWMG Fifth Child Premium]) Then
me.[Fifth Child].visible = False
me.[Fifth Child Name].visible = False
me.[Fifth Child Date of Birth].visible = False
Else
me.[Fifth Child].visible = True
me.[Fifth Child Name].visible = True
me.[Fifth Child Date of Birth].visible = True
End if

Hi! I would just like know if it is possible to shrink a row of
controls
based on one.

For example, I have 1 label called "Fifth Child" and 3 textboxes called
"Fifth Child Name", "Fifth Child Date of Birth" and "NEWMG Fifth Child
Premium". Could a code be created to shrink the label and 3 textebox if
"NEWMG Fifth Child Premium" is blank? If so, how?

I would really appreciate an answer to this question and any help in
accomplishing the task!

Tandy

 

[Guest]

Did you put the can shrink property of the fields and the section where the
fields are to true.

Ofer,

Thank you for the code. I only have one tiny problem. Your code makes
the label and textboxes invisible instead of shrinking them. As a result
there is a blank space in between employee. This wouldn't matter except I am
actually using this code for 5 different rows so it turns into quite a large
space in between employees. Is there a way I can shrink the label and
textboxes if "NEWMG Fifth Child Premium" is blank? I am sorry to bother you
again!

Tandy

You can use code
On the on print event of the section where the fields are write the code

me.[Fifth Child].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Name].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Date of Birth].visible = not isnull(me.[NEWMG Fifth Child
Premium])
===================================================
or you can use:
If isnull(me.[NEWMG Fifth Child Premium]) Then
me.[Fifth Child].visible = False
me.[Fifth Child Name].visible = False
me.[Fifth Child Date of Birth].visible = False
Else
me.[Fifth Child].visible = True
me.[Fifth Child Name].visible = True
me.[Fifth Child Date of Birth].visible = True
End if

Hi! I would just like know if it is possible to shrink a row of controls
based on one.

For example, I have 1 label called "Fifth Child" and 3 textboxes called
"Fifth Child Name", "Fifth Child Date of Birth" and "NEWMG Fifth Child
Premium". Could a code be created to shrink the label and 3 textebox if
"NEWMG Fifth Child Premium" is blank? If so, how?

I would really appreciate an answer to this question and any help in
accomplishing the task!

Tandy

 

[Guest]

Ofer,

I put the Can Shrink property to yes on the textboxes and the section.
The labels do not have one. Also, Fifth Child Name and Fifth Child Date of
Birth could have information in it even if there is nothing in NEWMG Fifth
Child Premium. I also used the first code you gave me:

me.[Fifth Child].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Name].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Date of Birth].visible = not isnull(me.[NEWMG Fifth Child
Premium])

Thank you again for the help!

Tandy

 

[Guest]

Sorry, move the code to the on format property of the section, and not in the

Ofer,

I put the Can Shrink property to yes on the textboxes and the section.
The labels do not have one. Also, Fifth Child Name and Fifth Child Date of
Birth could have information in it even if there is nothing in NEWMG Fifth
Child Premium. I also used the first code you gave me:

me.[Fifth Child].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Name].visible = not isnull(me.[NEWMG Fifth Child Premium])
me.[Fifth Child Date of Birth].visible = not isnull(me.[NEWMG Fifth Child
Premium])

Thank you again for the help!

Tandy

 

[Guest]

Ofer,

Hi! I moved the code to the On Format property of the section and there
is still a huge blank spot. Any other idea what it might be???

Tandy

 

[Guest]

Beside this fields that we specify as visible false, are there other fields
or lables?
I tried it few times and it gives the right resault

But, if you dont want the fields to apear, mybe the most simple thing to do
is write a criteria in the record source of the report

Select * From MyTable Where [NEWMG Fifth Child Premium] Is Not Null
That wont return the desire records, and you wont need to program anything

 

[Guest]

Ofer,

Hi! OK, before I was using a form made in form wizard. So I made a form
from scratch and it works like it should. I guess I didn't realize that no
matter what there will be some blank space.
So... I guess I will try writing a criteria in te record source of the
report. However, I don't really know how to do that. I'm sorry, I'm pretty
useless when it comes to Access.

Tandy

 

[Guest]

Post your corrent SQL in the record source and I'll help you from there.

 

[Guest]

Ofer,

Hi! I just wanted to let you know I finally got my report to come out
how I needed. Thank you so much for all of your help, I wouldn't have even
known where to start without it. I really appreciate it!

 

[Guest]

Good luck with your project