Multi Select List Box problem

[Guest]

Hello,
I have a Multi Select list box in a Form (A2k). The following is the Code I
have written. The Code works fine if only one item is selected in the List
Box. When two or more lines are selected the first pass through the "For Each
varItm ... Next varItm" works fine. The problem is in the second pass - I get
an error "Item not found in this collection" at the "rstMtrls_Add!fkPOMainID
= rst!fkPOMainID" line. The subsequent lines also display the same error in
Break mode. Cant seem to figure out why. Any help?

TIA
Anand

Set dbs = CurrentDb
Set ctl = Me.lstPOItems

For Each varItm In ctl.ItemsSelected
intMaterialsID = ctl.ItemData(varItm)
intRevNo = ctl.Column(1, varItm)

stSQL = "SELECT tblPOMaterials.fkPOMainID, tblPOMaterials.ItemNo
FROM tblPOMaterials WHERE tblPOMaterials.pkMaterialsID = " & intMaterialsID &
"
AND tblPOMaterials.RevisionNo = " & intRevNo & ""

Set rst = dbs.OpenRecordset(stSQL)
With rst
Set rstMtrls_Add = dbs.OpenRecordset("tblPOMaterials")
With rstMtrls_Add
rstMtrls_Add.AddNew
rstMtrls_Add!pkMaterialsID = intMaterialsID
rstMtrls_Add!RevisionNo = intRevNo + 1
rstMtrls_Add!fkPOMainID = rst!fkPOMainID
rstMtrls_Add!ItemNo = rst!ItemNo
rstMtrls_Add.Update
End With
End With

Next varItm

 

[Guest]

Not sure what is causing the problem, but I think I would have done it like
below.

Only real difference is that I am referring to the ctrl.Column(0, varItem)
(assumes bound column is 1) instead of the ItemData, and that I don't think
you need to use the "With / End With" for the source recordset. I also moved
the Open of the rstMtrls_Add outside the For/Next loop. I also like to use
the format rst("FieldName") instead of rst!FieldName, but this is just a
personal preference.

Set dbs = CurrentDb
Set ctl = Me.lstPOItems

'Moved this outside the For/Next loop
Set rstMtrls_Add = dbs.OpenRecordset("tblPOMaterials")

For Each varItm In ctl.ItemsSelected
intMaterialsID = ctl.Column(0, varItm)
intRevNo = ctl.Column(1, varItm)

stSQL = "SELECT fkPOMainID, ItemNo " _
& "FROM tblPOMaterials " _
& "WHERE pkMaterialsID = " & intMaterialsID _
& " AND RevisionNo = " & intRevNo

Set rst = dbs.OpenRecordset(stSQL)

With rstMtrls_Add
rstMtrls_Add.AddNew
rstMtrls_Add("pkMaterialsID") = intMaterialsID
rstMtrls_Add("RevisionNo") = intRevNo + 1
rstMtrls_Add("fkPOMainID") = rst("fkPOMainID")
rstMtrls_Add("ItemNo") = rst("ItemNo")
rstMtrls_Add.Update
End With

Next varItm

rstMtrls_Add.Close
SET rstMtrls_Add = Nothing
rst.close
SET rst = Nothing

HTH
Dale

 

[Guest]

Hello Dale,
Thanks for the suggestions. I implemented them and found that it made no
difference. Still have the same problem.... any other likely solutions?

Anand