Solved

Change title field based on detail record

Posted on 2012-03-28
15
389 Views
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.
0
Comment
Question by:IBMJunkman
15 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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?
0
 

Author Comment

by:IBMJunkman
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
...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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
<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......
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Her is my take on this:
Database103.mdb
0
 

Author Comment

by:IBMJunkman
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
https://filedb.experts-exchange.com/incoming/ee-stuff/8239-Database103.mdb
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 50 total points
Comment Utility
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.
0
 

Author Comment

by:IBMJunkman
Comment Utility
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.
Database103b.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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?
...etc

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...
0
 

Author Comment

by:IBMJunkman
Comment Utility
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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

;-)

JeffCoachman

JeffCoachman
Database103.mdb
0
 

Author Comment

by:IBMJunkman
Comment Utility
Jeff,

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?
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 200 total points
Comment Utility
<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:
=IIf([Reports]![rptCustInfo]![CustomerType]="1",[CorpPhone1],[CorpPhone2])
(and reduce the size of the empty sections to eliminate the blank page at the end)

;-)

Jeff
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
;-)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

10 Experts available now in Live!

Get 1:1 Help Now