Solved

Access Form Formatting Labels

Posted on 2012-04-11
12
223 Views
Last Modified: 2012-04-11
Good Morning!

I have what seems to be to be a tricky problem and I am not sure there is a solution.

I have created a report in Access with 3 grouping levels: toID, stoID, actID. It is sorted in the last group by the actType.

It looks a bit like this:

AV: Title
         01: Title
                Employee Name
                                         Activity
                                         Activity
                                         Activity



The activities listed (actID) are divided into tasks and meetings. They are sorted so that tasks will appear first on the list.

What I need is for a label to show up at the beginning of the list that says "Tasks" for all of those with the taskID of 1. Then I need a label that says "Meetings" for those that have a taskID of 2. I need the label to only show up once, so that it looks something like this:

AV: Title
         01: Title
                Employee Name
                    Tasks:           Activity
                                         Activity
                     Meetings:     Activity

Is this possible?

Thank you!
0
Comment
Question by:Megin
  • 6
  • 4
  • 2
12 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37833373
How about just posting a sample of this DB?
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

My fist guess is that you need a textbox to display this text.
Then use code like this as the controlsource for this textbox:
If TaskID=1 then
   SomeControl="Task"
ElseIf TaskID=2 then
    SomeControl="Meeting"
End if

Or, ...do something like this as the Controlsource in the control directly:
=IIF([TaskID]=1,"Task","Meeting")
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37833396
Why not just add an ActivityType with values (Tasks and Meetings) (it could be computed based on some other field) field to the query that is the source for the report.  You obviously already have some way of identifying that.  Then, instead of a label, you could use a textbox to display that value.

If need be, you could use the detail sections Format event to keep track of the employee name and Task type for each record, and use that to hide/reveal that textbox for only the first activity type within each employee, something like:

Private Sub Detail_Format

    Static strPrevEmployee as string
    Static strPrevActType as string
    dim bDisplay as boolean

    if me.txt_Employee <> strPrevEmployee then
        bDisplay = true
        strPrevEmployee = me.txt_Employee
        strPrevActType = me.txt_ActivityType
    elseif me.txt_ActivityType <> strPrevActType then
        bDisplay = true
        strPrevActType = me.txt_ActivityType
    endif

     me.txt_ActivityType.visible = bDisplay

End Sub

NOTE: if you use this technique, you will need to use the acPrintPreview option of the OpenReport method, because code behind the report will not fire if you use the ReportView option.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37833445
Try this:
Database116.mdb
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 37833497
Or, pull the TaskType into the report's Record source.

See the new report in the attached sample
Database116.mdb
0
 

Author Comment

by:Megin
ID: 37833548
Boag2000, that is exactly what I want it to do!

I may have made the entire thing harder than it has to be. I am going to try to upload the database so you can see it. It is going to take me a few minutes though, as I have to take out all the personal information.

Thank you for working on this for me!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37833568
Finally, just a note.

You may want to consider later changing your field name:
TasKID1=Task
TaskID2=Meeting

This is like saying:
StateID1=State
StateID2=Country
or
BuildingID1=Building
BuildingID2=Garage

Perhaps you should call your field "EventID", then the other field would be EventType (Task or Meeting)

In my two example, I would change my field to: LandParcelD, and StructureID, respectively...
0
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.

 

Author Comment

by:Megin
ID: 37833674
Attached is my database.

The report that is an issue is the rpt_Weekly.

To pull up the sample activities, when it asks for start and end date, please enter 4/6/12 for both.


Let me know if this works.

THANK YOU!
DatabaseDemo.accdb
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37833685
Jeff,

Looks like you've got this covered, and since I cannot download the database, I will leave this in your capable hands!

Dale
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37833871
Megin,

From what you posted, I don't really see the need for subreports...

In any event, you can take what I did in my sample and apply it to your database.
0
 

Author Comment

by:Megin
ID: 37833911
I was wondering about the subreport thing too. This is why they are there:

I need to have the employees grouped in SubTask Orders. The activities are then grouped under these subtask orders.

On the report, I need the subtask order listed once, all those who are part of that subtask order listed together, once. Then I need all of the activities listed, once. When I was doing this other ways I would end up with the employees listed multiple times or the activities listed multiple times.

When I do what it looks like you did in your example, the text box repeats itself for every activity.

Task: act1
Task: Act 2
Meeting: act 3
Meeting: act 4

I need it to only list it once, like you did in your example.

Yours looks simple, like I wanted mine to be, but I wasn't able to make it work that way when I was building it. Though, I am still not very good at this so there is a good chance that what I have done is wrong.
0
 

Author Comment

by:Megin
ID: 37833949
I got it!  Everything is going to be okay.

Thank you for your help!!!!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37833960
OK, then the main issue here (creating a prefix based on the task ID) seems to be resolved...

You can post a new Question on the report design.
Remember to post a sample of the DB following the steps I outlined in my first post...
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

757 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

18 Experts available now in Live!

Get 1:1 Help Now