One of the routine tasks for the prevention division of the
Utah County Health department is their assistance with alcohol compliance
checks. Alcohol compliance checks are the process of sending in underage buyers
to local vendors and have them attempt to purchase alcohol. This process is
done under the supervision of local law enforcement and citations are written
for successful purchases. It is the responsibility of Utah County to assist in
these tasks. The Health Department hires underage buyers and coordinates with
officers so that checks are done once a quarter.
In 2008 I was assigned by the health department to manage
this task. I was responsible for hiring and organizing the buyers as well as
filling out all the necessary paperwork as well as recording data about the
checks. The original process was slow and inefficient. During my time there, I
streamlined the process dramatically. This project is an improvement upon that
original solution.
The process to do the paperwork for a compliance check in a certain
city is as follows.
1.
Lookup the address of the local police
department and their associated officer
2.
Lookup the full name and birthdate of the
underage buyer
3.
Lookup the respective names and addresses of the
various stores in that city
4.
Input all this information into a form, print
those forms and take them on the check
5.
On return from the check, take the now filled
out paper forms and input that data into an excel spread sheet.
Though parts of this process were done by a simple copy and paste
from various spreadsheets the actual task of filling out the forms was primarily
manual. My application is designed to streamline this process
The solution has several major components.
1.
One spreadsheet where all data is managed in a
single place
2.
Multiple Visual Basic forms to assist the
recording of semi-permanent data (such as officer address and name). Separate
forms were built to record compliance check related information for Police
departments, Stores, and Underage buyers.
3.
A Visual Basic form to do search queries on the
large store list.
4.
A Visual Basic form for “pre-compliance-check” activities.
This form not only accepts user input but uses that input to predict other
information for the check. This form then automatically creates a word document
using the mail merge process. This process produces unique forms for each store
check.
5.
A Visual Basic form for “post-compliance-check”
activities. This form further assists the user with data input. This data is
then inserted into a master list with all other completed checks.
6.
Custom buttons inserted into the ribbon to
activate the various forms.
No comments:
Post a Comment