multi-select list box, multi-user environment, error 3421

[sub5mango]

Hello
I am using the following code to update tables from unbound controls on a form using rows selected in a multi-select list box. The code works fine in a single user environment (ie the problem is not a data type issue).

But when I have two people working on the form, person 2 (see circumstances) gets the error 3421 (in the code I have noted which line debug goes to):

CIRCUMSTANCES:
person 1 selects a few rows in the listbox and clicks cmdupdate button
person 2 selects all the rows in the list box and clicks the cmdupdate button

nb could it be something to do with me setting the listbox source to "" and then reinstating the source code (I have done this becuase on requery the list box cleared the rows but didn't clear my slected - black - lines hence was selecting phantom rows)

CODE: (nb I have tried substituting Recordset with DAO.Recordset but it didn't make any difference.


Private Sub cmdupdate_Click()

Dim dbs As Database
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim frm As Form
Dim ctl As Control
Dim varItm As Variant

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("tbl1")
Set rst2 = dbs.OpenRecordset("tbl2")
Set frm = Forms!frm1
Set ctl = frm!lstmulti

For Each varItm In ctl.ItemsSelected

rst1.AddNew
'here i get a 3421 The Microsoft Jet database engine was unable to convert data into the type required
'by a method or property. This problem typically occurs if you use an argument of one type when a method
'or property expects another type.
rst1!data100 = ctl.Column(0, varItm)
rst1!data200 = ctl.ItemData(varItm)
rst1.Update

rst1.MoveLast

rst2.AddNew
rst2!data300 = ctl.ItemData(varItm)
rst2!data400 = ctl.Column(5, varItm)
rst2!data400 = rst1!data400
rst2.Update

Next varItm

‘this code is in as a well to make the listbox not select blanks after the requery
Dim strRowS As String
strRowS = ctl.RowSource
ctl.RowSource = ""
ctl.RowSource = strRowS

Me! lstmulti.Requery
If ctl.ListCount = 0 Then
DoCmd.GoToControl " lstmulti"
End If
End Sub

 

[modest]

You should format your code. It would make things easier on us to follow your program.

furthermore try identifying what recordset type you're using

Set rst1 = dbs.OpenRecordset("tbl1")
Set rst2 = dbs.OpenRecordset("tbl2")

to

Set rst1 = dbs.OpenRecordset("tbl1", dbOpenDynaset)
Set rst2 = dbs.OpenRecordset("tbl2", dbOpenDynaset)


if what you're saying about the problem (not a datatype conflict despite what it says) is true, this may be a lock issue.