Streamlining Police Officer Paperwork with Word and Visual Basic

 

I work as a police dispatcher for a small department in the Denver, Colorado metro area. When I heard that officers were annoyed with some of the duplication they encountered when doing paperwork I started looking into their process to see if there was a way I could help. Officers are required to submit a “Statement in Support of Warrantless Arrest” (Warrantless Arrest) and a “Determination of Probable Cause” (PC) to the courts when arresting someone on new charges. Much of the same information on the Warrantless Arrest such as the subject’s name, the subject’s date of birth, date and time of arrest and crimes charged, etc. are also required on the PC. I wanted to find a way to allow officers to only enter that information once.

Purpose: The Warrantless Arrest and PC forms were already created as Microsoft Word documents that were locked for formatting with fields that officers could fill in. I wanted to create an entry form to allow information to go into both documents. I also did not want to have to re-create the documents from scratch.

Solution: My first thought was to create a locally hosted web application with C# and .NET that officers could access as long as they could get to the local network, but then I realized I want to get it done soon, I don’t know C# or .NET and I don’t have the knowledge or access of local resources to create a web application so I had to scale it down a bit.

  • Plan A – Use Microsoft Access to create the entry forms and populate the Warrantless Arrest and PC as reports
    I had created useful forms and reports using Access for my home inventory database so I am familiar with Access. I was also under the impression that Microsoft Office programs play nice with each other so I could have Access send data to a Word document or use the Word document to easily create Access reports. Plan A did not work and it did not work for a few reasons:

    • Access is good at creating databases. Databases are good for storing data for later use. However once the officers filled out these documents they are printed and sent to our Records section. There is no reason for the officers to store this data (The data is stored in a separate Records system. That’s a whole other deal)  so creating another way to store data was unnecessarily complex and redundant.
    • Access and Word do not play well together in the way I thought. I could not figure out how to send data from Access to Word and I was unable to use the Word documents to create reports in Access. I would have to re-create the documents from scratch. This would not do.

     

  • Plan B – Create a UserForm in Word to populate the Warrantless Arrest and PC documents
    I’m just going to come right out and admit I had no idea this was possible. I had reached a frustration point with Plan A and started googling for answers. I knew other people had to have had this issue! I was about to give up my search until I found this YouTube video: “Creating a userform in MS Word and using it to insert information into a bookmark” – http://youtu.be/8lKzMuk2nO0. This was a great jumping off point that allowed me to find the solution I was looking for.I combined the two documents into one, followed the example in the video, named all the fields and referred to them as Bookmarks. I soon had a solution that worked great… once.The example in the video used Visual Basic to replace the Bookmarks in the document with text from the UserForm which meant that if the information needed to be edited after the initial use of the UserForm the officer would need to edit the document itself. That wouldn’t work. A search of the MSDN Office Dev Center lead me to FormFields which eventually let me to my solution.

My solution is a Word document that contains both the Warrantless Arrest and PC forms and is locked for editing. The only changes a user can make to the document is through the Entry Form. When opened, the Entry Form pictured above checks the document for existing information and default values and opens for the user. Once the user submits the Entry Form, the Entry Form is hidden to allow the user to view the document. Clicking on any field in the document will make the Entry Form viewable again. This allows the user to enter and modify information on the forms.

Challenges: This is the first software project that I’ve worked on that I have approached purely from the position that I had an issue, but I didn’t know what the solution was. Most of my projects start out as “I just learned a new thing. Let’s find a problem to fix with new thing.”

I have never used Visual Basic so I got to learn the language as I was using it. Also using the Microsoft Visual Basic for Applications editor was new because some of the functionalities of the language and applications were hidden from me.

This was my first software project that didn’t start with an issue, an empty directory and a text editor. As a student, almost every problem I am given starts with a blank canvas. This project already had a solution, I just got to improve on it. I was also using an unfamiliar language, an unfamiliar framework and an unfamiliar IDE that is baked in to Microsoft Office.

This is also the first project that I did not develop for myself. Creating solutions for others means I have to make forms look nice and try to make it fit into their workflows. I am also unsure how easy it will be to fit this into the official workflow of the department. Department leaders are often wary of solutions they did not think of themselves.

Outcome: I’m very excited for this project because I did not start from scratch. I was only able to work on part of the problem and I had existing resources I had to use to reach my solution. This is how I imagine most real development work.

I just sent the document to some of the commanders for their review. I have also shared it with a couple officers but have not received any feedback. I will update this once I hear more!

Updates