Does your code have no ID(ea)?

by Rob Parsons - Eyecan Publishing

Have you noticed how Access by default assigns a counter column to tables with a field name of ID? I thought that ID stood for IDentification but it actually stands for Item Data. But it could equally stand for InDependence (pun on ID4 intended).


Computers are very dumb. They can't read and they can only count to two but they can count very very fast and through the magic of binary mathematics they are able to fool us humans into thinking that they can actually read and perform decimal arithmetic. This article explores the use of ID(ItemData) values in programming languages and explains why if you don't use ID values you may be dooming your code to immediate redundancy. This subject is recently topical as Fujitsu has chaired the first industry forum to establish standards for long integer values of system constants("The Australian" 13th Feb 1997) and with the release of VB5CCE, VB developers will be able for the first time be able to roll their own custom controls which will rely upon the use of long integers to describe the properties of these controls to the operating systems of computers. "Objects" in computing terms are described to the OS as a set of properties and methods, the attribute of the property being a long integer value and so these long integer values are important if MS is to implement their "Common Object Models".

With the release of version 3 of Visual Basic the ItemData property was first introduced to list controls (combo and list boxes) but the use of ID values is pivotal to all operating systems and this is self evident if you examine the constant declarations of the Windows' operating systems. I'll use as an example color attributes which as you will see can be used to not only describe a property of say a Form Object but is also an attribute of real life "Objects" that you may be trying to model in your computer programs.

Listing 1.
Some color constants from constants.txt that is shipped with VB3.

Global Const MAGENTA = &HFF00FF
Global Const RED = &HFF&
Global Const GREEN = &HFF00&
Global Const BLACK = &H0&
Global Const BLUE = &HFF0000
Global Const WHITE = &HFFFFFF
Global Const YELLOW = &HFFFF&

The first thing that you may notice is that the variable names are English. This is OK if your user audience are all English speaking but what if your audience is the WWW? Secondly the values that are assigned to the respective constants are Hexadecimal. This is not a problem as in reality these Hex values are converted to binary values and stored in long integer registars when VB is pre-compiled. Hex values though cannot be stored in the ListItemData property of VB4/5 combo and list boxes.

Listing 2.
A novice VB developer would probably proceed to program a color selection combo as follows :

Const RED = &HFF&
Const GREEN = &HFF00&
Const BLACK = &H0&
Const BLUE = &HFF0000

Sub Form_Load()
'....other form load code
Combo1.AddItem "Magenta"
Combo1.AddItem "Red"
Combo1.AddItem "Green"
Combo1.AddItem "Black"
Combo1.AddItem "Blue"
'... more form load code
End Sub

Sub Combo1_Click()
Select Case Combo1.ListIndex
	Case 0
		Me.Backcolor = MAGENTA
	Case 1
		Me.Backcolor = RED
	Case 2
		Me.Backcolor = GREEN
End Select
End Sub

The problem with this code is that it is dependant upon the ListIndex of the selected Item from the combo box. If subsequently the Sorted property of the combo is set to True then this code would fail. A better coding solution is to assign the hex color value to the ItemData property as Items are added to the combo -

....Constant declarations as before...

Sub Form_Load()
'....other form load code
Combo1.AddItem "Magenta"
Combo1.ItemData(Combo1.NewIndex) = MAGENTA

End Sub

Sub Combo1_Click()
	Me.BackColor = Combo1.ItemData(Combo1.ListIndex)
End Sub

This removes the dependency of the functionality of the combo box on its Sorted property but still there remains dependencies upon the constant declarations and the language that is used for Item values. Also as it is written the code is not very reusable nor is it easily maintained because it is hardwired to fill only a control named Combo1.

My proposed solution is as follows -

Listing 2 -- My solution.

Sub Form_Load()
dim languageID as long
' some API utility to retrieve the language ID value from the OS
languageID = LanguageID_Get()
' Fill combo1 with Items and data based upon the language
cboBackColor_Init Combo1, languageID	
' ... other form load code
End Sub

Sub cboBackColor_Init(C as Control, languageID as Long)
dim I as Integer
dim IDs() as long
dim Items() as string
Call BackColorData_Get(IDs(),Items(),languageID)	
' Utility to retrieve Item and Itemdata values from a database
For I = 0 to Ubound(IDs)
	C.AddItem Items(I)
	C.ItemData(C.NewIndex) = IDs(I)
' Set the default value for the control
C.ListIndex = 0
End Sub

' Retrieve the appropriate Backcolor data given the languageID
Sub BackColorData_Get (IDs() As Long, Items() As String, languageID As Long)
' Name of the column containing the Items in Language languageID
Dim Ilang As String 
Dim db As database
Dim dat As table
Dim i As Integer
' Find the column name containing the Items for the given languageID
Ilang = "Language" & Trim(Str(languageID))
Set db = OpenDatabase("id4.mdb")
Set dat = db.OpenTable("Example_Tab")
Do Until dat.EOF
    ReDim Preserve IDs(0 To i)
    ReDim Preserve Items(0 To i)
    IDs(i) = dat!ItemData
    Items(i) = dat.Fields(Ilang)
    i = i + 1

End Sub
Tables :

ID ItemData Language9 Language1
1 0 Black Hitam
2 128 Maroon Menganggur
3 32768 Green Hijau
4 32896 Olive Sausa
5 8388608 Navy Angkatan Laut
6 8388736 Purple Ungu
7 8421376 Teal Biru Kelabu
8 8421504 Grey Kelabu
9 12632256 Silver Perak
10 255 Red Merah
11 65280 Lime Getah
12 65535 Yellow Kuning
13 16711680 Blue Biru
14 16711935 Fuschia Ungu Mudah
15 16776960 Aqua Biru Mudah
16 16777215 White Putih

Notice that in the Example_Tab that the hex values for each color has been replaced with a long integer value. By placing these values in a table the dependency upon constant declarations has been removed. The ID column in this table has been included for consistency purposes as I always use a counter field to track physical record numbers in tables(more on this subject later).

The above code has been written in VB3 but because all dependencies have been removed it can be ported to both VB4 or VB5 without further modification. However because VB4 and VB5 list and combo boxes have List(Items) and List(ItemData) properties you may want to store the property values in the control itself but this would mean that the control would be language dependant. Note that if you do choose to store ItemData values in the List(ItemData) property of a control you will have to convert the hex values into long integer values. Also the subroutine cboBackColor_Init uses C as Control as an argument. This means that you can have a choice of applying the Init routine to either a combo box or list box control or any other custom control that has Item and ItemData properties.


To convert hex values into long integer values follow these steps :

  1. Start the Windows Calculator application (calc.exe) choosing the Scientific view.
  2. Press the Hex option button and enter the hex value that you want to convert to a decimal value.
  3. Press the Dec option button to convert the entered hex value to a decimal value.

This covers data that is stored in list controls but what about data that is stored in an RDB ? I've had the pleasure(more of a pain really) lately to work on some legacy insurance applications that are mainframe based written in COBOL and without exception the data structure of these applications use indexed character fields. This causes all sorts of problems when the application is ported to a client/server architecture as in many cases the indexed character field is a composite of codes that is meant to convey meaningful information to a user about the attributes of an insurance policy. Without exception these applications have failed miserably when they have been ported to unicode supported operating systems in an attempt to provide and support the one set of code to the Asian Pacific region. The dependence upon indexed character fields has meant that multiple versions of the same application have had to be written and maintained , an exceedingly expensive mistake.

I have tried to argue the point with the project managers that I have worked for but I have now given up on TC's (Technically Challenged) who see the world through Big Blue colored glasses.

Typically an insurance policy number has the following format - "XXXXXXXXXX" - 10 character indexed field. When a policy is loaded the policy number transferred from the Policy face page document and these policy numbers contained coded information about the type of insurance policy say "TRAD000001" or "NONTRAD0001" where the codes "TRAD" and "NONTRAD" indicate weather the policy is TRADitional or NONTRADitional. To me this is the wrong way of storing policy information. If you think of a policy as an "Object"(anything can be an "Object") then the type of policy will be a "Property" of the policy object not an attribute of the policy number.

A typical data structure of a policy face pages table would be in the form

        PolicyNo        Char(10) (Indexed - no duplicates)
        ' ....Other fields

I think it is a better Idea to build the following structure

	' system generated record counter, Indexed with no duplicates
	ID		Long	
	' system generated from a combination of PolicyType with 
	' the ID value of the Policy record that is being created - 
	' Indexed with no duplicates but used for display purposes only.
	PolicyNo	Char(10)
	' ItemData value read from a list control or joined from 
	' a table of PolicyTypes - Indexed with duplicates allowed.
	PolicyType	Long
	' ...Other Policy properties
	' Long	ItemData values read from a list control or joined from 
	' other tables of PolicyProperty tables.

Such a structure would free policy records from being language dependant and would allow the one application to service a number of sales branches in different countries and allow for the easy consolidation of policy information for head office reporting and all in all reducing policy maintenance costs.

I could go further on this subject but I feel that because the insurance industry is dominated by a certain manufacturer my opinions would fall on deaf ears.


Numbers are important to describe property attributes of things in the Object Orientated Programming world and if computers are to talk across the boundaries of Locale , Language and syntax then there is a need to establish global standards for the values of these "magic" numbers that describe the properties of objects. If there is no consensus on the implementation and use of these numbers then allot of OOP applications will be spelt backwards(POO).

Computers are still dumb no matter how pretty a face the OS has, weather its' green or GUI, the independence of applications will depend upon the Nut that holds the keyboard which makes the decision about weather to describe something with a number or a text string. As Supertramp tells us "Take the Long Way Home".


MS gets it wrong!

Following is a listing of the CTRY constants used to describe Country name constants in the API32.

Public Const CTRY_AUSTRALIA = 61             ' Australia
Public Const CTRY_AUSTRIA = 43               ' Austria
Public Const CTRY_BELGIUM = 32  	     ' Belgium
Public Const CTRY_BRAZIL = 55                ' Brazil
Public Const CTRY_CANADA = 2                 ' Canada
Public Const CTRY_DEFAULT = 0
Public Const CTRY_DENMARK = 45               ' Denmark
Public Const CTRY_FINLAND = 358              ' Finland
Public Const CTRY_FRANCE = 33                ' France

' etc....

Upon first inspection one will realise that the integer values assigned to these constants are the IDD(International Direct Dial) numbers of the countries that they describe. This would be sufficient if unique countries had unique IDD numbers but unfortunately this is not the case in the real world.

Provided with this article is country.mdb which contains a complete listing of country names, three character short codes and IDD numbers.

Image of How-To Icon You can download Rob's Sample files for this article at

Written by: by Rob Parsons
March 97