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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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
LVL 31

Assisted Solution

by:Helen Feddema
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

626 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