Quite some time ago I created a backend interface to serialize and record data for labels printed through Crystal Reports as generated from our ERP system.  Unfortunately, the ERP system makes no provision to enter or update all of the key information required to support traceability of the labels after they are printed.

To solve this problem, I used Excel VBA to create a front end label manager to edit or update labels with the relevant information.  The window appears modally when the “Manage Labels” button is clicked on the ASN E2 Data worksheet (pictured below), allowing us to continue working with our workbook as required.

I could have written the front end as an independently managed application, however, it is a natural extension of other features and capabilities supported by the workbook in which the VBA code resides.  No code is presented in this post, however, some familiarity with Excel VBA and User Forms is assumed.

When the “Manage Labels” button on the worksheet is clicked, a “Label Details” window appears as pictured above.  After confirming or selecting the Server Location and clicking Connect, a Customer Code is selected from the ComboBox.  We then click the “Get Labels” button to load the labels for that Customer accordingly.  More granular search capabilities are also provided.

The core functionality supported by the front end is the ability to edit the data associated with any given label.  Text boxes are used to edit data for the fields indicated.  A check box next to each field is used to make the field “sticky”.

Note that although each field can be set independently, all “sticky” checkboxes can be set or cleared by pressing the “Set Sticky” or “Clear Sticky” button respectively.

Sticky Fields

Normally, text boxes are updated when moving from one record to the next.  However, there may be times when we want our data to persist or carry over from one record to the next so we only have to enter it once.

For example, at the time of printing and when parts are being produced, we can’t possibly know the Packing Slip number in advance.  If multiple containers (each having a uniquely serialized label) are shipped, we can simply make the Packing Slip “sticky” and update the records without having to re-enter the packing slip for each individual label.  Similar logic applies to the remaining fields.

The “Reset” button is used to overwrite the TextBox with data from the original record.  To do this, we take advantage of the “Tag” property of the TextBox.  When the data is read from the database, a  copy of the data for each field from the original record is stored in the “Tag” for each TextBox.  The “Tag” can store up to 2048 characters and is more than sufficient for our purposes.

Functionality is also provided to Swap the data between the current field value and the original data value of the field by using the up/down arrow button immediately to the left of the “Reset” button.

Note that all fields can either be reset to the original data contained in the record or cleared entirely by pressing the “Reset Fields” or “Clear Fields” buttons on the Edit page respectively.

Carry On

This type of functionality is not typical for most applications though it is not necessarily unique.  I recall seeing a “record level” Carry ON type of functionality years ago where the data from the previous record would carry over to the next record and edited as required.  However, this only applied to new records as they were being entered.

Risks

If the user “forgets” that a sticky field is set, it is possible that some data may be changed unintentionally.  By default, all of the check boxes for the sticky fields are clear (off) when the edit page is presented and must be turned on intentionally by the user.

On the other hand, consider the risks associated with data entry where the user would have to enter the same data multiple times for a larger number of records.

Editing Multiple Records (Global Update)

Though not presented as part of this post, it is also possible to select multiple records from the main “label display area” and globally apply changes to “common fields”.  Of course, “global” edits to multiple records presents it’s own challenges and risks and is the topic worthy of it’s own post.

Final Thoughts

You may already be using Sticky fields or perhaps considered making this capability available to your users.  There are certainly times where this type of functionality may not be desirable and certainly where the risks of “contaminating” your database may be high.  You will note that we did not provide the ability to edit all of the fields for a given record for this very reason.

The efficiency and effectiveness of an application extends from the underlying code to the actual person using the application and is one of the reasons why this was included as part of our ongoing Lean Code series.  Saving the user from having to re-enter duplicate data into multiple fields across large record sets can save a significant amount of time and effort and minimizes the risk of error where “exact” duplication of data is a necessity.

Until Next Time – STAY lean!

Related Articles and Resources