Printing SharePoint 2010 list items using Access 2007
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?
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:
Also, we have created one SharePoint List that has these columns:
Basically, this is the work process:
- The client brings to the company one device that must be repaired, fixed, troubleshoot.
- The receptionist picks up the device, takes notes about the symptoms, and also notes the client’s name and phone number.
- The receptionist registers the ticket to SharePoint, and all the technicians receive an automated email (Exchange 2010 receives email from SharePoint lists).
- Based on the device, the right technician begins his/her work by inspecting and repairing the device.
- 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.
- Next, the receptionist notifies the client to pick up the device after services are finished.
- 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.
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 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.
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 new report called “rptPrintCurrentItem,” and choose as Table/Query the query we just created above.
Click the Next button three times; select the Columnar Layout, and then finish.
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.
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”
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.
Then, follow these tasks in order:
- Click the button control.
- Click somewhere on the form where we want to put the button.
- Then from the command button wizard that is opened automatically, choose “Miscellaneous”.
- Choose “Run Macro”.
- Click Next.
Next we choose to run our macro “mcrPrintCurrentItem” and then click Next button.
Choose the “Text” radio button; add some text that will be displaying on our button, and click Next.
In the final step of this command button wizard we put the name of our button “btnPrintCurrentItem” and click Finish.
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.
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.
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).
Then select Current Database (1) and under Display Form choose from the dropdown box “frmPrintCurrentItem” (2). Finally, click OK.
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).
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.
An in the last step, we label the combo box before clicking to finish the wizard.
We could easily search for a record in our form by using this combo box. Our final form will look like this: