run time error 3265

[VTTW]

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??

[Twodogs]

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?

[Thread1]

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

..
..
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..

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..

[VTTW]

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.

Thanks again,
Mark