collection corresponding to the requested name or ordinal

G

Guest

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
 
G

Guest

Hi Jez,

Me![txtQuestionNo] = iif(isnull(rs![Question]), NullValue,rs![Question])
etc.
HTH Paolo
 
G

Guest

Excellent, Thanks Paolo


Paolo said:
Hi Jez,

Me![txtQuestionNo] = iif(isnull(rs![Question]), NullValue,rs![Question])
etc.
HTH Paolo

Jez said:
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
Click to expand...
Click to expand...
 
Top