Results 1 to 6 of 6

Thread: Run Time Error 3265

  1. #1
    duckie10 is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    16

    Run Time Error 3265

    This is my code

    Private Sub ZIP_AfterUpdate()
    Dim strSQL As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = CurrentProject.Connection

    Set rs = New ADODB.Recordset

    St.Value = ZIP.Column(2)
    City.Value = ZIP.Column(1)
    'me.Refresh

    strSQL = "SELECT MANAGERID, REPRESENTATIVEID, Dept " & _
    "FROM Territory " & _
    "WHERE Startscf <= '" & ZIP.Value & "' " & _
    "AND Endscf >= '" & ZIP.Value & "' "

    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

    Do While Not rs.EOF
    If rs.Fields("Dept").Value = "BIO" Then
    [MANAGER B].Value = rs.Fields("MANAGER.ID").Value
    [SALESREP B].Value = rs.Fields("REPRESENTATIVE.ID").Value
    End If

    If rs.Fields("Dept").Value = "FER" Then
    [MANAGER F].Value = rs.Fields("MANAGER.ID").Value
    [REFERRED REP F].Value = rs.Fields("REPRESENTATIVE.ID").Value
    End If

    If rs.Fields("Dept").Value = "SER" Then
    [REFERRED MANAGER S].Value = rs.Fields("ManagerID").Value
    [REFERRED REP S].Value = rs.Fields("SalesRepID").Value
    End If

    rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing

    cn.Close
    Set cn = Nothing

    End Sub


    I am receiving this error message

    Run-Time Error '3265'

    Item Cannot Be Found In The Collection Corresponding To The Requested Name Or Ordinal

    How do I fix it?

  2. #2
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    123
    Usually this indicates that you a4re trying to reference a field in the recordset that does not exist. Check your field names in the code versus the field names in the source sql.

    For example, I see that your source sql has a field named "RepresentativeID" whereas your code refers to a field named "REPRESENTATIVE.ID" Try making all such references match and see if that fixes your problem.

  3. #3
    duckie10 is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    16
    I changed the names that they matched and it still didn't work

  4. #4
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    123
    Ok then.

    What does [MANAGER B], [SALESREP B] etc refer to? Are these global variables? If not, then this could be your issue.

    for example, when you use
    [MANAGER B].Value = rs.Fields("MANAGER.ID").Value
    you are telling the db to set the value of something called [Manager B] to whatever the value is in the field Manager.ID in the current record in the recordset.

    Now, if Manager B is a global variable then access should know all about it already because you already declared it elsewhere. If, however, this is instead a control in a form or a value in a table, then Access needs to be told specificly where to place this value.
    Eg. Forms!fmNameHere!ControlNameHere = ....

    If you want to store this in a temporary variable, then you need to declare that in the sub. Something like:
    Dim [Manager B] as String

    Also, to make your code easier to follow, you may wish to investigate the use of the select case statement rather than using multiple if statements.

    Somehting like
    Code:
    Select Case rs.Fields("Dept").Value
       Case "Bio"
        [MANAGER B].Value = rs.Fields("MANAGER.ID").Value
        [SALESREP B].Value = rs.Fields("REPRESENTATIVE.ID").Value
     
       Case "FER"
        [MANAGER F].Value = rs.Fields("MANAGER.ID").Value
        [REFERRED REP F].Value = rs.Fields("REPRESENTATIVE.ID").Value
     
       Case "SER"
        [REFERRED MANAGER S].Value = rs.Fields("ManagerID").Value
        [REFERRED REP S].Value = rs.Fields("SalesRepID").Value
     
    End Select

  5. #5
    duckie10 is offline Novice
    Windows Vista Access 2007
    Join Date
    May 2009
    Posts
    16
    I tried your suggestions. It did not work

    [Manager B] is an unbound control. The name in the property sheet is Combo88

  6. #6
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    123
    Then, as I explained in my previous post, you need to tell the code that information because it does not know what you mean by '[Manager B]'.

    So, instead of
    [MANAGER B].Value = rs.Fields("MANAGER.ID").Value
    use
    Me.Combo88 = rs.Fields("MANAGER.ID").Value

    And make sure the field name matches your sql.

    the Me. is a shortcut version of Forms!FormNameHere. and assumes you are running this code in a form's code module, not a general code module.

    After you have fixed those errors, if you are still getting the problem, then post your code in its entirety again so I can see if you have fully implemented what I have previously suggested. Even better would be to post a zipped example db so I can get hands-on with it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Run-time error 2001
    By RONY in forum Programming
    Replies: 0
    Last Post: 12-23-2008, 04:09 AM
  2. Run time error '6': Overflow
    By wasim_sono in forum Access
    Replies: 0
    Last Post: 06-22-2007, 05:44 AM
  3. Run Time Error 424
    By ddog171 in forum Programming
    Replies: 3
    Last Post: 02-04-2006, 05:13 AM
  4. Replies: 0
    Last Post: 01-18-2006, 06:02 AM
  5. Replies: 1
    Last Post: 12-09-2005, 07:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums