473,322 Members | 1,401 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

VBA Code: Run Time Error 3265

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
6 29807
FishVal
2,653 Expert 2GB
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
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
2,653 Expert 2GB
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
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
2,653 Expert 2GB
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
Thank you for taking the time to explain this to me.

Darkhat01
Aug 20 '07 #7

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

Similar topics

4
by: Christaaay | last post by:
I have been using the code below successfully for almost a year. yesterday, I began getting a run time error 6 (overflow). I am using the code in an Access 2000 database. Can anyone help me...
0
by: rajkalacbe | last post by:
I am running a visual basic executable program through Forms4.5 of D2K using Forms menu options.. When I Invoke this form in the server where I have the database installed, I do not have any...
2
by: twink | last post by:
I am trying to access certain rows in a table in a combo box. I get this error (Run-time error '3265' Item not found in this collection) Here is the code. I am new to VBA so please excuse me. Here is...
1
by: twink | last post by:
am trying to access certain rows in a table through a combo box.Basically what I am trying to do is when I select "Delete Data" from the combo box and click the go button it will run a macro. I get...
1
by: wassimdaccache | last post by:
Hello Please help me I working on database using access 2003 I'm writing into a save bottom on a form this code to insert some of my value on another table ...no relationship between them ...
3
by: DGNinja12 | last post by:
I get this error when i run, i cant figure out what is wrong Can some one please help me? Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or...
0
nurikoAnna
by: nurikoAnna | last post by:
Dim rsFaculty As New ADODB.Recordset Dim rsDepartment As New ADODB.Recordset Dim oCm As New ADODB.Command Private Sub Disconnect() connect.Close End Sub
8
by: charli | last post by:
Error 3265, "Item cannot be found in the collection corresponding to the requested name or ordinal" code programatically opens a query using ADOX and changed the sql Dim cat As New...
2
by: steve c | last post by:
Dim rs As New ADODB.Recordset Dim oCnn As ADODB.Connection Set oCnn = New ADODB.Connection 'Windows Integrated Security Login 'oCnn.ConnectionString = "provider=sqloledb;data source=intel;initial...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.