Background
The 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.
Overview
The 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 Elements
In 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 346
Export 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 94
Edit Master HI
Minimizes the main menu and opens the Edit Master HI form.
VBA Handler: EditMasterHI_Click()
Source: Form_Main Menu, line 51
Get 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
9
HI 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 Errors
Changes 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
94
Show 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
88
Submit 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
100
Edit 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
1375
Clear 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
1305
Insert 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
1599
Save Edits
Makes 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 HANDLER
Source: Form_Edit Master HI, line
1688
Calculate and Export Legal Description
Calculates 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 HANDLER
Source: Form_Edit Master HI, line
30
Standard Module
This 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_DIR
Source: Standard Module, line 2
Delete Table Records
Deletes all records from the table whose name matches the
passed in string.
VBA Handler: DeleteRecords(tableName)
Source: Standard Module, line 3
Upload 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 11
Create 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 105
Fix 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 200
Validate All Fields
Wrapper 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 310
Validate 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 330
Validate 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 363
Validate 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 379
Validate 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 406
Validate 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 428
Format Input
Runs the query FormatEntries to automatically apply formatting rules to uploaded data.
VBA Handler: FormatInput()
Source: Standard Module, line 464
Format Date
Attempts to format a date using the mm/dd/yyyy pattern.
VBA Handler: FormatDate(dateString)
Source: Standard Module, line 474
Format 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 509
Format Acres
Attempts to format an acres value using two decimal places
(unless more already exist).
VBA Handler: FormatAcres(acresString)
Source: Standard Module, line 525
State Specific Elements
Different 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 Validation
Certain 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 Queries
Each 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.
Troubleshooting
During 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 Working
This 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.