Run Time Error 3265

[duckie10]

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?

[CraigDolphin]

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.

[duckie10]

I changed the names that they matched and it still didn't work

[CraigDolphin]

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

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

[duckie10]

I tried your suggestions. It did not work

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

[CraigDolphin]

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.