Solved

Access 2010 subreport textbox

Posted on 2014-03-31
8
798 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
  • 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 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

21 Experts available now in Live!

Get 1:1 Help Now