Solved

Access 2010 subreport textbox

Posted on 2014-03-31
8
816 Views
Last Modified: 2014-04-03
I have a bound textbox in a subreport with can grow set to YES.  
The textbox shows client name for a report of job notes. So, for a client, there may be many jobs, and for each job there may be many notes.  The subreport source is a query that pulls all this together.  I have tested various combinations of job sub sub reports and note sub sub sub reports, but by far the fastest is the query mentioned above.
I do not want the client name repeated for multiple notes, but I do want client name to show for each new job.  Setting HIDE DUPLICATES to YES for client name is not good enough as it hides client name for multiple jobs, so I set txtClientName.Visible to TRUE/FALSE in the on detail format event.
There seems to be a bug in subreports for this combination of can grow=YES and dynamically setting visible flag - it sometimes does not complete the sub report group footer or sub report report footer for this combination.
If I set CAN GROW=FALSE then setting VISIBLE flag dynamically works perfectly.
So, how can I make Client Name invisible for repeat job notes and keep can grow=TRUE?
0
Comment
Question by:MonkeyPie
[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
  • 4
  • 4
8 Comments
 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 39968661
In continuous forms/reports you cannot hide the same field in some records and not others (a limitation of MS Access unfortunately).  What you will need to do is add a field in your query which identifies the record as being one that needs to display the client name.

Then in the report, rather than setting the Control Source for txtClientName to the client name field, set it to something like:
=IIF(displayClientName=true, clientName, "")
where displayClientName is the field added to the query as suggested above.
0
 

Author Comment

by:MonkeyPie
ID: 39970679
Thank you Oliver.  A good idea but I am struggling with how set displayClientName in my query.  Speed is an issue, but I really have no idea how to say :
displayClientName = this job number <> previous row job number
in a query.
Can you help any further?
0
 

Author Comment

by:MonkeyPie
ID: 39970690
I could keep a count of notes for current job in the query and set :
displayClientFlag=(note count=1)

but how do I keep a running count of notes per job in a query?
EG.
Client A, Job 1, note 1, 1
Client A, job 2, note 1, 1
Client A, job 2, note 2, 2
Client A, job 3, note 1, 1
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 39970716
I'm about to leave the office, but I can give you a quick steer and I'll happily help further tomorrow if you still haven't resolved.

The solution is to use a separate aggregate query (based on your existing query) which identifies the first record for each job number only.  Assuming that each record has a unique key, you can link your second query to your first via this unique key setting the relationship to show all records from your first query.  

Third query based on the first two which you should use for your report:
Query 1     Aggregate Query
PK---------->  PK (this field will only have value if it is the first row)
ClientID
JobID
NoteID

displayClientFlag can then be calculated based on the unique key field in the aggregate query, i.e. false if it is null, true if it is not null.
0
 

Author Comment

by:MonkeyPie
ID: 39973709
Yes - I see what you are suggesting, but this would tip the report render time  into the 'TOO LONG' basket from the client's point of view.

When you originally said "In continuous forms/reports you cannot hide the same field in some records and not others (a limitation of MS Access unfortunately). " what exactly did you mean?  It seems to me you CAN set the txt.visible flag dynamically in VBA, but just not if CAN GROW=YES.

Any other ideas?
0
 
LVL 3

Accepted Solution

by:
Oliver Wastell earned 500 total points
ID: 39974465
The aggregate query should run dead quick if it is optimised (i.e. no memo fields and all keys indexed).  When I said it should be based on your existing query, what I actually meant is that it should be similar to your existing query (i.e. same criteria etc) but it would make sense to have an optimised version for this purpose.

However, would the following format for your report not work? That would be a lot simpler to implement and wouldn't need any sub-reports:

Client A
Job 1, note 1
job 2, note 1
(job 2), note 2
job 3, note 1

Client B
...

Regarding my statement:
"In continuous forms/reports you cannot hide the same field in some records and not others (a limitation of MS Access unfortunately). "
Maybe I should have just stated 'forms' not 'forms/reports'.  It is certainly the case that if you try to hide, change the formatting, etc of a control in continuous forms, the change will be implemented to all the controls.  (When working with continuous forms, conditional formatting can however be used.)
0
 

Author Closing Comment

by:MonkeyPie
ID: 39976898
Oliver - thank for your help. I can't implement your suggestion as I must stick to exact report format client has requested.  However, I have come up with a solution.
I create one textbox with the control source=[ClientName] & CHR(10) & CHR(13) & [JobName].  I then set CAN GROW=YES and HIDE DUPLICATES=YES.  

This will then repeat client name for subsequent jobs since job name will differ, which was what I wanted.

However, your suggestion is sound, so happy to accept as solution. Thank you.
0
 
LVL 3

Expert Comment

by:Oliver Wastell
ID: 39976907
Thanks for the points and grading. I'm glad that you managed to come up with a simple solution that worked for you. Regards.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

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…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

630 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