How can I eliminate blank rows in record on a form?

[magmike]

I would like a form to display multiple fields from one record down
one column. This is basic contact information like name, title,
address, etc. However, if for example - the order is name, title,
address, phone, email - and there is not any data in the address
field, I would like the phone number to show directly under the title.
How can I do this on a form with separate fields so I can format them
each individually?

Currently I have all the data showing in one unbound field with If
statements which accomplishes this, without the ability to format each
field's data individually.

Thanks in advance for your help!
magmike

 

[Arvin Meyer [MVP]]

Base the form upon a query where you have used the IIf() function to create
the column:

Contact: IIf(IsNull([Address]), [Phone], [Address] & " " & [City])

 

[John W. Vinson]

I would like a form to display multiple fields from one record down
one column. This is basic contact information like name, title,
address, etc. However, if for example - the order is name, title,
address, phone, email - and there is not any data in the address
field, I would like the phone number to show directly under the title.
How can I do this on a form with separate fields so I can format them
each individually?

Currently I have all the data showing in one unbound field with If
statements which accomplishes this, without the ability to format each
field's data individually.

Thanks in advance for your help!
magmike

There's a cute trick using the + operator to concatenate strings. The &
operator and + operator both concatenate, but + propagates nulls - i.e. a
string & NULL returns the string, but string + NULL returns Null. So an
expression:

=[Name] & (Chr(13) + Chr(10) + [Title]) & (Chr(13) + Chr(10) + [Address]) &
(Chr(13) + Chr(10) + [Phone]) & (Chr(13) + Chr(10) + )

will insert a new line and a value only if the value is not NULL.

 

[magmike]

Base the form upon a query where you have used the IIf() function to create
the column:

Contact: IIf(IsNull([Address]), [Phone], [Address] & " " & [City])
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

I would like a form to display multiple fields from one record down
one column. This is basic contact information like name, title,
address, etc. However, if for example - the order is name, title,
address, phone, email - and there is not any data in the address
field, I would like the phone number to show directly under the title.
How can I do this on a form with separate fields so I can format them
each individually?

Currently I have all the data showing in one unbound field with If
statements which accomplishes this, without the ability to format each
field's data individually.

Thanks in advance for your help!
magmike- Hide quoted text -

- Show quoted text -

Forgive me for being a little confused. If I understand what you
recommend, if [address] is null, then [phone] would display in the
forms [contact] field? Using your recommendation, If I wanted [phone]
displayed in a red bold font, and [address] in a regular black font,
and [contact] in bold black font - wouldn't phone be displayed in a
bold black font instead since it is appearing in the [contact] field?
Or are you suggesting I have multiple fields named Contact and each
one addresses the next two fields in the process? And if so, how does
that correlate with the form fields and controling how I want [phone]
displayed or displayed?

magmike
If I do it the way you suggest, they would still all be in the same
font, font size, and font style. I would like to say that the contact
name is always bold

 

[magmike]

I would like a form to display multiple fields from one record down
one column. This is basic contact information like name, title,
address, etc. However, if for example - the order is name, title,
address, phone, email - and there is not any data in the address
field, I would like the phone number to show directly under the title.
How can I do this on a form with separate fields so I can format them
each individually?

Currently I have all the data showing in one unbound field with If
statements which accomplishes this, without the ability to format each
field's data individually.

Thanks in advance for your help!
magmike

There's a cute trick using the + operator to concatenate strings. The &
operator and + operator both concatenate, but + propagates nulls - i.e. a
string & NULL returns the string, but string + NULL returns Null. So an
expression:

=[Name] & (Chr(13) + Chr(10) + [Title]) & (Chr(13) + Chr(10) + [Address]) &
(Chr(13) + Chr(10) + [Phone]) & (Chr(13) + Chr(10) + )

will insert a new line and a value only if the value is not NULL.[/QUOTE]

John - I'm guessin' I would still have to format the whole shebang
exactly the same though, correct?

 

[Arvin Meyer [MVP]]

Forgive me for being a little confused. If I understand what you
recommend, if [address] is null, then [phone] would display in the
forms [contact] field? Using your recommendation, If I wanted [phone]
displayed in a red bold font, and [address] in a regular black font,
and [contact] in bold black font - wouldn't phone be displayed in a
bold black font instead since it is appearing in the [contact] field?
Or are you suggesting I have multiple fields named Contact and each
one addresses the next two fields in the process? And if so, how does
that correlate with the form fields and controling how I want [phone]
displayed or displayed?

magmike
If I do it the way you suggest, they would still all be in the same
font, font size, and font style. I would like to say that the contact
name is always bold

You can use Conditional formatting if you are using a continuous form or
datasheet view. In single form view, you can also use code in the Current
event with 2 textboxes, showing and hiding as appropriate.

 

[John W. Vinson]

I would like a form to display multiple fields from one record down
one column. This is basic contact information like name, title,
address, etc. However, if for example - the order is name, title,
address, phone, email - and there is not any data in the address
field, I would like the phone number to show directly under the title.
How can I do this on a form with separate fields so I can format them
each individually?

Currently I have all the data showing in one unbound field with If
statements which accomplishes this, without the ability to format each
field's data individually.

Thanks in advance for your help!
magmike

There's a cute trick using the + operator to concatenate strings. The &
operator and + operator both concatenate, but + propagates nulls - i.e. a
string & NULL returns the string, but string + NULL returns Null. So an
expression:

=[Name] & (Chr(13) + Chr(10) + [Title]) & (Chr(13) + Chr(10) + [Address]) &
(Chr(13) + Chr(10) + [Phone]) & (Chr(13) + Chr(10) + )

will insert a new line and a value only if the value is not NULL.[/QUOTE]

John - I'm guessin' I would still have to format the whole shebang
exactly the same though, correct?[/QUOTE]

I'm with Arvin... I'm not visualizing what you want. What kind of "formatting"
do you want to apply? Seeing the phone number in red and the title in italics,
or what??

 

[magmike]

I would like a form to display multiple fields from one record down
one column. This is basic contact information like name, title,
address, etc. However, if for example - the order is name, title,
address, phone, email - and there is not any data in the address
field, I would like the phone number to show directly under the title..
How can I do this on a form with separate fields so I can format them
each individually?
Currently I have all the data showing in one unbound field with If
statements which accomplishes this, without the ability to format each
field's data individually.
Thanks in advance for your help!
magmike
There's a cute trick using the + operator to concatenate strings. The &
operator and + operator both concatenate, but + propagates nulls - i.e.. a
string & NULL returns the string, but string + NULL returns Null. So an
expression:
=[Name] & (Chr(13) + Chr(10) + [Title]) & (Chr(13) + Chr(10) + [Address]) &
(Chr(13) + Chr(10) + [Phone]) & (Chr(13) + Chr(10) + (e-mail address removed)

 

[Dirk Goldgar]

I would like a form to display multiple fields from one record down
one column. This is basic contact information like name, title,
address, etc. However, if for example - the order is name, title,
address, phone, email - and there is not any data in the address
field, I would like the phone number to show directly under the title.
How can I do this on a form with separate fields so I can format them
each individually?

Currently I have all the data showing in one unbound field with If
statements which accomplishes this, without the ability to format each
field's data individually.

It sounds to me like you're trying to get something like the "Can Shrink"
functionality of controls on a report, but on a form instead. While text
boxes on forms do have a "Can Shrink" property, that only applies when the
form is being printed, so you're out of luck as far as that property is
concerned.

You could use the form's Current event to hide empty controls and move other
controls up to fill the empty space. However, the code to do this may be
more complicated than you want to get involved with. You'd have to
determine which controls are going to be displayed, then calculate a new Top
property for each control based on the Height properties of the controls to
be displayed and the amount of vertical space you want to leave between
them.

Normally, you don't want to shrink unused controls on a form, because you
want the user to be able to enter data in those empty controls. I'm
guessing your form must be a read-only form used for display only, or it
doesn't make sense to me. I suppose you could use a report instead of a
form; then you could use the Can Shrink property.

 

[magmike]

It sounds to me like you're trying to get something like the "Can Shrink"
functionality of controls on a report, but on a form instead.  While text
boxes on forms do have a "Can Shrink" property, that only applies when the
form is being printed, so you're out of luck as far as that property is
concerned.

You could use the form's Current event to hide empty controls and move other
controls up to fill the empty space.  However, the code to do this may be
more complicated than you want to get involved with.  You'd have to
determine which controls are going to be displayed, then calculate a new Top
property for each control based on the Height properties of the controls to
be displayed and the amount of vertical space you want to leave between
them.

Normally, you don't want to shrink unused controls on a form, because you
want the user to be able to enter data in those empty controls.  I'm
guessing your form must be a read-only form used for display only, or it
doesn't make sense to me.  I suppose you could use a report instead of a
form;  then you could use the Can Shrink property.

Holy smokes! I never realized that I could use a subreport on a form.
That answers my problem! Is there any reason that would be a bad idea?

And, yes, Dirk, you are right. It is just a display through a subform
on the parent form based on which contact is selected. I guess I am
going to play with subreports now. Thanks!

 

[Dirk Goldgar]

Holy smokes! I never realized that I could use a subreport on a form.
That answers my problem! Is there any reason that would be a bad idea?

Whoa! I never said that you could. Who said anything about a subreport --
or a subform either, for that matter? I can't say for sure about Access
2007 (that PC is turned off at the moment) but in Access 2003 and earliler,
you can't put a subreport on a form.

And, yes, Dirk, you are right. It is just a display through a subform on
the parent form based on which contact is selected. I guess I am

going to play with subreports now.

That's not going to work (unless I've overlooked some feature that would
permit it). But you could have a report open separately in Print Preview
mode, and then filter it from your form to show the particular record that
is current on the form. The code on the form would be something along these
lines:

'----- start of example code -----
Private Sub Form_Current()

Const conReportName As String = "YourReportName"

If Not CurrentProject.AllReports(conReportName).IsLoaded Then
DoCmd.OpenReport conReportName, acViewPreview
End If

With Reports(conReportName)
.Filter = "ID = " & Me.ID
.FilterOn = True
End With

End Sub

'----- end of example code -----

 

[ÀîÑÞÏã]

Forgive me for being a little confused. If I understand what you
recommend, if [address] is null, then [phone] would display in the
forms [contact] field? Using your recommendation, If I wanted [phone]
displayed in a red bold font, and [address] in a regular black font,
and [contact] in bold black font - wouldn't phone be displayed in a
bold black font instead since it is appearing in the [contact] field?
Or are you suggesting I have multiple fields named Contact and each
one addresses the next two fields in the process? And if so, how does
that correlate with the form fields and controling how I want [phone]
displayed or displayed? magmike If I do it the....org/access http://www.accessmvp.com [/QUOTE]

 

[magmike]

Whoa!  I never said that you could.  Who said anything about a subreport --  
or a subform either, for that matter?  I can't say for sure about Access
2007 (that PC is turned off at the moment) but in Access 2003 and earliler,
you can't put a subreport on a form.


going to play with subreports now.

That's not going to work (unless I've overlooked some feature that would
permit it).  But you could have a report open separately in Print Preview
mode, and then filter it from your form to show the particular record that
is current on the form.  The code on the form would be something along these
lines:

'----- start of example code -----
Private Sub Form_Current()

    Const conReportName As String = "YourReportName"

    If Not CurrentProject.AllReports(conReportName).IsLoaded Then
        DoCmd.OpenReport conReportName, acViewPreview
    End If

    With Reports(conReportName)
        .Filter = "ID = " & Me.ID
        .FilterOn = True
    End With

End Sub

'----- end of example code -----

I got excited too fast. When you hover over the tool button for add a
subform to for in form design view, the tool tip says Insert Subform/
Subreport.