Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 42 votes, 5.00 average. Display Modes
Old 02-25-2011, 08:24 AM   #1
Frank X
Newly Registered User
 
Join Date: Feb 2011
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Frank X is on a distinguished road
Run Time Error 3265 - Item not found in this collection

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?

Frank X is offline   Reply With Quote
Old 02-25-2011, 08:29 AM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,809 Times in 1,573 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Run Time Error 3265 - Item not found in this collection

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-25-2011, 09:09 AM   #3
Frank X
Newly Registered User
 
Join Date: Feb 2011
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Frank X is on a distinguished road
Re: Run Time Error 3265 - Item not found in this collection

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


Last edited by boblarson; 02-25-2011 at 09:19 AM. Reason: add code tags and indenting for easier reading
Frank X is offline   Reply With Quote
Old 02-25-2011, 09:20 AM   #4
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,809 Times in 1,573 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Run Time Error 3265 - Item not found in this collection

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

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-25-2011, 09:30 AM   #5
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,809 Times in 1,573 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Run Time Error 3265 - Item not found in this collection

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-25-2011, 09:42 AM   #6
Frank X
Newly Registered User
 
Join Date: Feb 2011
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Frank X is on a distinguished road
Re: Run Time Error 3265 - Item not found in this collection

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?
Frank X is offline   Reply With Quote
Old 02-25-2011, 09:46 AM   #7
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,809 Times in 1,573 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Run Time Error 3265 - Item not found in this collection

Quote:
Originally Posted by Frank X View Post
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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 02-25-2011, 09:49 AM   #8
Frank X
Newly Registered User
 
Join Date: Feb 2011
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Frank X is on a distinguished road
Re: Run Time Error 3265 - Item not found in this collection

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

Frank X is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error 3265 - Item not found in collection accessaspire219 Reports 18 09-04-2009 12:07 PM
Error 3265 Item not found on this collection... Villarreal68 Modules & VBA 3 04-07-2009 09:24 AM
Error 3265: Item not found in this collection PRodgers4284 Forms 2 04-06-2009 09:11 AM
Item not found in this collection error SeriesIII Modules & VBA 5 03-04-2009 07:22 AM
Item not Found in this Collection error Kenshiro Modules & VBA 12 05-12-2006 11:09 AM




All times are GMT -8. The time now is 04:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World