By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,161 Members | 1,015 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,161 IT Pros & Developers. It's quick & easy.

VBA Code: Run Time Error 3265

P: 16
I am getting a Run-Time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

The Function call I am using is:

Expand|Select|Wrap|Line Numbers
  1. =getNextLastActivityDataPlanReviewScore([ID],"Review","TARGET_DATE","InitialScore")
I think that is has something to do with sReturn = rst("sScore") in my function. But I am not 100% sure. Any Ideas would help.

Here is the Function Code:

Expand|Select|Wrap|Line Numbers
  1. Public Function getNextLastActivityDataPlanReviewScore(nID, sType, sNextLast, sScore)
  2.     Dim cnThisConnect As ADODB.Connection
  3.     Set cnThisConnect = CurrentProject.Connection
  4.     Dim rst As New ADODB.Recordset
  5.     Dim sSQL As String
  6.     Dim sReturn As String
  7.  
  8.  
  9. sSQL = "SELECT " & sNextLast & " AS fDate FROM DATA_PLAN_ACTIVITIES "
  10. sSQL = sSQL & " WHERE DATA_PLAN_ACTIVITIES.ACTIVITY_TYPE = '" & sType & "' "
  11. sSQL = sSQL & " AND DATA_PLAN_ACTIVITIES.APP_ID = " & nID
  12. sSQL = sSQL & " AND DATA_PLAN_ACTIVITIES." & sScore
  13. sSQL = sSQL & " AND " & sNextLast & " IS NOT NULL "
  14. sSQL = sSQL & " AND " & sScore & " IS NOT NULL "
  15. sSQL = sSQL & " ORDER BY " & sNextLast & " DESC"
  16.  
  17.  
  18.  
  19.     rst.Open sSQL, cnThisConnect, adOpenKeyset, adLockOptimistic
  20.  
  21.     If Not rst.BOF Then
  22.     rst.MoveFirst
  23.             sReturn = rst("sScore")
  24.     Else
  25.         sReturn = "None"
  26.     End If
  27.     rst.Close
  28.     cnThisConnect.Close
  29.  
  30.     Set rst = Nothing
  31.     Set cnThisConnect = Nothing
  32.    MsgBox (sReturn)
  33.    getNextLastActivityDataPlanReviewScore = sReturn
  34. End Function
Thanks for any help,

Darkhat01
Aug 16 '07 #1
Share this Question
Share on Google+
6 Replies


FishVal
Expert 2.5K+
P: 2,653
I am getting a Run-Time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

The Function call I am using is:

Expand|Select|Wrap|Line Numbers
  1. =getNextLastActivityDataPlanReviewScore([ID],"Review","TARGET_DATE","InitialScore")
I think that is has something to do with sReturn = rst("sScore") in my function. But I am not 100% sure. Any Ideas would help.

Here is the Function Code:

Expand|Select|Wrap|Line Numbers
  1. Public Function getNextLastActivityDataPlanReviewScore(nID, sType, sNextLast, sScore)
  2.     Dim cnThisConnect As ADODB.Connection
  3.     Set cnThisConnect = CurrentProject.Connection
  4.     Dim rst As New ADODB.Recordset
  5.     Dim sSQL As String
  6.     Dim sReturn As String
  7.  
  8.  
  9. sSQL = "SELECT " & sNextLast & " AS fDate FROM DATA_PLAN_ACTIVITIES "
  10. sSQL = sSQL & " WHERE DATA_PLAN_ACTIVITIES.ACTIVITY_TYPE = '" & sType & "' "
  11. sSQL = sSQL & " AND DATA_PLAN_ACTIVITIES.APP_ID = " & nID
  12. sSQL = sSQL & " AND DATA_PLAN_ACTIVITIES." & sScore
  13. sSQL = sSQL & " AND " & sNextLast & " IS NOT NULL "
  14. sSQL = sSQL & " AND " & sScore & " IS NOT NULL "
  15. sSQL = sSQL & " ORDER BY " & sNextLast & " DESC"
  16.  
  17.  
  18.  
  19.     rst.Open sSQL, cnThisConnect, adOpenKeyset, adLockOptimistic
  20.  
  21.     If Not rst.BOF Then
  22.     rst.MoveFirst
  23.             sReturn = rst("sScore")
  24.     Else
  25.         sReturn = "None"
  26.     End If
  27.     rst.Close
  28.     cnThisConnect.Close
  29.  
  30.     Set rst = Nothing
  31.     Set cnThisConnect = Nothing
  32.    MsgBox (sReturn)
  33.    getNextLastActivityDataPlanReviewScore = sReturn
  34. End Function
Thanks for any help,

Darkhat01
You are right, this is exactly where the error occurs.
You don't retrieve field [sScore] into your recordset. Surely "Item cannot be found in the collection".

And, G..d save us, what this line supposed to do
Expand|Select|Wrap|Line Numbers
  1. cnThisConnect.Close
close current project connection ???
fortunately Access will not allow this operation as well as will not raise an error - simply nothing will happen
Aug 16 '07 #2

P: 16
What would be the best way to retrieve sScore into the record set?

Something like:

Expand|Select|Wrap|Line Numbers
  1.  sSQL = "SELECT " & sScore & " AS fDate FROM DATA_PLAN_ACTIVITIES "
Then have this?

Expand|Select|Wrap|Line Numbers
  1. sReturn = rst(fScore)
I tried this but I get the same error…..
Aug 16 '07 #3

FishVal
Expert 2.5K+
P: 2,653
What would be the best way to retrieve sScore into the record set?

Something like:

Expand|Select|Wrap|Line Numbers
  1.  sSQL = "SELECT " & sScore & " AS fDate FROM DATA_PLAN_ACTIVITIES "
Then have this?

Expand|Select|Wrap|Line Numbers
  1. sReturn = rst(fScore)
I tried this but I get the same error…..
The best way would be
Something like:

Expand|Select|Wrap|Line Numbers
  1.  sSQL = "SELECT " & sScore & " AS fDate FROM DATA_PLAN_ACTIVITIES 
  2. .......
  3. sReturn = rst("fDate")
Only be sure sScore string contains a valid field name compliant with field naming convention (without forbidden symbols, not a reserved word) otherwise enclose the name into square brackets.
Aug 16 '07 #4

P: 16
FishVal,

Thank you so much, this worked.

Maybe you can explain to me why it works now. My understanding is that fDate is a value, why would I need to but quotes around it. This make it a string correct?

Thank you again,

darkhat01
Aug 16 '07 #5

FishVal
Expert 2.5K+
P: 2,653
FishVal,

Thank you so much, this worked.

Maybe you can explain to me why it works now. My understanding is that fDate is a value, why would I need to but quotes around it. This make it a string correct?

Thank you again,

darkhat01
Well. Actually it isn't a value. Its a field name.
When you open recordset on SQL expression like
Expand|Select|Wrap|Line Numbers
  1. SELECT Field1 AS Field1_Alias FROM Table 
field having name "Field1" is being retrieved with the name "Field1_Alias".

What concerning RecordSet object.
RecordSet object has Fields property returning Fields collection.
Fields collection has Item property which receives member name or ordinal and returns member which is object of class Field.
Field object has property Value which returns the value of the field - actually what do you need.

So we have the following
Expand|Select|Wrap|Line Numbers
  1. Dim varVariable as Variant
  2. Dim RS as Recordset
  3. ........
  4. varVariable = RS.Fields.Item("FieldName").Value
  5.  
properties Fields, Item and Value are default for the correspondent parent class, so the last line in a pretty way turns to
Expand|Select|Wrap|Line Numbers
  1. varVariable = RS("FieldName")
  2.  
Moreover vb provides two object operators "." (dot) to refer to object property/method, "!" (bang) to refer to collection member (w/o explicitely calling Collection.Item property).

we have collection Fields, using bang operator we can refer to collection member this way
Expand|Select|Wrap|Line Numbers
  1. varVariable = RS.Fields!FieldName.Value
  2. or simply
  3. varVariable = RS!FieldName
  4.  
VBA (like other IDE's) has ObjectBrowser. I reccomend you to use it whenever you have questions concerning object model.

BTW you obviously have used expression like
Expand|Select|Wrap|Line Numbers
  1. var = Forms!FormName!ControlName
  2.  
if we rewrite this with all default properties called behind the scenes, then we will see expression like this
Expand|Select|Wrap|Line Numbers
  1. var = Forms.Item("FormName").Controls.Item("ControlName").Value
  2.  
Aug 16 '07 #6

P: 16
Thank you for taking the time to explain this to me.

Darkhat01
Aug 20 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.