Pluralsight blog Where devs, IT admins & creative pros go for news, tips, videos and more.
Pluralsight + Digital-Tutors - 3,000 tech & creative courses - starting at $29/month Get it now →
June 28, 2013

Printing SharePoint 2010 list items using Access 2007

By

Printing something from SharePoint isn’t as easy as hitting “print.” It has no print button. So what then can we do if we need to print some data from it? What if we need to print some records based on one or more criteria, or print a single item from a SharePoint list?

One answer would be to deploy a solution and feature to the SharePoint site by writing some JavaScript, some .xml and .aspx files and integrating and loading these codes and files to our SharePoint sites.
But this is a far too complicated solution and one that does not offer us the flexibility to customize the printed data or to add design elements like a company logo or header on reports.

A better, quicker way to print data from SharePoint is to use Microsoft Access. Since the two software products are of the same company, they work and interact with each other by default very easily. There’s no need to learn programming, write code, execute scripts, etc. There’s a simple solution where you can just drag and drop Microsoft Access 2007 objects.

To demonstrate how you can do this, I’ve created a scenario: A fictitious company named “REPAIR IT” offers services for its clients such as repairing devices like PCs, laptops, photocopiers and smartphones. The company employs the following four people:

  • Receptionist: This person tracks the physical devices with problems from clients, and registers a ticket for them in a SharePoint list.
  • PC Tech: This technician repairs the problems/issues related with PCs, laptops, etc.
  • Photocopies Tech: This technician fixes and repairs problems with photocopiers, printers, faxes, etc.
  • Smartphone Tech: this technician fixes and repairs problems related to smartphones.

For these four, we’ve created user accounts in Active Directory as in the below picture:

created AD user accounts

Also, we have created one SharePoint List that has these columns:

Sharepoint list columns

Basically, this is the work process:

  1. The client brings to the company one device that must be repaired, fixed, troubleshoot.
  2. The receptionist picks up the device, takes notes about the symptoms, and also notes the client’s name and phone number.
  3. The receptionist registers the ticket to SharePoint, and all the technicians receive an automated email (Exchange 2010 receives email from SharePoint lists).
  4. Based on the device, the right technician begins his/her work by inspecting and repairing the device.
  5. When the technician repairs the device, or the device could not be repaired due to damages, he makes the notes to the SharePoint list item.  Receptionist automatically receives an email and sees the new status of the ticket online.
  6. Next, the receptionist notifies the client to pick up the device after services are finished.
  7. When the client arrives, the receptionist NEEDS TO PRINT THE INVOICE OF SERVICE.

Now, here comes the question: How can you print the item directly from SharePoint? We have to use our old friend Access 2007.

As we know, Microsoft Access has these main Objects, and we’ll use each of them in this way:

  • Table: This is where the data is stored, and will be generated automatically from the SharePoint list.
  • Query: Use this to ask/retrieve some data based on one or more criteria. We’ll use the criteria to print the current item that is open in our form.
  • Form: Users will interact with this GUI, and use it for its day to day work with the database. Our form will show items from SharePoint List.
  • Report: As the name suggests, it generates/prints data. We’ll use this to print the invoice for client for the current item.
  • Macro: This tool automates tasks and adds functionality to forms, reports and controls.  We’ll attach our macro to a button on our form with the event “OnClick” that will open the report and print it.
  • VBA: We’ll not use in this example.

Let’s go over the steps we’ll use, and how and why our objects are used.

Printing a list item: Step-by-step

Create a MS Access database linked to a SharePoint list, by opening the list with Microsoft Office (we have to save the database somewhere on our computer).

Create a MS Access database

Create a form called “frmPrintCurrentItem” based on the table generated from the SharePoint list. It has the same name as the list, and in our case the table name is “IT Device Services”.

And choose the fields that you want to display in the form, then continue with the default next, next finish.

Choose fields to display

Create a query called “qryPrintCurrentItem” based on all fields of the automatically generated table from the SharePoint list, and add a criteria for one of fields that could act as primary key (like “Nr”) to be the same with the one in current form “frmPrintCurrentItem.”

Create a query

Create a new report called “rptPrintCurrentItem,” and choose as Table/Query the query we just created above.

Create a new report

Click the Next button three times; select the Columnar Layout, and then finish.

Columnar layout

Let’s create one macro which will be attached later to one button that we’ll create on our form “frmPrintCurrentItem”. This macro will open the report “rptPrintCurrentItem”. For this example, we have to choose the View mode “Print Preview”, as we don’t have a physical printer connected to the computer, but we want to see the generated report on the screen.

Create a macro

Still in our Microsoft Access 2007 Database, we have created on our own these objects (except the default tables that are created from SharePoint):

  • One query named “qryPrintCurrentItem”
  • One form named “frmPrintCurrentItem”
  • One report named “rptPrintCurrentItem”
  • One macro named “mcrPrintCurrentItem”

Objects created

Modifying the form

Let’s modify our form, and add one button named “btnPrintCurrentItem” to execute the macro “mcrPrintCurrentItem” that we created. Open the form in Design View by right-clicking on the Form and choosing Design View.

Form in design view

Then, follow these tasks in order:

  1. Click the button control.
  2. Click somewhere on the form where we want to put the button.
  3. Then from the command button wizard that is opened automatically, choose “Miscellaneous”.
  4. Choose “Run Macro”.
  5. Click Next.

Modifying form in design view

Next we choose to run our macro “mcrPrintCurrentItem” and then click Next button.

Modifying form in design view

Choose the “Text” radio button; add some text that will be displaying on our button, and click Next.

Run macro

In the final step of this command button wizard we put the name of our button “btnPrintCurrentItem” and click Finish.

Finishing macro wizard

We also have the option to modify the shape, size and colors of the button created in our form. With some font and color changes, our form could look like this one.

Form example

When reception has to print the invoice, they just need to open the MS Access Database form, find the record, and click on “Print Invoice for Current Item” button.

Bonus configurations

Let’s check out some extra customization that we could do for our database.

Automatically open files with the database

What if we want to open our form “frmPrintCurrentItem”each time we open the database?

First, click the Office Button (1), and then click Access Options (2).

Automatically open files

Then select Current Database (1) and under Display Form choose from the dropdown box “frmPrintCurrentItem” (2). Finally, click OK.

Automatically open files 2

After this, every time the database is opened, the form “frmPrintCurrentItem” will open automatically.

Add a combo box for searching

For another configuration, we could add some combo boxes controls on our form to find the record easily.

Let’s open the form in Design View. Choose the combo box control (1), and click somewhere in the form to open the combo box wizard (2), choose the last option of radio boxes (3) and click Next (4).

Combo boxes

Then we add some of the field that we want to see in the combo box (Nr, Client and Device in this example) and click Next twice.

Combo boxes 2

An in the last step, we label the combo box before clicking to finish the wizard.

Combo boxes 3

We could easily search for a record in our form by using this combo box. Our final form will look like this:

TrainSignal trial

About the Author

(MCT, MCITP, CCNA Security etc) is working in IT field since 2007. He is a system integrator and his focuses are designing and implementing server infrastructure and core networking. One of his strong passions, is Microsoft Access which helped him to create different databases for business and individuals. During his free time, he plays chess, football, watches Formula1 and…learns for the his upcoming certification.


Discussion