Solved

Access 2010 subreport textbox

Posted on 2014-03-31
8
796 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

762 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