Nothing to display in "detail" section

[striker73]

I have a report that displays client and contact information in the following format:

Client #1
Contact1
Contact2
Contact3, etc.

Client #2
Contact1
Contact2, etc.

I am running into the situation where the client might not have any contacts related to it, but Access still outputs the fields for the contact, like the following:

Client Name: ARCO
Contact Name: Jon Wilkinson
Title: Cheif Engineer
Phone: 949-582-6011
Fax: 714-582-0122

Client Name: Air Products
Contact Name:
Title:
Phone:
Fax:

Client Name: Chevron Refineries
Contact Name: Mike Jones
Title: President
Phone: 714-582-6955
Fax: 714-582-6933

Contact Name: John Doe
Title: Operations Officer
Phone: 714-582-6930
Fax: 714-202-5144


So if there are no contacts to display, I still get the field names for the contact. How can I avoid this? Any ideas? Thanks!

 

[Steve101]

(a) In your report, replace the label controls for the four contact details with unbound text controls. Call these
lblContactName, lblTitle, lblPhone and lblFax respectively.

(b) Align the new 'pseudo label' controls to the left of and in line with their corresponding bound text fields.

(c) Make sure that each pair of corresponding fields is touching top-to-bottom, the pair above/below them.

(d) Set the CanGrow/CanShrink properties for each of the eight fields to Yes. Also set the CanGrow/CanShrink properties associated with the Detail section to Yes.

(e) Add the following code to the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim R As Report: Set R = Me
If IsNull(R!ContactName) Then
R!lblContactName = Null
Else
R!lblContactName = "Contact Name:"
End If
If IsNull(R!Title) Then
R!lblTitle = Null
Else
R!lblTitle = "Title:"
End If
If IsNull(R!Phone) Then
R!lblPhone = Null
Else
R!lblPhone = "Phone:"
End If
If IsNull(R!Fax) Then
R!lblFax = Null
Else
R!lblFax = "Fax:"
End If
End Sub

This code programatically controls the value of the 'pseudo label fields', depending on whether associated data exists or not. It assumes that the names of your bound fields are ContactName, Title, Phone and Fax respectively. Change the code otherwise as appropriate.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au