Error with Multi-value field

Question

Hi:

I'm using a multi-select field (combobox) to allow users to select multiple values for a particular field.  I've created an Audit Tracking/Change Log table to capture changes made to this, and other fields.  In the BeforeUpdate Event of the multi-select field, I have the following code:

Private Sub DocSourceID_BeforeUpdate(Cancel as Integer)

'if user select the same value that was already in the field, cancel update

If Me.DocSourceID.OldValue = Me.DocSourceID then

Cancel = True

Me.DocSourceID.Undo

End if

End Sub

In the AfterUpdate Event, I have the following code:

Private Sub DocSourceID_AfterUpdate()

Me.Dirty = False

'Call a sub that logs new value in a Change Log Table

Call sLogChange("Doc Source",Me.ID,UsersName,Me.DocSourceID)

End Sub

When I run either the BeforeUpdate or AfterUpdate, I get the following error message: Run-Time error '13' Type mismatch.  Any idea why I get this error?  The datatype for DocSourceID in the underlying table is Number/Long Integer.  In the table datasheet view I see multiple values stored in the field, but separated by commas (e.g., 3,8).  In the Change Log table, new values (e.g.,, DocSourceID) are stored in a text field (because I'm logging changes for both text and numbers in the Log, and I can always convert the numbers from text to number should I need to do so)

I know that multiselect fields are new to >=2007.  Is there some nuance to these field types that I need to account for in order to read the contents of the field?

What am I missing?

Thanks,

Manuel

Answer 1

The Value and OldValue properties of a multiselect combo box are both arrays of variants, and you can't compare arrays for equality using "If Array1 = Array2 Then ...".  What you could do is first, check that the arrays have the same number of elements (by checking with UBound()), and if they do, then loop through the elements of the two arrays and see if each element of the .OldValue array is equal to the corresponding element of the .Value array. 

I'm not completely sure if the values are always in the same order;  you might need to check if each individual value in the .OldValue array is in the .Value array somewhere.  I don't know about that, as I haven't worked with these controls much.