Access 2010 subreport textbox

Posted on 2014-03-31
Medium Priority
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?
Question by:MonkeyPie
  • 4
  • 4

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.

Author Comment

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?

Author Comment

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?
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.


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)

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.

Author Comment

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?

Accepted Solution

Oliver Wastell earned 1500 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.)

Author Closing Comment

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.

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.

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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