473,325 Members | 2,442 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,325 software developers and data experts.

Item cant be found in the collection corresponding to the requested name or ordinal

I keep getting this error message - Item cant be found in the collection corresponding to the requested name or ordinal

I dont understand what it means to be honest.

I am trying to get the code I have written to fill the textboxes on the form, I have been using Me.Controls on another form and this works fine, but with me doing slightly different in the way the form opens it doesnt seem to like it.

Can anyone point out where I have gone a miss.

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATQuestions.QuestionID, tblCSATQuestions.QuestionNo, " & vbCrLf & _
"tblCSATQuestions.QuestionText, tblCSATQuestions.ResponseID, " & vbCrLf & _
"tblCSATResponse.Response, tblCSATResponse.CSATNumber, " & vbCrLf & _
"tblCSATResponse.JobNumber, tblCSATResponse.ResponseValue " & vbCrLf & _
"FROM tblCSATQuestions " & vbCrLf & _
"INNER JOIN tblCSATResponse ON tblCSATQuestions.QuestionID = tblCSATResponse.QuestionID "
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenForwardOnly, adLockOptimistic
rs.MoveFirst
QuestionsRSControls
Me.Controls("txtQuestionNo") = rs.Fields("Question") = Nz(Question.Value, NullValue)
Me.Controls("txtQuestionNoText") = rs.Fields("QuestionText") = Nz(QuestionText.Value, NullValue)
Me.Controls("cboResponse") = rs.Fields("Response") = Nz(Response.Value, NullValue)
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub
Jul 17 '07 #1
2 4227
MikeTheBike
639 Expert 512MB
Hi
I keep getting this error message - Item cant be found in the collection corresponding to the requested name or ordinal

I dont understand what it means to be honest.

I am trying to get the code I have written to fill the textboxes on the form, I have been using Me.Controls on another form and this works fine, but with me doing slightly different in the way the form opens it doesnt seem to like it.

Can anyone point out where I have gone a miss.

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
"Data Source=" & cTables
sQRY = _
"SELECT " & vbCrLf & _
"tblCSATQuestions.QuestionID, tblCSATQuestions.QuestionNo, " & vbCrLf & _
"tblCSATQuestions.QuestionText, tblCSATQuestions.ResponseID, " & vbCrLf & _
"tblCSATResponse.Response, tblCSATResponse.CSATNumber, " &
vbCrLf & _
"tblCSATResponse.JobNumber, tblCSATResponse.ResponseValue " & vbCrLf & _
"FROM tblCSATQuestions " & vbCrLf & _
"INNER JOIN tblCSATResponse ON tblCSATQuestions.QuestionID = tblCSATResponse.QuestionID "
rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenForwardOnly, adLockOptimistic
rs.MoveFirst
QuestionsRSControls
Me.Controls("txtQuestionNo") = rs.Fields("Question") = Nz(Question.Value, NullValue)
Me.Controls("txtQuestionNoText") = rs.Fields("QuestionText") = Nz(QuestionText.Value, NullValue)
Me.Controls("cboResponse") = rs.Fields("Response") = Nz(Response.Value, NullValue)
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Exit Sub
End Sub
From the error message I assume the recordset has opened (you do not say what line throws the error), in which case I think this

rs.Fields("Question")

should be this

rs.Fields("QuestionNo")

Appart from that I don't think this will work

Me.Controls("txtQuestionNo") = rs.Fields("QuestionNo") = Nz(Question.Value, NullValue)

ie what does this

= Nz(Question.Value, NullValue)

do on the end of the statement??


BTW you cand shorted
Me.Controls("txtQuestionNo") = rs.Fields("QuestionNo")
to
txtQuestionNo = rs("QuestionNo")

You can also replace rs("QuestionNo") with rs(1)
The 1 being the ordinal position of the field (in the error message), which is zero based, so 1 is the second field listed in the query.

There are other thing, but one step at a time!

Does that help?

MTB
Jul 17 '07 #2
MTB,

Thanks, yes it helps, I thought that Nz what to tell it to do something if there was a null value in the table. but I tried what you have said and works perfectly.

Thanks
Jul 18 '07 #3

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

Similar topics

5
by: J. Muenchbourg | last post by:
The field name 'articleid', which is an identity/primary key , is not being recognized in my recordset as I get an " Item cannot be found in the collection corresponding to the requested name or...
2
by: CJM | last post by:
I'm running a stored procedure that inserts a record into a table then returns the Identity field value. I've done this kind of thing plenty of times in the past, but I'm obviously doing something...
4
by: Dthmtl | last post by:
I know what this error is, all fieldnames have been verified and reverified. Not sure why it is happening. Here is the SQL statement strSQL = "SELECT Problems.TicketNumber, Activity, ActDate...
9
by: Gerald Lightsey | last post by:
I am doing some work that involves automating MS MapPoint 2002. The object model is documented in the context of Visual Basic 6.0. A typical example follows. 'Output first result of find...
2
by: Ash Jones | last post by:
I trying to do nested items in a collection. I've got an item which one of the properties is subitems, which is a collection of item. can do the root level but if i do a subitem at design time i...
18
by: JohnR | last post by:
From reading the documentation, this should be a relatively easy thing. I have an arraylist of custom class instances which I want to search with an"indexof" where I'm passing an instance if the...
2
by: indhu | last post by:
Hi its not working, i want only distinct record. here its repeated records coming. accdb = "SELECT DISTINCT sequence FROM scene WHERE sceneid = '" & myquery & "' " and when i select the...
7
by: colin | last post by:
Hi, I have my property editor wich uses the pop up collection editor for arrays etc, but i have had to use a generic wrapper for the elements in some types of collections. although the editing...
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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.