Change title field based on detail record

Posted on 2012-03-28
Last Modified: 2012-03-30
I have an existing Report that has address data in the title at the top that comes from a single record in an address table.  The user wants a different phone number to show in the title based on the type of detail record reported on in the detail section.  I have added a 2nd phone number field to the address table.

I don't know what to put in the Control Source for the phone number in the title.  Right now it is the name of the original phone number field in the address table.

If I need VB code I am not sure what event to attach the code to.
Question by:IBMJunkman
LVL 77

Expert Comment

ID: 37777896
More technical detail needed.

'the title at the top' - what does this mean ?  A textbox in the report header? or what?
Is there a separate textbox for the phone number?

'type of detail record ' - means what? Is there only one record being reported in the report?
How is 'type of detail record ' defined?

Is each telephone number in its own field in the report's recordsource?

Author Comment

ID: 37778122
The report Page Header section displays company name, address, etc along with a company phone number.  The data values for these fields come from an Address table that has 1 row.
I have added a 2nd phone number to this table.

The report Detail section displays data from a customer table.  Based on a value in each customer record I need to change the phone number in the Page Header section using 1 of the 2 phone numbers in the Address table.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37778174
...then if the Cust has two numbers, which one do you want to display...?

Typical systems like this will have a One to many relationship between the Customer and thier many Phone numbers.

In the Phone number table there would be a "IsPrimary", Yes/No field, to signify which will print.
Then the Report would be filtered on only Phone numbers that have IsPrimary set as True.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37778263
<The Report Page Header section displays company name,>
Lets be clear...
The Report Header, or the Page header...?
These are two different sections...

It is also not clear if you are filtering this report By Cust or it is Grouped by cust......
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37778301
Her is my take on this:

Author Comment

ID: 37778449
The document being printed is something like a customer balance statement.  The company info is printed in a Page Header.  The customer data is printed in the Detail section.

The phone number being printed is NOT the customers.  It is the companies but needs to change based on what type of customer is being printed.  There are 2 types of customers.  Type 1 gets corporate phone number 1 printed on their statement.  A type 2 customer gets corporate phone number 2 printed on their statement.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37778861
None of this was mentioned in the original question...
Still not clear if the report is filtered or Grouped...
Still not clear if the phone numbers are in the same table as the customers...

Test this modified sample
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 31

Assisted Solution

Helen_Feddema earned 50 total points
ID: 37779398
What you want doesn't match the uploaded databases.  The report's record source query only includes phone numbers in tblPhonenumbers that have IsPrimary = True.  So you get just one phone number per company.  You might be able to do something like what you want using a CustomerType field (there isn't one at present), and linking that to a specific phone number for a customer (something like =Iif([CustomerType] = 1, [Phone1], [Phone2]) as a textbox control source) .  But tables would have to be redesigned.

Author Comment

ID: 37781848
New DB uploaded with exact situation.
On page 1 for Customer Jake the phone number in the PageHeader should be 2022782000.
On page 2 for Customer Bob the phone number in the PageHeader should be 7034820623.
On page 3 for Customer Sally the phone number in the PageHeader should be 7034820623.
On page 4 for Customer Sam the phone number in the PageHeader should be 2022782000.
I tried the Iff solution but the number does not change.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37782068
1. In your sample you do not have a "Type" designation associated with the two phone numbers...?
2, It is also not clear how the Customer is tied to the Corp.
3. You also seemed to have deleted my "Types" table...?
4. It is also not clear if this is a table for only One Corp, ...or many?
(Only one corporation is listed...?)
5. There is no Primary Key in the Corp table to tie back to the customer?
6. Can one Cust be associated with many Corps?
7. Can a corp have more than two phone numbers?

In other words, your system here is not "Normalized"
So I would first ask what is your skill level with basic database design
(Normalization, Relationships)

I am creating a new sample based on my take on this...

Author Comment

ID: 37782326
I did not design the database I am working on.  The user just asked me to make the corporate phone number change based on the customer type.

1. The customer has a type.  The corp does not.
2. All customers 'belong' to the single corp.
3. For this situation a type table is not really needed. Type is in the customer record.
4. There is 1 corp and many customers.
5. The customers 'belong' to the single corp. No Corp primary key really needed. Only 1 record in the corp table.
6. There is only 1 Corp.
7. Yes, the single corp has 2 phones numbers.  Which phone number is printed in the page header depends on the customer type.

Try this scenario.  I own a company.  I have a phone number for cash customers to call and a different phone number for credit customers to call. When I print statements the phone number at the top of the statement (as part of my corp info) has to change based on what type of customer you are.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37783175
Thanks for the Scenario, not it is a lot clearer.

Still some of the things you have said above violate standard database theory.
Is this what you want?

Also, some things you asked for are done a lot easier than how you specified them.
For example, if you normalized this system, you could easily put all the info in the detail section and achieve the same effect (Corp info in the Page Header), by setting the "Force new page" property of the detail section to: After Section.
This eliminates the need for the extra sections or the overhead associated with subreports.
(It also simplifies things if you later create a system to "Select" a specific
This is why it is important to *first* start with a normalized design, then you can do anything you want with minimal effort.)
Obviously you can still put the Corp info in the page header if you like, but I don't see the need to complicate this.

See the attached sample DB




Author Comment

ID: 37783957

I bet you are a developer.  :-)

I am in support.  I don't have the luxury of redesigning the tables and forms.  They are what they are.

I thought about getting the 2 phone numbers at report On Load and storing them in 2 globals.  The question is how to word the control field statement for the page header phone number.  Can it reference a field from the detail query/detail section?

=Iff(detailfield = “1”, gblPhone1, gblPhone2)

Should detailfield be qualified somehow?  Will the Iff be executed for each detail record?
LVL 74

Accepted Solution

Jeffrey Coachman earned 200 total points
ID: 37784125
<I bet you are a developer.  :-)>
I have been called that, ...among many other things...

Yes, I know that sometimes you must:
"Build with the bricks you are given"

So here goes...
All you have to do in *your* report (The one with the subreport) is change your IIF in the subreport to this:
(and reduce the size of the empty sections to eliminate the blank page at the end)


LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37787726

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Report that will show table changes 7 45
combine ShipTo and BillTo Address 3 22
data analyst 3 48
Access 2010 Query Syntax 5 21
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now