Creating a Custom Bench Sheet
Summary
The information in this article applies to LIMS version 1.x.
One of the first customizations many LIMS users will require is the addition of site-specific bench sheets. This article describes the procedures necessary to create and install a custom bench sheet. Creating a custom bench sheet requires knowledge of the MS Access development environment and familiarity with the MS Access report generator.
More Information
An LIMS bench sheet is an Access report. In its simplest form, a bench sheet contains nothing more than boilerplate text, lines and rectangles, and it is not bound to a data source. More complex bench sheets can require custom setup forms, tables, queries, and custom code. Bench sheets that need a sample query dialog to select and display sample characteristics can use the standard bench sheet sample query form included in the system.
All bench sheets must perform specific tasks in response to the report’s Open, Activate, and Deactivate events. To ensure that your new bench sheet includes standard event procedures for these events, always copy an existing bench sheet or one of the LIMS bench sheet templates provided for this purpose.
When developing and testing any custom additions to the LIMS system, always use a separate “development” version of LIMSCODE for your revisions. After thorough testing, the new features can be moved to the production LIMSCODE by exporting the required database objects and, if necessary, making appropriate entries in system tables. See knowledge base article “Creating a Development Version of LIMSCODE” for more information.
To create a simple “boilerplate” bench sheet:
1. Open your development copy of LIMSCODE with a full copy of MS Access and select the Reports tab in the database window.
2. Select either the portrait or landscape version of the “LIMS Bench Sheet Template”, enter Ctrl-C to copy the template to the clipboard, then enter Ctrl-V to paste the template into a new report object. Follow the standard bench sheet naming convention by beginning your bench sheet name with “rptBenchSheet”.
3. Now open your newly created bench sheet report in design mode and add the required text, lines, rectangles, etc. to create the sheet’s design. Save the report.
4. Since this bench sheet type is not bound to a data source and it does not require a setup form, you can test the report by simply opening it in preview mode.
5. To test your bench sheet from the LIMS user interface, you must first “install” it. Select the Tables tab in the database window and double-click the sysBenchSheet table. Add a new record by completing the following fields (note that the BenchSheetID field will automatically receive a counter value and the Subform field MUST remain empty):
- Name: enter a unique name to appear in the bench sheet pick list
- Report: enter the name of the bench sheet report object
- Inactive: set to ‘No’ so the bench sheet is included in the pick list
- Notes: (optional) enter any notes or comments to describe this bench sheet
6. Start the LIMS user interface by opening frmMainMenu (or switch to the Main Menu window if already open) and test the new bench sheet from the bench sheet setup form.
To create a bench sheet that uses the standard sample query dialog:
1. Open your development copy of LIMSCODE with a full copy of MS Access and select the Reports tab in the database window.
2. Often, the quickest method to create a new bench sheet is to copy a similar bench sheet and alter its design. Select an existing rptBenchSheet… object that uses the standard sample query dialog, enter Ctrl-C to copy the template to the clipboard, then enter Ctrl-V to paste the template into a new report object. Follow the standard bench sheet naming convention by beginning your bench sheet name with “rptBenchSheet”.
3. Now open your newly created bench sheet report in design mode and edit the layout accordingly. Note that this style bench sheet uses for its RecordSource qryBenchSheetSample which is populated by the standard bench sheet sample query form frmBenchSheetSampleQuery.
4. Copy any bound control and change its ControlSource property to display sample characteristics or use the View/Field List… menu entry and drag new fields to the report layout. Save the report.
5. You can test this style bench sheet by opening the print preview window from the design window. However, the bench sheet will only display sample data if qryBenchSheetSample (actually table sysBenchSheetSample) has data. To add sample data for testing, simply run any existing bench sheet that uses the standard sample query dialog. The samples queried for this style bench sheet are not overwritten until samples are queried for another bench sheet.
6. To test your bench sheet from the LIMS user interface, you must first “install” it. Select the Tables tab in the database window and double-click the sysBenchSheet table. Add a new record by completing the following fields (note that the BenchSheetID field will automatically receive a counter value):
- Name: enter a unique name to appear in the bench sheet pick list
- Subform: enter “frmBenchSheetSampleQuery” (without the quotes)
- Report: enter the name of the bench sheet report object
- Inactive: set to ‘No’ so the bench sheet is included in the pick list
- Notes: (optional) enter any notes or comments to describe this bench sheet
7. Start the LIMS user interface by opening frmMainMenu (or switch to the Main Menu window if already open) and test the new bench sheet from the bench sheet setup form.
To install the new bench sheet in the production LIMSCODE:
1. Export the report object to the production LIMSCODE.
2. Open the production LIMSCODE and add the sysBenchSheet table record to “install” the bench sheet in the production system.
3. Use the database window’s Security/Permissions… menu entry to set group permissions for the new report object:
- Admins: Open/Run, Read, Modify, Administer
- Guests: no permissions
- ReadOnly: no permissions
- Samplers: no permissions
- Technicians: no permissions
- Users: Open/Run
Creating a bench sheet that uses a custom setup dialog:
Creating a bench sheet that uses a custom setup dialog is beyond the scope of this article since it includes creating the custom setup form and may include creating a “sys…” table to hold intermediate results and a query for the bench sheet’s Record Source. Users proficient at this level of Access development should use frmBenchSheetSampleQuery, table sysBenchSheetSample, and qryBenchSheetSample as guidelines.