dLSoft  barcode & labelling software     

Barcodes in Access

Generating barcodes in Access forms or reports may be achieved the Active-X control of Barcodes & Labels for Office, OLE objects or barcode fonts and macros. Suitable for Access 2007, 2010 or 2013.

Active components

Barcodes may easily be placed on Access forms or reports using the Active-X control component. (Similar functionality is available for developers through the Active Barcode Components or the the dBarcode Developer kit)

Barcodes in MS Access


Microsoft Access is a database that holds data in tables. When the Active Barcode Control is added to an Access Form or Report, the control's Properties list allows the user to select one of the Table fields as a Source of the components data - i.e. the data to appear in the barcode. This approach has the advantage that no space is required in the database to store the barcode images - they are created "on the fly" as needed.

An Active Barcode Control may be placed on an Access form or report by selecting the More Controls icon in the toolbox (which, if not visible, can be displayed by checking the Controls Toolbox member of the View – Toolbars menu), or by choosing Active-X Controls from the Insert menu and choosing Active Barcode Control from the list of controls available. In Access 2007/2010/2013 the Active-X Controls item appears on the Design toolbar.

A default barcode image appears on the report. The barcode properties may be set programmatically or by the user. For example, right-clicking on the barcode and selecting Properties from the pop-up menu causes the control’s Properties dialog to be displayed.

The Record Source may be selected from a drop-down list in the form or report's properties list, and the barcode's Control Source may be selected from the drop down list in the component's properties list. All other properties (code type, size, font, etc.) can be selected from the other entries in the Access Properties list at design time.

Barcode properties in Access

This allows barcode creation to be fully automatic for every record in a database.

Of course, a barcode image that is the same on every record may also be created - by ensuring that the Control Source entry on the component's properties list is empty, and the required data is entered for the Caption property.

Barcode macros

Barcodes & Labels for Office also includes macros that allow barcode images of font-based barcodes to be saved within a database table.

For picture barcodes the macros are PBarcodeSetup - which sets the barcode properties - and PBarcode which (by default) takes data from a field named BarcodeSource and puts the barcode image into the adjacent field - in this example named BarcodePicture.

First the barcode propeties are initialised by running the PBarcodeSetup macro which displays the barcode options dialog:

Barcode macros properties dialog

The user then opens the table containing the barcode data. To create barcode images within the currently displayed table simply select the table cell or cells containing the barcode data and run the PBarcode macro from the list of macros in the Macro dialog.

The barcode picture will be pasted into the cell on the right. Note that the picture will not be visible while in datasheet view; the word Picture will be displayed instead.

It would probably be more usual to create an entire column of barcodes in a datasheet, rather than just one. Select an entire column by pushing the column title button at the top of the column, and then run the macro to generate the barcode pictures.

Access table with barcode data

Access table with barcode pictures

The barcode pictures may be displayed in a form or report by inserting a bound control onto the form, with its control source specified as the table column containing the barcode pictures.

Font-based barcodes in Access

While barcode images are the ideal way to produce barcodes in Access forms or reports, font-based barcodes in Access may also be stored within the database and this can be useful in some situation - for example, where Word's Mailmerge is to be used to generate documents containing data from an Access database.

Font-based barcodes are barcodes in the form of a sequence of text characters that appear as a barcode when displayed or printed in a suitable barcode font. The Barcodes & Labels for Office product provides macros (including source code) for creating font based barcodes from a field in an Access database, storing the resultant barcode text in another field from where it can be used, either to display or print the barcode, or as the source of the barcode for external use - such as Mailmerge in Word.

The macros are FBarcodeSetup and FBarcode. The FBarcodeSetup macro causes the display of the Barcode Font Options dialog which is used to specify the barcode type and other details of font-based barcodes to be created next time the FBarcode macro is to run.

barcode font properties dialog

Running the FBarcode macro creates the font barcodes using data from the BarcodeSource field and placing the result in the BarcodeFont field - where it looks unintelligible until displayed in the correct barcode font.

Access table with font barcode

A developer can modify the macros to create the barcodes without end-user intervention, but two macros are  provided - one to allow the user to display a dialog box for setting the required barcode properties, and one to create the barcodes for all records in a table.

Most common font barcodes, including many popular postal barcodes, can be stored in a database in this way, and as the barcode text is relatively small the impact on the database size is small. It is important to note that the relevant barcode font needs to be present on the computer that displays or prints the barcode.

Customisation of the macros

The PBarcode macro operates when a table is displayed in datasheet view, takes the barcode data from selected cells (which must be Text cells) and pastes the barcode images into the adjacent cells (which must be OLEObect cells). Navigation and pasting are controlled by sending keystrokes to the table. TAB keys move the cursor around and the ^v (Control + V) initiates the pasting of the image.

Set tbl = Screen.ActiveDatasheet
i = tbl.SelTop
j = tbl.SelLeft
k = tbl.SelHeight
If k > 1 Then n = k - 1 Else n = 1
SendKeys "{TAB}", True
SendKeys "+{TAB}", True
For i = 1 To n
Set CurrentControl = Screen.ActiveControl
If (IsEmpty(CurrentControl.Text) Or IsNull(CurrentControl.Text) Or CurrentControl.Text = "") Then
errorcode = 9
GoTo done
End If
BarcodeString = CurrentControl.Text
k = 0
errorcode = BarCodew(ByVal BarcodeString, k)
If (errorcode = 0) Then
' these keys paste the barcode image into the adjacent cell
SendKeys "{TAB}", True
SendKeys "{DEL}", True
SendKeys "^v", True
ElseIf errorcode = 1 Then
MsgBox "Wrong number of characters in barcode"
ElseIf errorcode = 4 Then
MsgBox "Illegal character in barcode data"
MsgBox "Error in barcode data"
End If
' these keys move to the cell below for the next source
SendKeys "+{TAB}", True
SendKeys "{DOWN}", True
Next i

It is generally not necessary to edit this macro because there is no significance in the order of columns in datasheet view.

The FBarcode macro uses named fields in a named table, by default taking the barcode data from a field named BarcodeSource in Table1 and placing the font-based barcode into a field named BarcodeFont. Any of these names may be changed simply by editing the macro code:

Set MyDB = CurrentDb()
Set dset = MyDB.OpenRecordset("Table1")
' The rest just cycles down the table taking the source text from a field called BarcodeSource
' and placing the output in a field called BarcodeFont
' Edit as required to change fieldnames
Do Until dset.EOF
St$ = dset!BarcodeSource
n = Len(St$)
Out$ = String(255, " ")
x = BarCodewF(ByVal St$, n, ByVal Out$, ByVal foname$, foht)
If (x > 0) Then
dset!BarcodeFont = Out$
End If