These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Saturday, April 13, 2013

simplifying record keeping for the Utah County Health Department

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

Blog Archive