Beyond Excel: VBA and Database Manipulation

Jun 19 2010   11:25AM GMT

Check Entry – Form Select – Theory and Demo

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

frmSelect facilitates finding and selecting a record in massive lists by allowing the user to search by code/id/number or description/name (See video demo at bottom of post).  Below is a screen shot showing someone searching a poorly designed customer master file.  It looks and functions similar to another form I introduced months ago called frmSelect_Mulitple.  This differs in that frmSelect only permits a single item to be selected. Selecting multiple codes makes sense when you want to filter a database and expect several records to be returned.  It does not make sense when only a single item can be entered into a single field/column of a single record/row of a database file/table.  So where as frmSelect_Multiple was introduced in the context of reporting, frmSelect is introduced in the context of data entry.

frmSelect Sample

frmSelect Sample

Data entry often requires things like Country Codes, Employee IDs, Account Numbers, Customer IDs, Project Numbers, Status Codes, Contact IDs, etc.  Excel natively provides means to select such items in drop down list boxes or combo list boxes.  Those work great for small lists.  They really don’t work for massive lists.  So before we explain why, allow me a moment to talk about business databases in general.

In my years as a business developer, I classified four major types of file structures, three of which I’ll discuss here:

  • Transaction Files/Tables – These files contain things like purchase orders, invoices, checks, manifests, etc.  They are usually divided up into a Header File/Table and a Detail File/Table.  
    Header Files: These files contain transaction attributes for which there can be only one item associated to the transaction.  Using an Invoice as an example, an Invoice can be for 1 and only 1 customer.  It is issued on 1 and only 1 date.  It has 1 and only 1 Invoice number.  In this case, the invoice number should be the primary key.
    Detail Files: These files contain the many to 1 relationships.  Using the Invoice as an example: there can be many items on 1 Invoice; there can be mutliple charges on 1 Invoice; there can be multiple accounts associated with 1 Invoice.  In general, these files are keyed with the Header File’s primary key and a Line Item number.
  • Master Files/Tables – These files contain things like people, places or things.  They include Employee Masters, Customer Masters, Inventory Masters, etc.  Master files, in my experience, all share something in common.  They all have a Primary key field, and they all have a description field
    Primary Key Field: Primary Key Fields are unique identifiers that never, ever change.  Good primary keys for master files are ALWAYS numbers assigned by the system that have NO meaning what-so-ever associated with the contents of the record (The non-numeric abbreviation as the key to the customer master in the screen shot makes that file poorly designed in my opinion).Primary Key Fields are ONLY a means to uniquely identify a Master File record. Because master file primary keys never change, they are what should be stored in Transaction Files.  An example might be an Employee ID from the Employee Master which must be recorded in the Payroll Checks Transaction File so we know who the check was for.  We always want the key field and never the employee’s name because the name might change such as when an employee is married and that would orphan child records.
    Description Fields: Descriptions are what we, as humans, know people, places, and things by.  For example, we know Fred Jones by his name, not his Employee Number.  It’s possible there are two “Fred Jones” employeed at my company, but each one will have a unique employee number.  For this reason, we must be able to find master file records by the description (what we know), and use the primary key field (guaranteed unique and unchanging) to identify a specific record.
  • Code Files – These files are very similar to Master Files except the primary key has meaning.  It’s usually an abbreviation that was accepted by convention.  An example is “USA” as the code for “United States of America”.  Another example is a General Ledger’s Chart of Accounts which uses a highly coded account number as the primary key.   Like master files, the primary key must be guaranteed unique and unchanging.  Unlike master files, we sometimes know the abbreviation as well or better than the name or description.

Excel’s list boxes facilitate “begins with” searching of only one field in a list and, for small lists, they are faster.  frmSelect facilitates “begins with” and “contains” searching of the primary key and description fields and, for massive lists stored on a server, frmSelect is overall faster.  Since both Master Files and Code Files have a primary key and a description, frmSelect works nicely for both. 

The YouTube video below shows frmSelect in action.  We’ll see how to construct frmSelect in the next post.

[kml_flashembed movie=”” width=”578″ height=”476″ wmode=”transparent” /]

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: