Before returning to BYU for my MBA I worked at Metro Code Analysis, a small inspection company in Fort Worth, TX. The company is a third-party permit expediting and inspection company that works with the City of Fort Worth and surrounding municipalities by reviewing construction plans, pulling permits, and inspecting construction in lieu of the municipality. While working there I managed the Inspection Department, and part of my duties was inputting all inspections called in into an Excel spreadsheet. While it wasn’t too complicated, the process was time consuming, and pressing Tab 17 times for each address in the spreadsheet and inputting numbers and names (while on the phone) left a lot of room for human error. These errors were the cause of many incomplete reports, missed/incorrect inspections, and fires to put out. After the daily deadline for inspections had passed (9am for same-day), the information in the spreadsheet would be transferred by hand into an MS Access database and sent to the City of Fort Worth.
For my project I created a user form within the spreadsheet in which the user could quickly input the information of each project and do so in a manner that greatly reduced the chance of human error. The user form held options for many different inspections and allows for the common “group” inspections to be specified with one click. In addition, once the day’s schedule is complete, clicking the “Create City Report” button in the ribbon will reformat the schedule and create the Access database table, ready for final review and to be sent to the city.
For the user form I decided to create a form that includes all the necessary text input fields for a construction project: the address, contractor’s name, contractor’s phone number, comments about the inspection, and any special inspections. Since time is very valuable in this department I decided against using lists, but instead used check boxes, option buttons, and command buttons. The check boxes are used for the common inspections, the command buttons for the common grouped inspections, and the options for determining what category a special inspection would fall into. The command boxes for grouped inspections will select the appropriate check boxes and turn green. If clicked again the check boxes will be deselected and the command box will turn back to the default gray. The text boxes, check boxes, command buttons, and option buttons are all tab indexed appropriately to facilitate movement to the next logical selection. I also included a “Clear Inspections” command button that will clear all selected check and option boxes. The “Schedule Inspection” command button will input all the information into the spreadsheet, and the “Cancel” command button will unload the form and return the user to the unchanged spreadsheet.
When the user clicks on the “Create City Report” button the generateReport sub will run, which is the main sub for the database export. The four other subs are components that make up the entire program. The program will first ask if the active cell is located within today’s report, then upon confirmation create a new worksheet after the current worksheet (createWorksheet) and input data from today’s schedule (pullData). The format of the database is completely different than that of the worksheet, so there is an entire sub called tidyWorksheet dedicated to reformatting the data to match the layout and format of the database file. Finally the sub updateDatabase will insert the new information into a table in the database. Currently this process takes 45-90 minutes to do by hand at Metro Code Analysis, depending on the size of the schedule. By comparison, this entire sub takes less than 2 seconds to complete.
The form was straightforward to begin with, but I ran into difficulties when I wanted to make it appear and work in a more professional manner. It took more coding than I original anticipated to make the command buttons turn green and change the appropriate check boxes, and to do the opposite when clicked again. The logic behind making a form, and how so many items are linked to each other, was a little surprising to me. The design of the form was also difficult to pin down. I feel like I achieved a concise form that has a natural sense of flow, though perhaps I’ll tweak it further in the future to add graphics and softer edges.
The database was difficult to do at first. I used the template we received from Dr. Meservy for our Project 5 and grew from there. I had to truly learn what I was doing in P5 in order to change the code to work with my own database. (It should be noted that I did not have access to the real database I used in my past job, so this database is just a replica-by-memory that I created for this use. If and when I do receive a copy of the actual database it shouldn’t be difficult to change the code to work with it.) The various subs I used to update the database was necessary when the code grew longer and longer. I learned much more about defining modular variables and sending variables from one sub to another without resetting the values. The difficult parts of this section were expanding all of the abbreviations used in the spreadsheet, parsing all of the addresses and inspection names, removing various symbols (“*” and “/”) from addresses/inspection names, and organizing the data appropriately. It took much more code than I thought it would. Updating the database was actually one of the easiest parts of this section.
A major element I wanted to include but was not able to was retrieving permit numbers from a public website to populate the spreadsheet. When I initially wrote my project proposal I was unaware that the City of Fort Worth had very recently changed its entire website and online services from an HTML-based site to a Javascript-based site. While I am successful at submitting forms and retrieving subsequent information from the internet, I was not able to go very far with this aspect of the project. I presented my problem to Dr. Meservy and he too was unable to work with the Javascript. He suggested I work with Dr. Allen to see if we could figure it out, so I approached Dr. Allen. It took weeks until I was able to receive a reply from him, and after 30 minutes of frustrated work, he finally found a way to get to the second page of the website and fill in the form with VBA. However, despite our hard work, we were unable to submit the form. I will, though, keep working with Dr. Allen and researching on my own to learn more about working with Javascript so that I can present a very useful tool to Metro Code Analysis in the future.
An element that grew into existence with this project, but not included, is exporting yet another report to MS Word. In my conversations with current employees at Metro Code, they expressed their wish to have a button that could generate the report they send directly to their inspectors. This little side project has taken much more time to do because it involves learning how to work with MS Word, creating a completely new layout, and parsing various strings differently than I have done in other parts of the main project. This program is almost complete.
No comments:
Post a Comment