Access 2010 subreport textbox

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?
Who is Participating?
Oliver WastellConnect With a Mentor MemberCommented:
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.)
Oliver WastellMemberCommented:
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.
MonkeyPieAuthor Commented:
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?
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

MonkeyPieAuthor Commented:
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?
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
Oliver WastellMemberCommented:
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)

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.
MonkeyPieAuthor Commented:
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?
MonkeyPieAuthor Commented:
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.
Oliver WastellMemberCommented:
Thanks for the points and grading. I'm glad that you managed to come up with a simple solution that worked for you. Regards.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.