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

On Sat, 11 Oct 2008 18:59:12 -0700 (PDT), magmike <magmike7.TakeThisOut@yahoo.com> wrote:

>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) + [Email])

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

John W. Vinson [MVP]
 >> Stay informed about: How can I eliminate blank rows in record on a form? 

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, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"magmike" <magmike7 RemoveThis @yahoo.com> wrote in message
news:19c09ac5-0b6b-4d97-b857-6488067f3f9b@17g2000hsk.googlegroups.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
 >> Stay informed about: How can I eliminate blank rows in record on a form? 

On Oct 11, 11:19 pm, John W. Vinson
<jvinson.RemoveThis@STOP_SPAM.WysardOfInfo.com> wrote:
> On Sat, 11 Oct 2008 18:59:12 -0700 (PDT), magmike <magmi....RemoveThis@yahoo.com> wrote:
> >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) + [Email])
>
> will insert a new line and a value only if the value is not NULL.
> --
>
>              John W. Vinson [MVP]

John - I'm guessin' I would still have to format the whole shebang
exactly the same though, correct?
 >> Stay informed about: How can I eliminate blank rows in record on a form? 

"magmike" <magmike7 RemoveThis @yahoo.com> wrote in message
news:0e30c6fc-73bb-45fa-99f6-0e14844b27fd@t54g2000hsg.googlegroups.com...

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 [url] 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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
 >> Stay informed about: How can I eliminate blank rows in record on a form? 

On Sat, 11 Oct 2008 23:15:43 -0700 (PDT), magmike <magmike7 RemoveThis @yahoo.com> wrote:

>On Oct 11, 11:19 pm, John W. Vinson
><jvinson RemoveThis @STOP_SPAM.WysardOfInfo.com> wrote:
>> On Sat, 11 Oct 2008 18:59:12 -0700 (PDT), magmike <magmi... RemoveThis @yahoo.com> wrote:
>> >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) + [Email])
>>
>> will insert a new line and a value only if the value is not NULL.
>> --
>>
>>              John W. Vinson [MVP]
>
>John - I'm guessin' I would still have to format the whole shebang
>exactly the same though, correct?

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??
--

John W. Vinson [MVP]
 >> Stay informed about: How can I eliminate blank rows in record on a form? 

"magmike" <magmike7 RemoveThis @yahoo.com> wrote in message
news:19c09ac5-0b6b-4d97-b857-6488067f3f9b@17g2000hsk.googlegroups.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.

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.

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

(please reply to the newsgroup)
 >> Stay informed about: How can I eliminate blank rows in record on a form? 

On Oct 12, 5:39 pm, "Dirk Goldgar"
<d... RemoveThis @NOdataSPAMgnostics.com.invalid> wrote:
> "magmike" <magmi... RemoveThis @yahoo.com> wrote in message
>
> news:19c09ac5-0b6b-4d97-b857-6488067f3f9b@17g2000hsk.googlegroups.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.
>
> 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.
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)

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!
 >> Stay informed about: How can I eliminate blank rows in record on a form? 

"magmike" <magmike7.TakeThisOut@yahoo.com> wrote in message
news:5876537e-7a92-4250-b816-759fbec1fef2@26g2000hsk.googlegroups.com...
>
> 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 -----


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

(please reply to the newsgroup)
 >> Stay informed about: How can I eliminate blank rows in record on a form? 

"Arvin Meyer [MVP]" <arvinm.DeleteThis@mvps.invalid> дÈëÓʼþ
news:%23L9V3ZHLJHA.4292@TK2MSFTNGP03.phx.gbl...
>
> "magmike" <magmike7.DeleteThis@yahoo.com> wrote in message
> news:0e30c6fc-73bb-45fa-99f6-0e14844b27fd@t54g2000hsg.googlegroups.com...
>
> 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 [url] 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.
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>
>
 >> Stay informed about: How can I eliminate blank rows in record on a form? 

On Oct 13, 8:42 am, "Dirk Goldgar"
<d....DeleteThis@NOdataSPAMgnostics.com.invalid> wrote:
> "magmike" <magmi....DeleteThis@yahoo.com> wrote in message
>
> news:5876537e-7a92-4250-b816-759fbec1fef2@26g2000hsk.googlegroups.com...
>
>
>
> > 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 -----
>
> --
> Dirk Goldgar, MS Access MVPwww.datagnostics.com
>
> (please reply to the newsgroup)

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.
 >> Stay informed about: How can I eliminate blank rows in record on a form?