run time error 3265

The code below loops thru a listview & saves the items to a db. when ever there is only one item in the listview, everything is ok. But when there is more than one item, I get a run-time error '3265', "item cannot be found in the collection corresponding to the requested name or ordinal."
~~~~~~~~~~

Sub save_inv()
Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim connStr As String
Dim i As Integer
Dim strSql As String
Dim terms As Integer
Dim pono As String
Dim sINV As Long
Dim DwgID As String
Dim RcdCount As Integer

Set adoConn = New ADODB.Connection
Set adoRs = New ADODB.Recordset

connStr = "Provider=Microsoft.Jet.OLEDB.3.51;" _
& "Data Source=C:\program files\DCI\db1.mdb"

adoConn.Open connStr

adoRs.Open "job", adoConn, adOpenKeyset, adLockOptimistic
adoRs.Find "[jobnumber] = '" & Invoice.cmbJobNumber & "'" 'find compan
terms = adoRs!terms
pono = adoRs!pono
adoRs.Close

adoRs.Open "sequence", adoConn, adOpenKeyset, adLockOptimistic
adoRs.Find "[jobno] = '" & Invoice.cmbJobNumber & "'"
RcdCount = adoRs!inv + 1
adoRs.Close

adoRs.Open "invoice", adoConn, adOpenKeyset, adLockOptimistic


If Invoice.cmbBillType.ListIndex = 0 Then
With Invoice.ListView1
For i = 1 To .ListItems.Count
adoRs.AddNew
adoRs!jobno = Invoice.cmbJobNumber.Text
----> adoRs!sequence = RcdCount '<--error occurs here
adoRs!pono = pono
adoRs!Date = Date
adoRs!Type = Invoice.cmbBillType.ListIndex + 1
adoRs!duedate = Date + terms
adoRs!dwgs = Invoice.ListView1.ListItems(i).SubItems(1)
adoRs!revno = Invoice.ListView1.ListItems(i).SubItems(2)
adoRs!wgt = Invoice.ListView1.ListItems(i).SubItems(5)
adoRs!amount = Invoice.total
adoRs.Update
adoRs.Close
adoRs.Open "dwglog", adoConn, adOpenKeyset, adLockOptimistic
adoRs.MoveFirst
DwgID = Invoice.ListView1.ListItems(i).Key
sINV = Right$(DwgID, Len(DwgID) - 2)
adoRs.Find "[dwgid] = '" & sINV & "'"
adoRs!deleted = True
adoRs!invno = RcdCount
adoRs.Update
Next i
End With
End If
adoRs.Close

adoRs.Open "sequence", adoConn, adOpenKeyset, adLockOptimistic
adoRs.Find "[jobno] = '" & Invoice.cmbJobNumber & "'"
adoRs!inv = RcdCount
adoRs.Update
adoRs.Close


adoConn.Close
Set adoRs = Nothing
Set adoConn = Nothing
Call invmod.load_inv
Call invmod.pop_lst2
Call invmod.lv1_partial

End Sub



I don't understand why the error doesn't occur during the first loop thru the listview, but then does during the second. Any Ideas??

adoRs!dwgs = Invoice.ListView1.ListItems(i).ListSubItems("invoice_no").text
adoRs!revno = Invoice.ListView1.ListItems(i).ListSubItems("customer").Text
adoRs!wgt = Invoice.ListView1.ListItems(i).ListSubItems("amount").Text

is how I get the values.

Where exactly is your code breaking?

Are you absolutely sure that you don't have a null value in any of the items you're trying to retreive?

You must populating the INVOICE table and at the same time you are trying to update another table - GWGLOG - using the same recordset object?! Although, this is possible but in-between loops you forgot to close the GWGLOG and re-open the INVOICE table...

code

Quote:

---

..
..
adoRs.Open "dwglog", adoConn, adOpenKeyset, adLockOptimistic
adoRs.MoveFirst
DwgID = Invoice.ListView1.ListItems(i).Key
sINV = Right$(DwgID, Len(DwgID) - 2)
adoRs.Find "[dwgid] = '" & sINV & "'"
adoRs!deleted = True
adoRs!invno = RcdCount
adoRs.Update
...
'
'AT THIS POINT THE adoRS is the DWGLOG
'
'
...
Next i

---

However, much better if you assign another recordset variable for GWGLOG, and open these recordsets (INVOICE and GWGLOG) before entering the loop..

Quote:

---

adoRs.Open "invoice", adoConn, adOpenKeyset, adLockOptimistic
adoRs2.Open "dwglog", adoConn, adOpenKeyset, adLockOptimistic
...
For ...
Next ...
...
adoRs.Close
adoRs2.Close

---

Or Finally, you can directly update the GWGLOG using SQL statement with the EXECUTE method of the connection object..


adoConn.Execute "UPDATE gwglog SET deleted = TRUE, invno = " & RcdCount & " WHERE dwgid = '" & sINV & "'"


Hope it helps..

:)

Thanks TwoDogs & Thread1,

Thread1 was correct with the opening & closing of db's. To shed a little more light, this little snipet of code is for writing an invoice for multiple drawings. Once the invoice is written, the code opens my drawing log & checks that the drawing has been invoiced. :cool:

Thanks again,
Mark