Run Time Error 3265 - Item not found in this collection (1 Viewer)

Frank X · Feb 25, 2011

I'm new to Access and Visual Basic and I keep getting the message "Error #:3265 - Item not found in this collection" I've checked and cannot find the problem. If I remove this line of code, the problem happens on the next line. It doesn't seem to recognize the recordset? Can anyone help me on this?

boblarson · Feb 25, 2011

It would help us if you posted the code for the entire procedure where this is happening. Just telling us you have this problem is like going to the doctor and saying doc my leg hurts but not showing him where on your leg it hurts.

Frank X · Feb 25, 2011

Bob:
Here is the code:

Code:

Option Compare Database
Option Explicit
 
Private Function Conversion()
    Dim dbsOmya_2 As DAO.Database
    Dim rstSAP_SALES1 As DAO.Recordset
    Dim InStrB As String
    Dim INCO_2X As String
    Dim strSQL As String
    Dim Count As Integer
    Dim i As Integer
    Dim value As String
    Dim FIX_SC As String
    On Error GoTo ErrorHandler
    Set dbsOmya_2 = CurrentDb
    'Open a recordset on all records from the rstSAP_SALES1 table that have
    strSQL = "SELECT * FROM SAP_SALES1;"
    Set rstSAP_SALES1 = dbsOmya_2.OpenRecordset(strSQL, dbOpenDynaset)
 
    'If the recordset is empty, exit.
    If rstSAP_SALES1.EOF Then Exit Function
    Count = 1
 
 
    With rstSAP_SALES1
        Do Until rstSAP_SALES1.EOF
            .Edit
 
            ' CONVERT SHIP_QTY TO rstSAP_SALES1![SALE_QTY] - DRY '
 
            If rstSAP_SALES1![PKG_FORM] <> "01" Then
                If rstSAP_SALES1![SU] = "TON" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY]
                End If
                If rstSAP_SALES1![SU] = "TO" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY] * 1.1023
                End If
                If rstSAP_SALES1![SU] = "LB" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY] / 2000
                End If
                If rstSAP_SALES1![SU] = "DTN" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY]
                End If
                If rstSAP_SALES1![SU] = "DLB" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY] / 2000
                End If
                If rstSAP_SALES1![SU] = "DTO" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY] * 1.1023
                End If
                If rstSAP_SALES1![SU] = "KG" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY] / (2.2046 * 2000)
                End If
            End If
 
            rstSAP_SALES1![SHIP_WGT_USTONS] = rstSAP_SALES1![SALE_QTY]
            rstSAP_SALES1![SHIP_WGT_MTONS] = rstSAP_SALES1![SALE_QTY] / 1.1023
 
            ' CONVERT SALE_QTY TO rstSAP_SALES1![SALE_QTY] - SLURRY '
 
            If rstSAP_SALES1![PKG_FORM] = "01" Then
                If rstSAP_SALES1![SU] = "TON" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY] * rstSAP_SALES1![SLURRYPC]
                End If
                If rstSAP_SALES1![SU] = "TO" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY] * 1.1023 * rstSAP_SALES1![SLURRYPC]
                End If
                If rstSAP_SALES1![SU] = "LB" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY] / 2000 * rstSAP_SALES1![SLURRYPC]
                End If
                If rstSAP_SALES1![SU] = "DTO" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY] * 1.1023 * rstSAP_SALES1![SLURRYPC]
                End If
                If rstSAP_SALES1![SU] = "KG" Then
                    rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY] / (2.2046 * 2000) * rstSAP_SALES1![SLURRYPC]
                End If
            End If
 
            ' Create the Order Type '
 
            If rstSAP_SALES1![CUSTCODE] < "2" Then
                rstSAP_SALES1![ORDERTYP] = "09"
            Else
                rstSAP_SALES1![ORDERTYP] = "01"
            End If
 
            ' Covert Freight Terms code '
 
            INCO_2X = rstSAP_SALES1![INCO_2]
 
            rstSAP_SALES1![FRTTERMC] = "XXX"
 
            If rstSAP_SALES1![INCOT] = "CIP" Then
                If InStrB(INCO_2X, "ADD") <> 0 Then
                    rstSAP_SALES1![FRTTERMC] = "PPA"
                End If
                If InStrB(INCO_2X, "PPA") <> 0 Then
                    rstSAP_SALES1![FRTTERMC] = "PPA"
                End If
                If InStrB(rstSAP_SALES1![INCO_2], "PPD") <> 0 Then
                    rstSAP_SALES1![FRTTERMC] = "PPD"
                End If
            End If
 
            If rstSAP_SALES1![INCOT] = "FCA" Then
                rstSAP_SALES1![FRTTERMC] = "COL"
            End If
            If rstSAP_SALES1![INCOT] = "EXW" Then
                rstSAP_SALES1![FRTTERMC] = "WLC"
            End If
            If rstSAP_SALES1![FRTTERMC] = "XXX" Then
                rstSAP_SALES1![FRTTERMC] = rstSAP_SALES1![INCOT]
            End If
 
            ' Attempt to fix sc = 70 99
 
            FIX_SC = "True"
 
            If rstSAP_SALES1![TRPT] = " " Then
                FIX_SC = "False"
            End If
            If rstSAP_SALES1![TRPT] = "70" Then
                FIX_SC = "False"
            End If
            If rstSAP_SALES1![TRPT] = "99" Then
                FIX_SC = "False"
            End If
            If rstSAP_SALES1![SC] = "70" Then
                If FIX_SC = "True" Then
                    rstSAP_SALES1![SC] = rstSAP_SALES1![TRPT]
                End If
            End If
            If rstSAP_SALES1![SC] = "99" Then
                If FIX_SC = "True" Then
                    rstSAP_SALES1![SC] = rstSAP_SALES1![TRPT]
                End If
            End If
 
            ' Fix MODE
 
            rstSAP_SALES1![Mode] = "UNK"
 
            If rstSAP_SALES1![Mode] = "UNK" Then
                If rstSAP_SALES1![PKG_FORM] = "01" Then
                    If rstSAP_SALES1![SALE_QTY] < 49 Then
                        rstSAP_SALES1![Mode] = "TSL"
                    Else
                        rstSAP_SALES1![Mode] = "RSL"
                    End If
                End If
            End If
 
            If rstSAP_SALES1![Mode] = "UNK" Then
                If rstSAP_SALES1![PKG_FORM] = "02" Then
                    If rstSAP_SALES1![SALE_QTY] < 49 Then
                        rstSAP_SALES1![Mode] = "TBL"
                    Else
                        rstSAP_SALES1![Mode] = "RBL"
                    End If
                End If
            End If
 
            If rstSAP_SALES1![Mode] = "UNK" Then
                If rstSAP_SALES1![PKG_FORM] = "13" Then
                    If rstSAP_SALES1![SALE_QTY] < 49 Then
                        rstSAP_SALES1![Mode] = "TRL"
                    Else
                        rstSAP_SALES1![Mode] = "RBX"
                    End If
                End If
            End If
 
            If rstSAP_SALES1![Mode] = "UNK" Then
                If rstSAP_SALES1![PKG_FORM] = "35" Then
                    If rstSAP_SALES1![SALE_QTY] < 49 Then
                        rstSAP_SALES1![Mode] = "TRL"
                    Else
                        rstSAP_SALES1![Mode] = "RBX"
                    End If
                End If
            End If
 
            If rstSAP_SALES1![Mode] = "UNK" Then
                If rstSAP_SALES1![PKG_FORM] = "41" Then
                    If rstSAP_SALES1![SALE_QTY] < 49 Then
                        rstSAP_SALES1![Mode] = "TRL"
                    Else
                        rstSAP_SALES1![Mode] = "RBX"
                    End If
                End If
            End If
 
            If rstSAP_SALES1![Mode] = "TSL" Then
                rstSAP_SALES1![Mode2] = "TK"
            End If
            If rstSAP_SALES1![Mode] = "TBL" Then
                rstSAP_SALES1![Mode2] = "TK"
            End If
            If rstSAP_SALES1![Mode] = "TRL" Then
                rstSAP_SALES1![Mode2] = "TK"
            End If
            If rstSAP_SALES1![Mode] = "RSL" Then
                rstSAP_SALES1![Mode2] = "RR"
            End If
            If rstSAP_SALES1![Mode] = "RBL" Then
                rstSAP_SALES1![Mode2] = "RR"
            End If
            If rstSAP_SALES1![Mode] = "RBX" Then
                rstSAP_SALES1![Mode2] = "RR"
            End If
 
            ' Export Processing
 
            If rstSAP_SALES1![Group] = "ZEXP" Then
                rstSAP_SALES1![CUSTTYPE] = "EXPORT"
            End If
 
            If rstSAP_SALES1![Group] = "ZEXP" Then
                rstSAP_SALES1![ORDTYPE] = "01"
            End If
 
 
 
            ' Change Order type for Consignment Orders
 
 
            'If rstSAP_SALES1![ORTYPNEW] <> " " Then'
            'rstSAP_SALES1![ORDTYPE] = rstSAP_SALES1![ORTYPNEW]'
            'End If'
 
 
            .Update
            .MoveNext
            Count = Count + 1
        Loop
    End With
 
    rstSAP_SALES1.Close
    dbsOmya_2.Close
    Set rstSAP_SALES1 = Nothing
    Set dbsOmya_2 = Nothing
    Exit Function
ErrorHandler:
    MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Function

boblarson · Feb 25, 2011

Next time please use code tags whenever posting code (especially when it is a long bit of code).

boblarson · Feb 25, 2011

What is probably happening is that you have somewhere used a field name in your code which doesn't exist in the recordset. A common error is just some misspelling (leaving out a space, or using an underscore instead of a space when the field name in the table actually has a space, etc.)

So check all of the code like this:

rstSAP_SALES1![CUSTTYPE]

to make sure that the field you are referencing (in this case CUSTTYPE, but it could apply to any of them) is in fact in the SAP_SALES1 table or query (whichever it is).

Frank X · Feb 25, 2011

Bob:

I've checked this a hundred times. The field does exist. And it appears to make no difference. I've removed lines of code and yet any reference to any field causes the same error. The first line it fails on is:

If rstSAP_SALES1![PKG_FORM] <> "01" Then

But when I removed this line, it failed on the next and so forth

If rstSAP_SALES1![SU] = "TON" Then
rstSAP_SALES1![SALE_QTY] = rstSAP_SALES1![SHIP_QTY]

What amy I missing?

boblarson · Feb 25, 2011

Frank X said:
Bob:

I've checked this a hundred times. The field does exist.

You realize I wasn't talking about that one specific field but ALL of them? You would need to check all of your code everywhere that recordset object refers to a field. And the code will fail right away and not fall on the line that is causing the problem in this case because it is trying to compile and can't because you are referring to a field that doesn't exist.

Frank X · Feb 25, 2011

Bob:
Thanks. I didn't realize it was ANY field.
Frank

Run Time Error 3265 - Item not found in this collection (1 Viewer)

Frank X

New member

boblarson

Smeghead

Frank X

New member

boblarson

Smeghead

boblarson

Smeghead

Frank X

New member

boblarson

Smeghead

Frank X

New member

Similar threads

Users who are viewing this thread