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
2 4227
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
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |