Duplicating a field that is set to "allow multiple values"...How?

[John Harrington]

I'm writing a sub to duplicate records in my database. I'm writing it
myself because I've found that the built in duplicate button in access
is insufficient (for one, it doesn't duplicate records in the
subforms).

One of my fields is set to "allow multiple values". Yes, I know this
feature is frowned on, but it's a specific requirement, so there's
nothing I can do.

The trouble is, all of my records duplicate fine, except this one.
When I have the corresponding line of code uncommented (see "no. 1" in
code snippet below), I get the error "datatype conversion error".
Fair enough.

So I rerun the sub putting the combo box data into a msg box (see "no.
2" in code snippet below) just to see what's in there. I get a
message "invalid use of null". So I guess there is no data in
there...is that right?

If so, how do I duplicate the data???

With Me.RecordsetClone
.AddNew
'Names have been made generic for privacy
!field01 = Me.txtBox01
!field02 = Me.txtBox02
!field03 = Me.txtBox03
!field04 = Me.txtBox04
!field05 = Me.txtBox05
!field06 = Me.txtBox06
!field07 = Me.txtBox07
!field08 = Me.txtBox08
!field09 = Me.txtBox09
!field10 = Me.txtBox10
!field11 = Me.txtBox11
!field12 = Me.txtBox12
!field13 = Me.txtBox13
!field14 = Me.txtBox14
!field15 = Me.txtBox15
!field16 = Me.txtBox16
' field17 = Me.txtBox17 ' no. 1
MsgBox Me.txtBox17 ' no. 2
!field18 = Me.txtBox18
!field19 = Me.txtBox19
!field20 = Me.txtBox20
!field21 = Me.txtBox21
.Update

'Display the new duplicate.
Me.Bookmark = .LastModified
End With

Further info: The msgbox test was just an experiment. Entering values
into the combo box on the form works fine. You can select multiples
and a comma-delimited list of text values is written to the table.
The datatype of the field is "Text". The ControlSource of the combo
box on the form is the field itself (field17) in the table. The row
source is simply a query on a table that provides the combo items in
field17, selecting the table, and the specific field in the table that
holds the values. Again, this all works fine to write the right
values to the main table.


Many thanks in advance,
John

 

[John Harrington]

              ' field17 = Me.txtBox17 ' no. 1
              MsgBox Me.txtBox17  ' no. 2

Sorry, in my haste to replace the field and control names, I neglected
to make the "no. 1" and "no. 2" lines above read:

' field17 = Me.cboBox17 ' no. 1
MsgBox Me.cboBox17 ' no. 2