Solved

Change title field based on detail record

Posted on 2012-03-28
15
394 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
[X]
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
15 Comments
 
LVL 77

Expert Comment

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

Author Comment

by:IBMJunkman
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.
0
 
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.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

Expert Comment

by:Jeffrey Coachman
ID: 37778301
Her is my take on this:
Database103.mdb
0
 

Author Comment

by:IBMJunkman
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.
0
 
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
https://filedb.experts-exchange.com/incoming/ee-stuff/8239-Database103.mdb
0
 
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.
0
 

Author Comment

by:IBMJunkman
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.
Database103b.mdb
0
 
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?
...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
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.
0
 
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

;-)

JeffCoachman

JeffCoachman
Database103.mdb
0
 

Author Comment

by:IBMJunkman
ID: 37783957
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
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:
=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
ID: 37787726
;-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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…
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…
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…

756 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