Write a Junxure Custom Application in Visual Studio

Creating a custom  Junxure Application with VB.net

Problem.

Download the Code for this project

This office manages accounts for advisors who do not work directly for them.  The people who own the accounts are not clients of this firm, but rather are cleints of the external Adivsory firm.  They need to be able to see the following information:

How much each individual Investor has in each account
How much each external advisor has under management with the main firm.
How much revenue was generated via these accounts.

For our example we will use the following people.
Steve Findlay – An external Advisor that the firm works with.
Alan Anderson – A client of the External Firm

Solution

The solution to this issue was a 2 part item.
First we needed to figure out a way to use Junxure to track this information.  To do this we did the following.

Added a user to Junxure with the same lastname and first name as the external advisor.
Created an Employee Position to indicate that a contact record in Junxure has an External Advisor.  The Employee Postion is called “External Advisor”
Go the the clients of the External Firm and add an employee position to them and mark their External Advisor as “Steve Findlay”.

2

Writing the custom Application.

In this article it is not my aim to show you how to use visual studio to write a custom application, I am assuming that if you are reading this, then you have some level of experience in using visual studio, or another programming language.  I am just trying to show you the possibilities of what you can do using Visual studio to enhance Junxure.  I am however including the source code for this project, so you can explore the solution further.

What we now need to do, is to find all of the Contacts in Junxure, that have an External Advisor, and present a grid that shows their accounts and the revenue for each one.
We will use two views that already existing in Junxure, vqryAccountTotals and vqryRevenueFeesPaid, since these views totals up the details of the accounts and clients revenue.
Here is a graphical representation of what we are trying to do.

We can choose whatever fields we want from the above tables, so to
create the sql for this, we will use the following fields.  We
will exclude the Junxure Manually added accounts since they would not
have revenue

and we would not want them in the results.

SELECT   dbo.tblClientEmpJobPositions.ClientID,
dbo.tblEmployees.FName,
dbo.tblEmployees.LName,
dbo.tblClients.LastName,
dbo.tblClients.FirstName,
dbo.vqryAccountTotals.AccTotal,
dbo.ContactsAccountsXref.Account#,
dbo.vqryRevenueFeesPaid.Inception,
dbo.vqryRevenueFeesPaid.YTD,
dbo.vqryRevenueFeesPaid.Last12,
dbo.vqryRevenueFeesPaid.Last3
FROM         dbo.tblEmployees RIGHT OUTER JOIN
dbo.vqryRevenueFeesPaid RIGHT OUTER JOIN
dbo.tblClients ON dbo.vqryRevenueFeesPaid.ClientID = dbo.tblClients.ID RIGHT OUTER JOIN
dbo.tblClientEmpJobPositions LEFT OUTER JOIN
dbo.tblLookJobPositions ON dbo.tblClientEmpJobPositions.JobPositionID = dbo.tblLookJobPositions.ID ON
dbo.tblClients.ID = dbo.tblClientEmpJobPositions.ClientID ON dbo.tblEmployees.EmpID = dbo.tblClientEmpJobPositions.EmpID LEFT OUTER JOIN
dbo.vqryAccountTotals RIGHT OUTER JOIN
dbo.ContactsAccountsXref ON dbo.vqryAccountTotals.CLTNO = dbo.ContactsAccountsXref.Account# ON dbo.tblClients.ID = dbo.ContactsAccountsXref.ContactID
WHERE     (dbo.tblLookJobPositions.JobPosition = N’External Advisor’) AND (NOT (dbo.ContactsAccountsXref.Account# LIKE N’Junxure%’))

This will return all of the records in Junxure that have an Employee

position of External Advisor, along with the totals in the accounts and
the revenue paid from those clients.

Now we need to make sure that when we are looking at the Junxure
Contact Record for the External Advisor of Steve Findlay, we are only
seeing the contacts that he is the External Advisor for.

Since our custom program will be launching in the context of the
current client in Junxure, we will not be able to open the custom query
showing the records for the Employee Steve Findlay.

In order to do that, we will need to convert his Junxure Client ID that
is show on the top of the client form, into the Junxure Employee ID for
the records that you added for Steve Findlay.

When we setup the Steve Findlay record we were careful to enter the
lastname and firstname the same in the Employee form as it was in the
Client form.  So we will have to do a lookup to find the Employee
id, like is shown in this diagram.

We will pass the ClientID into the new program and lookup in the
employees table for the same lastname, firstname, returning the
employee ID.  We will then filter the above sql statement to only
show the people where Steve Findlay is and External Advisor.

Junxure has the ability to launch custom programs and pass in context
sensitive information as a command line argument.  We will setup a
custom launch command to launch our CustomAdvisorReport.exe and pass in
the argument of <ID> for the current client.

This will launch our CustomAdvisorReport.exe program and it will also
pass in the ID as an argument.

It will be up to our program to read the argument and do everything
else.

So on the form load we read the arguments, and since there should only
be one, we will assign that argument to the ClientID variable.

We will use the Environment.GetCommandLineArgs to get this
value.   The GetCommandLineArgs always includes the program
name as the first element, so we will check if there are 2 arguments
and if so, we will stuff the second argument into the client ID

Dim arr() As String
arr = Environment.GetCommandLineArgs
If arr.Length <> 2 Then
MsgBox(“You need to supply a ClientID to report on”)
End If
clientID = arr(1)

Once we have the client ID, we will need to get the firstname and
lastname so we can do a lookup in the clients table.

We will use a bit of SQL to get the Employee ID.

SELECT     dbo.tblEmployees.EmpID
FROM
dbo.tblEmployees INNER JOIN
dbo.tblClients ON dbo.tblEmployees.FName = dbo.tblClients.FirstName AND dbo.tblEmployees.LName = dbo.tblClients.LastName
WHERE     (dbo.tblClients.ID = 2620)

This will return the EmpID for the user in Junxure that we
entered to match this employee.  We will now apply that Employee
crtieria to only show the accounts that match that Employee.

You can download the sample code for this application here.
Download the Code for this project

There are a few things that you will need to be aware of when using
this sample code.

We have a class that is called SqlServer.vb and it uses the connection
string that is defined in the external.config.  If you are going
to use this application as a framework for additional custom
programming, you will need to copy your external.config from
c:\junxure\codedn into the debug folder so it will know how to connect
to your database.  Or you can just edit the one that comes with
this zip file.  The line you want to change is the line that looks
like this.

<add
name=”Junxure” connectionString=”Data Source=i7\SQLExpress;Initial Catalog=Junxure;Integrated Security=True” providerName=”System.Data.SqlClient” />

You will have to change the Highligted portion to reflect your server
and instance name as it is on your network.

Once we get the data to display we are just loading it into a grid.

Additional Implementation.

We added another button to display all of the externally managed
clients, instead of the one that you were on in Junxure.  It is
basically the same code, but we skip the filtering by client ID.

Once we get the data to display we are just loading it into a grid.

We added some code to open the client when you double click the row.

We added a button to open the Advisor when you click that button.

Adding a Button to the Custom Database

 

Hooking up the custom database to the Junxure Client Form

In Junxure, you have the ability to hook up a custom Access database to the
client form.  Once it is hooked up, you will have a new button on the top
left of the client form.

Clicking this button will open the custom database to the form that you have
specified in the options settings.  The beauty of this, is that you can
then build the form in your custom database to open for the client that you are
on in Junxure, allowing you to extend Junxure in a way that is seamless to the
users.

I will give you step by step instructions for completing this whole process. 
So let’s get started.

Setup the Options.

You will need to turn on the option to display this button, and then you will
need to specify a form to open in the custom access database.  In this
example, I am going to use the frmClients as the form that I want to open, but
that form actually does not exist in the access database.  We are going to
create it later.  For now, just turn on the option by going to Maintain
System, System Options, Global Options , custom and checking the box and adding
“frmClients” to the form name.

No that you have turned on this option, Junxure will open the custom database
and then open the frmClients.  Since you probably don’t have a frmClients,
you will probably get an error if you were to try this right now.  You will
first need to configure your custom database.

Custom Database Location

The custom database is located in

 

 

c:\junxure\code and it is called customJunxure.mdb.  If you have a
newer version of Junxure, you may not have either that folder or that file. 
If it is there, you can open it now.  If you don’t have it I have provided
the file that I am building in this example, so you can download it and it will
already have the form that I am showing you how to build.  You can download
a copy of one here.  This is a self extracting zip file that will extract
to the proper folder.
CustomJunxure.exe

 

 Once you have the c:\junxure\code\customjunxure.mdb file on your system, double-click on it to
open it and you will see this screen. 

 

 

This is the custom database screen that I have created, but you are not forced
to use this database.  You can actually use any access.mdb file in place of
this file as long as it is named customjunxure.mdb.

In this blog entry, I am going to assume that you are using this file. 
Before you will be able to “read” any of the Junxure data, you will need to be
sure you have configured and ODBC Connection to your Junxure database. 
Here is a link that has directions for setting up and ODBC connection. 

http://www.junxure.com/kb/ViewArticle.aspx?ArticleID=151

Now lets get started setting up the frmClients in the custom database. 
The first thing you will notice in this custom database, is that you are
basically “Locked” out of the design of the menu and you probably cannot figure
out how to get to the “database Container” in the database.  We hide it
when this opens, and to see the database container, you will click “F11″. 
That will open the database container and you will see something like this, if
you are using Access 2003, and something a bit different if you are using
another version of access.  It all works the same but the interface is a
bit different.  Go ahead and hit “F11″ with the open database.

Here you will see a list on the left that allows you to select the different
object types in the database.  In this demo, we will be working with
Tables, queries, forms and Modules.  Don’t worry about the modules, there
is only one thing that we need to do and I will walk you thru it.

Refresh Table Links

The next thing that we will want to do is to refresh the table links. 
This requires that you have the link table manager installed in your access
version.  If you did not install the link table manager, you can drop and
reattach all the tables, but I would recommend that you install the link table
manager when prompted if you do not have it installed.

Click on Tools |  Database Utilities | Linked Table Manager and that
will open the linked table manager. 

If you are using Access 2007 it is on the Ribbon on the Database Tools Tab

It will look like this. 

Click Select all, then click OK.  If your ODBC connection is properly
setup, it will refresh all of the links and this database will now be able to
view the data in Junxure. 

 

 

Caution: When you are looking at “DATA” in the custom database, you are actually looking at data in Junxure’s SQL database and any changes or deletions made here, will also be made in Junxure.

 

 

 

 

 

 

 In order to assist you in really getting the most benefit out of the custom
database, we have build a “hook” into Junxure to allow you to see the what
client your are on in Junxure and what employee you are logged in as.  In
order to take advantage of this, you will need to create a module that will call
some functions that we have created.  Don’t worry if you don’t understand
this, just follow these steps.

 

1.  Click on the Modules entry in the database container.

2 Click the New button on the top

This will open a module window and it will be called Module 1.  It is in
this module that we will want to “Paste” in some code. 

3.  Copy this code below and paste it into the window, below the “Option
Compare Database” text that appears in the module windows.  The code is
between the horizontal lines, not including the lines.

---

 

Function getCurrentID()    Dim o As Object     Set o = CreateObject("JxPublicObject.clsPublicObject")     Dim msg As String     msg = o.GetCurrentInfo     Dim ClientID     Dim emp     If msg & "" = "" Then 'added for testing, if clientform is not open, I return 1187, you can change this for your favorite client     getCurrentID = 1187     Exit Function   End If         If InStr(msg, ",") >= 0 Then      ClientID = Right(msg, Len(msg) - InStr(msg, ","))      EmpID = Left(msg, InStr(msg, ",") - 1)      Else      ClientID = 0     End If     getCurrentID = ClientID End Function Function getCurrentEMPID()    Dim o As Object     Set o = CreateObject("JxPublicObject.clsPublicObject")         'I am not sure why this is not regestring     Dim msg As String     msg = o.GetCurrentInfo     Dim ClientID     Dim emp     If InStr(msg, ",") >= 0 Then      ClientID = Right(msg, Len(msg) - InStr(msg, ","))      EmpID = Left(msg, InStr(msg, ",") - 1)      Else      ClientID = 0     End If     getCurrentEMPID = EmpID End Function

 

 

---

 

 

 

 

When you paste in that code it should look something like this. 

If you only see the first function, don’t worry, you may have the editor
setup to only show one function at a time.

 We are going to test it in a second so just continue along.

Testing the Functions

Open Junxure and Go to a client in Junxure.  With the client form open,
we want to get the ID of the client that you are on.

Leave Junxure open and go back to the code windows.  Look for the window
that is labeled Immediate Window. 

If you do not see it in the code window you can click on “View”
Immediate
Window

In the immediate window, type the following.  A question mark followed
by getCurrentID() and hit enter. 

You should see the current ID from Junxure below what you typed.  If
that works, you now have the ability to get the current client ID.  Now
lets test it for the current logged on employee.

Type ?GetCurrentEMPID() and hit return

You should see the employee ID.  If you get any errors, you need to
check the following.

1. Junxure is started and Running.

2. The client form is open and on a client.

3. You have installed the Junxure Public Object.  This is found in

C:\Junxure\codeDN\JxPublicObject\SetupJxPublicObject.msi.  If you have
done number 1 and 2, then run this MSI and try again.  If it still does not
work, then you have a problem and you should call support, they can help you
with getting the public object installed.

Save The Module.

Click the save Icon and then follow the prompts to save the module, then
close the module window by clicking the X in the top right of the window.

Build a query to return the current Client’s Information

1. Return to the database container, and click on Queries.

2. Click the new button like you did for module.  This will open a query
designer dialog.

Select Design view and click OK, this will open a table selection dialog.

Select tblClients and click OK and Close.  This will take you to the
query designer with tblClients at the top of the designer.

Double click on the any fields that you think you may want to use, but for
this demo, we are going to use these fields.

ID
Lastname
Firstname

This will give you something that looks like this.

Now what we want to do, is filter this query to only
return the client that we have open in Junxure.  To do that we are going to
add criteria to the ID field.

In the cell under ID and to the right of Criteria, you
are going to type =getcurrentID() and that will call the function that we made
in previous steps.

Once you are done, it will look like this.

To test this, you can click on the menu bar “View” and
“Datasheet View’ and you should see the client that you have open in Junuxre.

Now click the save Icon and save this query as “qryClients”

close the query by clicking the X in the top right
corner.

Building the Form

From the database container, click on forms and click
new

Click Autoform:Columnar and put qryClient in the box at
the bottom, then click OK.  This will create a form that shows the fields
that you selected in your query.

The newly designed form should look like this.

You now have a form that will open to your open client
in Junxure.  We will do a few things to this form and you will be well on
your way to being able to use the custom database.

Click on View | Design View from the menu. This will put
the form in Design View.

I am going to do the following.

1. Make the form a bit bigger.

Drag the bottom right corner of the form to make it a
bit bigger.  I would make it about an inch taller and an inch wider for
now.  You can always design it to your hearts content later.

2. Resize the white text boxes to be the same size

Select the three white boxes and use the menu “Format”
size To Shortest

This will make them all the same size.

3. put a button on the form to close the application.

Find the toolbox and select a button from the toolbox. 
If you don’t see the toolbox, you can click View Toolbox from the menu and it
will toggle it on and off.  Find the button that will make a button on the
toolbox, click it and then draw a button on your form.  It does not matter
where you draw it, just put it on the form somewhere. 

that will pop up a dialog box, that will help you tell
Access what you want that button to do when it is clicked.

Select Application, Quit Application, and then click
Finish, you will then have a button on your form, that when clicked will close
the application, returning you back to Junxure.

4. Save the form as frmClients.

Click the same save button you used above to save the
form.  It will want to save it as qryClients, since that is what you based
the form off of, but you want to change that to frmClients to match what you
have in Junxure on your options page.

5. Put some code on the form to maximize it when it
opens.

Find the box in the top right of the form designer and
double click it. Select the event tab, then click in the On Open cell to reveal
the ellipsis button then click the ellipsis button to open the code builder.

Double click the code builder item in the list box.

This will open the code editor with a new OnOpen event
for the form.  Type the following in that event so it looks like this

That will cause the form to maximize when you open it. 
Now close the code editor by clicking the X at the top.

Close the toolbox and the properties windows and then 
close the form by clicking the X at the top of it. 

Be sure to SAVE everything when you are prompted.

Be sure to close the Access Custom Database and exit
Access or it will not be able to open the form when you click the button to test
it.

Testing the database from Within Junxure

Return to Junxure and open the client form to a client
of your choosing.  I choose to open the form for Ken Golding

Now click the button for that the arrow is pointing to
and it will open the custom database to the client that you have open in Junxure

Now you click the stop sign button and you will return
to Junxure.

Summary

This gives the user a seamless hook into your custom
database.  While this demo does not give you much in terms of
functionality, it does demonstrate that you can easily hook into Junxure to open
your Access database to the form that you decide, and if you write reports in
that custom database, and those reports are for the displayed client, you can
really extend Junxure allowing you to design your own reports and you need.

For more information on creating reports in Access, you
can see my previous blog posts, or just google microsoft access report designer
tutorial for a list of resources.
Click
here for Link

 

Code to find the current client in Junxure

Creating a custom report (Part 3) Where’s the data.

As you probably already know, Junxure keeps all of the data inside of a Microsoft SQL server database. This database contains a number of tables, and if you know where to look and how to ask for the data, you can pull anything and everything out of the database for your own custom reports. I will try to show you the tables and the relationships for the main parts of the program. Here is what I will cover.  While I am not going to cover all of the tables below, you will see that the clients tables are prefaced with tblclients so you should be able to figure them out pretty easily

Clients – tblClients
Phones -tblClientPhones
Addresses -tblClientAddresses
Emails – tblClientEmailAdds
Classifications -tblClientClassifications
Keywords – tblClientkeywords

Actions -tblClientActions
Emails – tblClientActionsEmails
Attachments – tblClientActionsEmailAttachments
Documents – tblClientActionsDocuments

Accounts – tblClientDBCamIDs
Owners -ContactAccountXref
Assets – FAS-Assets

Hopefully this will help you find where the data is for your custom reports.

Clients

The main data for the clients is stored in tblClients.  In that table you will find all of the fields that pertain to any contact in your database.  Since Junxure is a product that has been evolving since since 1995, there are a few things that you should not about this table.  There are some fields that we no longer use, those fields were left in the table, because many of our clients have written custom reports that use that table, and if we removed the fields, many of their reports would have been broken.  We decided to leave all of the phone, email and address fields in the table, even though we no longer use them.    All of that data was moved to the appropriate tables as show in the photo below.

If you look in the center of the picture, you will see the clients table.  The Primary key and main identifier for each client is the ID fields.  Each client can have unlimited records in each of the tables that the arrows are pointing to.  The 3 tables highlighted in blue are the tables that hold the phone, email and addresses on the contact info tab in Junxure.  The tables highlighted in Yellow, are the tables that hold the data in the bottom part of the profile tab.

Here is a diagram that shows the relationships between employees and the clients.

Actions

The actions are contained in a main table, and the emails and documents are located in additional tables.  Here is a diagram that shows the relationships for the actions.

tblClientActions -In the diagram below, you can see the white box in the center is the main action table.   It contains the main part of the actions.

tblClientActionKeywords -The pink table is the action keywords table, and you can have many records in this table for each action.

tblClients -The purple table is the clients table, and it is related to the actions via the client ID field.

tblAlerts -The darker yellow table is the alerts, and if an action has a task assigned to it, then an alert is placed into this table.  Once alerts are completed, they are deleted from this table

tblClientActionsFYi -The light yellow table at the top left is where we store the FYI’s for this actions.

tblClientActionsEmails and tblClientActionsEmailAttachments -The Green table are the emails and the email attachments.  Each action can have multiple emails, and each email can have multiple attachments.

tblClientActionsDocuments -Finally the blue table is the table where we store the documents.  We do not actually store the document in the database, but we store the path to the document in the filename fields.

Also on actions you may be interested in seeing what employees are related to the action.  Here is the diagram for that.

Accounts and Assets

Accounts and Assets are a bit harder to comprehend and because of the way Junxure has evolved, the naming on these tables is a bit strange.  One thing to remember is that Junxure, in it’s evolution, now allows multiple clients to own an account, so the accounts table is not really associated with the clients at all, that is where we use the cross reference table.  To help you understand how it is all related, it would help if you think of these 4 entities, and then look at the names of the tables that hold these entities.

Clients  – tblClients

Accounts – tblClientDBCamIds

Owners – ContactsAccountsXREF

Assets – FAS-ASSETS

Looking at the picture below you can see that each assets belongs to an account.  Each account has owners, and each owner is a record on the clients table.

Asset Fields

The field names on the asset form do not match the field names in the tables.  Here is a screen shot that shows what the actual field names are for the asset form.

This should answer many questions you may have about the location of the data and the how the data is related.  In my next post, I will try to show you how you can “Hook” the custom database to the Junxure database using the Junxure Public Object.

Creating A Custom Report (Part 2) – Designing the report

Creating custom reports in Junxure (Part 2) The Reports

Recap

In the previous post,  I covered the most basic parts of Access, and described how Access has 2 parts, the database and the programming environment.  Using the first part to hook up to the data, you can then build queries to extract the data that you want.  You can use the queries to limit what fields you see and how that data is “Joined” together to show you want you want.  You can also put filters on the queries so you further limit what is returned from the database.    Here is a link to Part 1

Creating the Report

When you create a query that displays the fields that you want and contains the appropriate filtering, you can look at it in “Datasheet view”, and see what data is returned.   It will look something like this.

This is great for making a simple list of data, but if you want a printable, formatted report, you will need to do a bit more work.  The good news is that you can leverage all of the work you did in creating the query, all you need to do is to create a report that pulls it’s data from your query.

I will show you a step by step creation of a report using the query that we made in the previous blog entry.  In the sample database that I have provided, I have called the query qryDemoActions.

If you need a copy of the custom database you can get it http://s3.amazonaws.com/Junxurebasics/CustomDemo.mdb

Finding the Report Wizard

You will need to open the database container.  There are a couple of ways to accomplish this.   Hit {F11} and it should open up, or you can click on the menu item Window | Database,  (If you do not see Database on the window menu, you will have to click Window | Unhide Window to make it visible).  Once you have the database container open, you will click on the reports tab, and then click New Report.  That will open the starting page of the report builder.

Here there are 6 items that you can select from

• Design View – This will just open a report in design view and you will have to place all content on the report.  Access does nothing for you.
• Report Wizard – This will walk you thru some choices and you will wind up with a basic report,  ready for additional editing.  (This is the one we are going to walk thru.)
• Auto Report: Columnar – This option will create a report with each field entered one on top of another, with the labels going down the left side of the page and the data on the right.
• Auto Report: Tabular – This option will create a report similar to a spreadsheet, but you will be able to edit it further.
• Chart Wizard – If you query had data that was suitable for charting, you could select his and create a chart.
• Label Wizard – This option will allow you to use the Access label wizard and create many different labels.

In our case you will select Report Wizard at the top, then select the query that we built in the previous blog entry.  qryDemoActions.  That tells Access that you want it to create the report automatically for you.  Once you click OK you will then get a screen where you can select the fields that you want in your report.

On this form, on the left side you will see all of the fields in the query.   You can double click on any of the fields to add them to the report.  Once added to the report, they will disappear from the left column and appear in the right column.  In the above screen shot, I have selected all of the fields, so Access will create controls for each field on the report.  I will discuss the controls a bit later in this entry, but you should know that Access will place a text box on the report and when you run the form, the data for that field will appear in the text box.  When you click Next from this screen, you will be prompted for any grouping that you would like on the report.  Grouping is useful when you want to group report records under a particular field.  For instance, I would like to group all of the actions in the qryDemoActions by who they have been assigned to.

By selecting Assigned to, my report will have a group header and the records in the query will be grouped by the person who they have been assigned to.  This will make it easy to see all of the record by each employee.  You can select additional groups if you like, but for this example we are going to stick to one grouping.  When you click next you will be prompted for the sorting.  Again you can select multiple sorting levels.

Here we have chosen to sort by Last name, First name and Date.   This will produce a report that is grouped by the employee the actions are assigned to and then sorted by clients in last name, first name order, then in date order.  You can change the order of any field so it is either ascending or descending order, so if you want the oldest actions at the top of each group, you would select Descending order.   When you click next you will be prompted for the alignment of the fields within the report.  You can try each setting to see how it will look, but other than cosmetic purposes, it does not matter what you select on this screen.  You can also choose between landscape or portrait, along with forcing all of the fields to appear on one page.  If you force all fields to appear on one page, you may have to do some adjusting to make it look right.

When you click next you will be prompted for a format for your report.  Access provides several different formats and you can choose any of them.  You will just have to play around with each one to see what style you prefer.

Finally, once you have selected your style, you are ready to create your report.  When you click finish, you will view your report in design view with the data from your query, and if you were to print it, that is exactly what it would look like.  Let’s click Finish and see what it looks like.

Modifying your Report

If you click on View Design view, you will be taken from the above print preview view into design view of the report.  In design view you will be able to edit the look and feel of the report.

In the above screen shot there are several things that I have pointed out.

• Pink Arrow at top left – This is how you can switch back and forth from design view to print preview.  Click the little arrow and select either design or print preview.
• Red Arrows -  This is the button that toggles the field chooser on and off.  You can drag any field from this list and it will be linked up to the query behind the report.  Notice where the black arrow is pointing to the lastname field.  That is a field that Access created for you, but if you were to drag it to the report again, you would get something that looked just like that.
• Green Arrow – This toggles the toolbox on and off.   There are a number of controls in the toolbox.  The top two icons, highlighted in orange in this screenshot  are the selector and the autowizard tool.  The rest are listed below going across then down.
  • Large Italic Aa This is a label control and it allows you to type any text in it, an that text will just show up on the report.  The top control on the report, that says “Actions By Assigned to Employee for 2000″ is a label control and I have set the text in it to what I wanted.  Changing the reports underlying data does not change the contents of a label control
  • ab|  – This is a textbox control and generally it will display the underlying data if you bind it to the field.  If you look at the properties box for the lastname textbox, you will see that the control source is set to lastname. This tells access that when you run this report, take the data from the lastname and display it in this box.
  • Small box with xyz on top.  This is what you call a group by box.  We are not using it here, but it can be used to hold buttons  or checkboxes along with grouping controls for cosmetic reasons.
  • Toggle button – This can be bound to a yes no field and it will either display a depressed button or a raised button depending on the selection.
  • Radio button – These are usually placed inside of a group by box, and only one can be selected.
  • Check Box – This is usually bound to a yes not field, like I did on the action required field in this report.  If the action had action required, you will see a checked box, otherwise you will see an empty box.
  • Combo Box – This is a drop down control that holds both the value for the field and a list of items that you can choose from.
  • List box – Similar to a drop down, but it is a list of items and the selected item will be highlighted.
  • Button – Not usually used on reports, but it is just a button that you can click.
  • Image control – This is a control that you can bind to an image.  If you wanted a logo on your report you could place an image control and then set the properties for the path to the image.
  • Picture box – Similar to an image control but with some other options.  The image control is the lighter control and will give better performance.
  • The picture with the xyz is a bound object frame and you can use it to display an object, like a word document inside of your report.  Typically you do not use this item and there are a lot of things that you have to setup to make sure it works properly, including having the proper program installed to display the object.
  • Page Break – This will force a page break wherever you place this control.
  • Tab Control – This is a control that give you selectable tabs.  It is useful in a report for showing only certain controls at a certain time.  (This is a more advanced control)
  • Subform / Report  – This is a powerful control, that allows you to place another report inside of an existing report.  For instance, for the assigned employee, you could have a separate report that shows all of the information for that employee.   It would actually run the report for each employee and only show the data for the employee that it is linked to.   For more information, I would google Access Subreports to learn more.
  • Diaganol Line  – This allows you to draw lines on your report.  Notice in our report we have several lines.  The best way to make a line totally flat is to select it and set the height to 0.
  • Square – This allows you to draw squares on your report.
  • Toolbox – This is a link to additional active x controls on your system.
• Black Arrows – This button toggles the property pages for the selected control.  The property pages are where you can set the value of properties on the control.  Properties can affect the behavior and appearance of a control.  Here are some examples.
  • Name – This is what you will refer to this control as, when you are in code or macros.
  • control Source – This tells Access what fields contains the data for this control
  • Decimal Places – Used when the control contains numeric data.  You can accomplish rounding to 2 decimal places by setting this to a 2
  • Can Shrink and Can Grow – Allows the control to either get taller or shorted depending on how much data is in the contro.  If you have a note field that can contain anywhere from no data to paragraphs, then setting this to Can Grow=True will allow it to display all of the data without having blank spaces in records with only a small amount of data.
  • Font Bold Size etc,  changes the size and attributes of the font.
  • Visible – If you can see the control in print or print preview mode.
  • Text Align – Left Center or right justified.
  • Width and height These set the size of the control
  • Others – there are many other properties but this covers the most used ones.
• Green box in lower left – This is a text box that was dropped on the report, and =Now() was added as the control source.  It will cause the report to print the current date and time when the report is printed.
• Blue text box at the lower right.  This is a special code called [Page] and [Pages].  Access is smart enough to know how many pages of data there will be when the report is printed and what page it is on.  Using these special codes you can display that information on  your report

Additional Formatting

Now you should have enough information to get started playing around with the format of your report.   You can try changing the size, alignment etc and see if you can make it look exactly like what you are wanting it to look like.