How to Display Sample Characteristics in an Import by Sample Template

Summary

The information in this article applies to LIMS 2.x and Excel 97.

When you use Spreadsheet | New to initialize an Excel template from the results by sample screen, the data exported to the template’s LIMSData worksheet does not include sample characteristics. This article describes the steps necessary to retrieve and display sample characteristics in the template.

More Information

Using Spreadsheet | New to initialize an Excel template from either the results by analyte or results by sample screen will export the screen’s underlying analysis recordset to the template’s LIMSData worksheet. In the results by analyte screen this recordset includes sample characteristics for each analysis. However, the results by sample screen uses a subform for its analyses and this recordset does not include sample characteristics which are displayed through the main form’s recordset.

In order to display sample characteristics in a results by sample template, the template must retrieve this data from the LIMS’ results by sample screen’s main form recordset. Place the VBA code below in your template’s AfterTransferFromLIMS macro to retrieve the sample characteristics to an unoccupied region of the LIMSData worksheet.

Dim objAccess As Access.Application

' Read the sample's characteristics from the LIMS
' Results by Sample screen and display in the
' LIMSData worksheet.  NOTE: You must set a reference
' to the Microsoft Access 8.0 Object Library using
' Tools | References in the VBA editor.
Set rng = Range("LIMSData!BA1")   ' start cell
On Error Resume Next
Set objAccess = GetObject(, "Access.Application")
If Err = 0 Then     ' Access is running
    Set rs = objAccess.Screen.ActiveForm.RecordsetClone
    For i = 0 To rs.Fields.Count - 1
        Set fld = rs.Fields(i)
        rng.Offset(i, 0).Formula = fld.Name
        rng.Offset(i, 1).Formula = rs(fld.Name)
    Next i
End If

The VBA code above retrieves the results by sample screen’s main recordset. For each field in the recordset, the field’s name and value are written to the LIMSData worksheet beginning at cell BA1. Your template can then redisplay any recordset field value in your data entry worksheet.

For an example, see the results by sample import tutorial in the LIMS Release Notes. This example uses the MyProject.xlt template installed in the Example Files folder. Note that file MyProject.xlt should be dated 10/14/1999 or newer. If you have an older version of this file you can download a current version of the Excel examples from the file library