Change title field based on detail record

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
IBMJunkmanAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
...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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jeffrey CoachmanMIS LiasonCommented:
<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......
Jeffrey CoachmanMIS LiasonCommented:
Her is my take on this:
IBMJunkmanAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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
Helen FeddemaCommented:
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.
IBMJunkmanAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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...
IBMJunkmanAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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



IBMJunkmanAuthor Commented:

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?
Jeffrey CoachmanMIS LiasonCommented:
<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)



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.