MS ACCESS / VBA – REFERRING TO A LISTBOX COLUMN BY NAME

Believe it or not, but you can’t refer to items in a listbox or combobox by their column name, only their ordinal (number based on their position in the listbox) which makes things difficult if the listbox is taken from a constantly-changing table or XML file. To get round this you can refer to the first line of the listbox which carries the field names, pull them into an array, and then assign a name to some variables based on finding the right entry in the array. Then, it doesn’t matter if the contents of the listbox change positions because your code can now refer to things by name.

 For i = 0 To Me.List1.ListCount - 1
 strList1 = strList1 & "," & Me!List1.Column(i, 0)
 Next
 array1 = Split(Mid(strList1, 2), ",")
 vONumX = IsInArray("oh_order_number", array1)
 vNameX = IsInArray("ih_delivery_name", array1)
 vCoidX = IsInArray("Account Number", array1)
 vCredX = IsInArray("ccd_credit_limit", array1)
 vBalaX = IsInArray("ccdt_balance", array1)
 vINumX = IsInArray("ih_number", array1)
 vStatX = IsInArray("ih_credit", array1)
 vDeliX = IsInArray("delivery_gross", array1)
 vShipX = IsInArray("delivery_address", array1)
 vHoldX = IsInArray("ccd_onhold", array1)

Now, change your code to use the variables, like this:

vCoid = Me.List1.Column(vCoidX, x)

Instead of:

vCoid = Me.List1.Column(2, x) 'columns start from 0