BackgroundThe OCR conversion process was started with the end goal of having all historical index data in a single location that could be easily manipulated in order to accomplish many goals. These goals include mass data cleanup or standard updates, quick and easy formatting of data into a presentable product, and semi-automated updating of Case Recordation legal descriptions. It is also important to note that this process eliminates the need for paper copies of every historical index page. Thus, we developed the Historical Index Upload Database (HIUD) to take over the responsibility of these goals after the pages have been converted and gone through the Historical Index Conversion Tracker process.OverviewThe HIUD is a desktop application was built using Microsoft Access (2007-2010).While the HICT is a tool that can be utilized for any state work, the upload database must have specific elements tailored to the state whose data is being worked with. Therefore, we have slightly different versions of the HIUD for different states. Because of this I will cover the basic structure of the HIUD, what you can expect to stay the same and what you can expect to change between each states version.Common ElementsIn this section, we’ll take a look at the main components in the HIUD that will not change between any version. Please make yourself familiar with Microsoft Access, VBA, and the structure of this database before attempting to make any changes. All of the forms in this database use VBA, not macros, to handle user interaction.Main Menu (Form)When you open the tool, you will be greeted with the Main Menu. This simple menu allows the user three options. Upload Township Table, Export Township HI, and Edit Master HI. Let’s take a look at what is going on behind the scenes with this menu.Upload Township Table Prompts the user to select a formatted text file containing HI data (see the user guide if you are interested in what the formatted data should look like). Validates that it is the correct file type, if so passes the file path to the Get Upload Info form (See the section on this form for more information).VBA Handler: UploadTable_Click()Source: Form_Main Menu, line 346Export Township HI Prompts the user to select a text file containing the PLSSIDs (one per line) of the townships that they wish to export. Then prompts the user to select a directory to save the exported .csv files to. Attempts to find symbols that will not properly be displayed in the word document and replace them with plain text (see the word template technical documentation for more information).VBA Handler: PrintHI_Click()Source: Form_Main Menu, line 94Edit Master HI Minimizes the main menu and opens the Edit Master HI form.VBA Handler: EditMasterHI_Click()Source: Form_Main Menu, line 51Get Upload Info (Form)The Get Upload Info form is opened after the user has pressed the Upload Township Table button on the main menu and selected a properly formatted text file to upload. The purpose of this intermediary form is the gather some extra data regarding the data being uploaded.The user is prompted to enter in the PLSSID (A PLSSID generator is available to make sure no mistakes are made) and the page number of the document they are uploading. Once they have done so, the only real functionality on this form we need to talk about is the OK button.OK (Submit Form) Validates that the PLSSID is 15 characters and possibly has added validation based on the state (see the State Specific Elements section for more information). Validates that the page number entered in is an integer. If both are validated, calls the UploadSelection function (See the Standard Module header in this section). passing the PLSSID and page number as well as the path to the selected file.VBA Handler: OKButton_Click()Source: Form_Get Upload Info, line 9HI Entry Form (Form)This form will display the uploaded data in a familiar historical index formatted form. The user can make changes to the data in this form, and they form validates and updates data per rules set in place. To update validation rules (cells turn red if validation fails) see the Standard Module functions that begin with Validate. To update auto-update rules (fields like acres update from 160 to 160.00) see the Form_Edit Found Entries Form functions that end with _Exit.This form provides us with three useful buttons, Show Errors, Show All, and Submit Entries.Show ErrorsChanges the HI Entry Form’s record source to the query named FindInvalidRows. This query runs each row through the ValidateAll function in the Standard Module. This function attempts to validate all fields with validation rules and returns true if any field fails validation. This means that the query will only show records that have failed at least one validation.VBA Handler: ShowErrors_Click()Source: Form_HI Entry Form, line 94Show All Changes the HI Entry Form’s record source back to the original record source, 01_SelectedHIEntries. This can be used after the Show Errors button has been pressed to make the form display all uploaded records again.VBA Handler: ShowAll_Click()Source: Form_HI Entry Form, line 88Submit Entries Deletes all records in the Master HI where the PLSSID and page match the upload data. This is to avoid two versions of an HI page existing in the database. The CreateLinks function from the Standard Module is called to populate the linking fields (ACTDATE_ALL, KOE_ALL, MTPNUM_ALL) thus linking base rows to the non-base rows above them. Then, the SendToMaster query is called to append the uploaded data to the Master HI table (99_MasterHI). Finally, the FixLinks function from the Standard Module is called to update all link fields in the Master HI.VBA Handler: SubmitEntriesButton_Click()Source: Form_HI Entry Form, line 100Edit Master HI (Form)This form is where much of the action takes place. In this form, you can search, edit, and export data. Unlike the main menu’s export function, this form can export legal descriptions for any serial number in the data. Most of this forms VBA back-end consists of legal description export code, but we’ll focus on the user interaction code that is most subject to small changes.Filter Records Filters the form’s record source based on the data entered into the fields above this button (Serial Number, PLSSID, Section, Dates). This function is automatically called whenever one of the fields is changed. Importantly, while the form may only display some records, it will keep a list of all records within the affected townships. For example, if a serial number is selected, it may only show one or two records within the form. However, for each PLSSID that serial number is in, this function will also remember to keep all records in each of these PLSSIDs in the query Select for Editing. The reason this is important is that when we go to save edits to the master, an entire PLSSIDs data must be deleted and replaced to avoid primary key conflicts. To do this, we need to save the data for the entire township (or townships) we are editing in the background, even if we only display a few rows.VBA Handler: FilterButton_Click()Source: Form_Edit Master HI, line 1375Clear Filter Clears all filters on the edit form and returns it to the starting state.VBA Handler: ClearFilterButton_Click()Source: Form_Edit Master HI, line 1305Insert Row Above Inserts some number of rows (based on the amount in the row count field right below the button) above the selected row into the 99_EditMasterHI table (the sub form’s record source).VBA Handler: InsertRowButton_Click()Source: Form_Edit Master HI, line 1599Save EditsMakes sure a PLSSID is entered in for every record being edited, then saves the changes by deleting all effected townships from the Master HI and then calling the Save Edits query to append the updated data for these townships to the Master HI.MISSING VBA HANDLERSource: Form_Edit Master HI, line 1688Calculate and Export Legal DescriptionCalculates the current standing legal description for a case using data from the Master HI. This data is then exported into a formatted text file (SFTA Upload Format) that can be used to upload legal descriptions into Case Recordation. The code is lengthy and complex. For a better understanding of how legal calculation works take a look at the source code and comments.MISSING VBA HANDLERSource: Form_Edit Master HI, line 30Standard ModuleThis module holds functions that do not directly relate to any form but can be called from any of the forms. Think of these as global functions.Default Directory (Constant)A constant variable that holds a default directory to open at when using file dialogues.VBA Handler: DEFAULT_DIRSource: Standard Module, line 2Delete Table Records Deletes all records from the table whose name matches the passed in string.VBA Handler: DeleteRecords(tableName)Source: Standard Module, line 3Upload Text File to Table Validates and transfers the text file data to a temporary table 00_UploadedHIEntries using the import specification “HI Import Specification” and then takes the required fields and appends them to the 01_SelectedHIEntries table. During this append, many fields are formatted using various format functions (see later in this section). Replaces common fractions with their symbols for a better viewing experience while editing. This function may also perform further cleanup operations via queries depending on State preferences (see the State Specific Elements section for more information).VBA Handler: UploadSelection(PLSSID, pageNum, selectedFile)Source: Standard Module, line 11Create Base Row Links Traverses backwards through the 01_SelectedHIEntries table (the upload table) and attempts to find base rows. Base rows are defined as rows where at least one of the ACTDATE, KOE, or MTPNUM fields is populated. Non-base rows are defined as rows where all the above fields are not populated. Once a base row is found, we save the values for these fields into temporary variables. As we continue our search upwards, if we find a non-base row we will save the most recently visited base row’s data for these fields into the corresponding ACTDATE_ALL, KOE_ALL, and MTPNUM_ALL fields. This will create a link between the base row and the non-base rows above it. Once we find another base row we update the temporary variables with values from this new base row and continue upward through the table until all non-base rows are linked with a base row.VBA Handler: CreateLinks()Source: Standard Module, line 105Fix Base Row Links Works similarly to the CreateLinks function but is used after editing has been done to the master HI. This attempts to update the links for all townships that were updating during the latest edit. For a more detailed description about what these links are and how they are create see the CreateLinks function above.VBA Handler: FixLinks()Source: Standard Module, line 200Validate All FieldsWrapper function used by the FindInvalidRows query that calls all the specific validation functions with arguments passed in from the query. Returns 1 if any of the validation functions fail and 0 if they all pass.VBA Handler: ValidateAll(lotStr, aliquotStr, dateStr, acresStr, secStr)Source: Standard Module, line 310Validate Lots Passes if the lots value is left blank or contains only numbers, spaces, commas, and dashes. Fails if any other characters are found.VBA Handler: ValidateLots(lotstring)Source: Standard Module, line 330Validate Aliquots Passes if all aliquot fields contain either a capital X or are left blank. Fails if any other characters are found.VBA Handler: ValidateAliquot(aliquotString)Source: Standard Module, line 363Validate Date Passes if the date is left blank or is determined valid using the IsDate VBA function. Fails otherwise.VBA Handler: ValidateDate(actdateString)Source: Standard Module, line 379Validate Acres Passes if the acres value is left blank or is numeric AND does not contain dashes or commas. Fails otherwise.VBA Handler: ValidateAcres(acresString)Source: Standard Module, line 406Validate Section Passes if the section value is left blank or contains an integer from 1 to 36, inclusive. Fails otherwise.VBA Handler: ValidateSection(sectionString)Source: Standard Module, line 428Format Input Runs the query FormatEntries to automatically apply formatting rules to uploaded data.VBA Handler: FormatInput()Source: Standard Module, line 464Format Date Attempts to format a date using the mm/dd/yyyy pattern.VBA Handler: FormatDate(dateString)Source: Standard Module, line 474Format Aliquot Changes any lower case x to an uppercase X which is the standard for aliquot values.VBA Handler: FormatAliquot(aliquotString)Source: Standard Module, line 509Format Acres Attempts to format an acres value using two decimal places (unless more already exist).VBA Handler: FormatAcres(acresString)Source: Standard Module, line 525State Specific ElementsDifferent states sometimes have different standards, validation rules, and obviously different data. This means that each upload database must be slightly tailored to match these needs. In this section, we will talk about where these differences can arise and what they would entail changing.Township ValidationCertain states want to make sure that the PLSSID typed in on the Get Upload Info form matches an existing township in their state. This requires a full list of townships from that state. This data is usually saved in an optional table. For example, California’s upload database has a table named 99_CaliforniaTownships that is used to validate the PLSSIDs entered in. This data must be comprehensive enough to recreate the entire PLSSID. This validation is usually done within the Get Upload Info form’s VBA back-end.Cleanup QueriesEach state will likely have their own set of cleanup queries. These are queries that attempt to find common errors in the Master HI and fix them in mass. Common examples include fixing spacing around semicolons, removing semicolons at the end of certain fields, etc. Some of these queries do not make changes themselves, but instead show the user rows that are in error according to specific standards. These queries help to identify errors that cannot be fixed in mass. For example, showing rows in which the Action Date is out of order, or showing rows with invalid or missing LR2000 serial numbers.These queries will often have accompanying functions located in the Standard Module.TroubleshootingDuring regular use, the user may run into certain issues. Below is a list of commonly experienced problems and how to go about fixing them.Form Buttons Don't Appear to be WorkingThis is usually a trust center issue. Follow the steps below to solve it.Using the Microsoft Access Menu go to File > Options.The pop-up options window will have a menu on the left side. At the bottom of this menu is the label Trust Center. Click this.On this page, press the Trust Center Settings button.The Trust Center window will now open. On this window’s menu, click on the Trusted Locations label.On this page, press the Add new location button.On the new window, press the Browse button.Browse to the folder in which the HIUD Access Database is located and select it. To be safe, you can also check the ‘Subfolders of this location are also trusted’ checkbox before pressing the OK button.Press OK on the remaining two windows and then close and reopen the entire database to refresh the trust settings.The form buttons should now work.