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

[dwinn]

Hi Everyone,

I am writing a program in VBA (within Access 2010) that grabs a specific .txt file, split up the fields as per given specification and imports the values in to an Access table.

So far, I can open the file and see what the program is seeing and I can open the table, but when it comes to writing the values in to the table, I get the following error message:

Run-time error '3265':
Item cannot be found in the collection corresponding to the requested name or ordinal.

I have compared my code with the table, and all fields in the program correctly match up with my table fields.

My code is as folows:

Sub ImportTextFile()
 Dim LineData As String
 
 Dim F1 As String
 Dim F2 As String
 Dim F3 As String
 Dim F4 As String
 Dim PC As String
 Dim S As String
 Dim PN As String
 Dim F8 As String
 Dim ED As String
 Dim F10 As String
 Dim F11 As String
 Dim CN As String
 Dim F13 As String
 Dim CNAME As String
 Dim F15 As String
 Dim REQ As String
 Dim QTY5 As String
 Dim QTY10 As String
 Set cncurrent = CurrentProject.Connection
 Set rsDiag = New ADODB.Recordset
 ' Open the text file
 Open "T:\FileDirectory\File.txt" For Input As #1
 
 ' Open the table to insert the text file into
 strsql = "Select * from tblTable;"
 
 rsDiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic

 Do While Not EOF(1)
 ' Read a line of data.
 Line Input #1, LineData
 F1 = Left(LineData, 4)
 F2 = Mid(LineData, 5, 11)
 F3 = Mid(LineData, 16, 8)
 F4 = Mid(LineData, 24, 7)
 PC = Mid(LineData, 31, 4)
 S = Mid(LineData, 35, 20)
 PN = Mid(LineData, 55, 2)
 F8 = Mid(LineData, 57, 7)
 ED = Mid(LineData, 64, 11)
 F10 = Mid(LineData, 75, 2)
 F11 = Mid(LineData, 77, 12)
 CN = Mid(LineData, 89, 4)
 F13 = Mid(LineData, 93, 4)
 CNAME = Mid(LineData, 97, 30)
 F15 = Mid(LineData, 127, 2)
 REQ = Mid(LineData, 129, 3)
 QTY5 = Mid(LineData, 132, 2)
 QTY10 = Mid(LineData, 134, 2)

 rsDiag.AddNew
 rsDiag!ICD9raw = ICDraw
 rsDiag!Description = ICDDesc
 rsDiag.Update

 Loop
 ' Close the data file.
 Close #1
 rsDiag.Close
 End Sub

Could anyone please advise on what I could be doing wrong?

Many Thanks,

Dan

[BitBlt]

The only thing I see that's a little odd is, down just below the "rsDiag.AddNew" statement, you have "rsDiag!ICD9raw = ICDraw". Did you mean to have that "9" in there? Also, I don't see any declaration for either of the "ICDraw" or "ICDDesc" variables.

Finally, a bit of advice. You probably want to avoid using the "select *" syntax, and explicitly state the columns you need from your table. It takes a little extra typing, but it can really help debugging or maintaining.

[dwinn]

Hi BitBlt,

Thank you for your response.

I took them two lines out, and it imports the data.

When I go to the table, however, no records are there, but theres empty cells as if data is there, but invisible.

Do you have any idea why this could be?

My code is now:

Sub ImportTextFile()
 Dim LineData As String
 
 Dim Field1 As String
 Dim Field2 As String
 Dim Field3 As String
 Dim Field4 As String
 Dim Field5 As String
 Dim Field6 As String
 Dim Field7 As String
 Dim Field8 As String
 Dim Field9 As String
 Dim Field10 As String
 Dim Field11 As String
 Dim Field12 As String
 Dim Field13 As String
 Dim Field14 As String
 Dim Field15 As String
 Dim Field16 As String
 Dim Field17 As String
 Dim Field18 As String
 Set cncurrent = CurrentProject.Connection
 Set rsDiag = New ADODB.Recordset
 ' Open the text file
 Open "T:\Directory\File.txt" For Input As #1

 ' Open the table to insert the text file into
 strsql = "Select * from tblIndentTest"
 
 rsDiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic
 Do While Not EOF(1)
 ' Read a line of data.
 Line Input #1, LineData
 Field1 = Left(LineData, 4)
 Field2 = Mid(LineData, 5, 12)
 Field3 = Mid(LineData, 17, 9)
 Field4 = Mid(LineData, 26, 7)
 Field5 = Mid(LineData, 33, 4)
 Field6 = Mid(LineData, 37, 20)
 Field7 = Mid(LineData, 57, 2)
 Field8 = Mid(LineData, 59, 7)
 Field9 = Mid(LineData, 66, 11)
 Field10 = Mid(LineData, 77, 2)
 Field11 = Mid(LineData, 79, 12)
 Field12 = Mid(LineData, 91, 4)
 Field13 = Mid(LineData, 95, 4)
 Field14 = Mid(LineData, 99, 30)
 Field15 = Mid(LineData, 129, 2)
 Field16 = Mid(LineData, 131, 3)
 Field17 = Mid(LineData, 134, 2)
 Field18 = Mid(LineData, 136, 2)

 rsDiag.AddNew
 'rsDiag!ICDraw = ICDraw
 'rsDiag!Description = ICDDesc
 rsDiag.Update

 Loop
 ' Close the data file.
 Close #1
 rsDiag.Close
 End Sub

Could you advise at all?

Many Thanks for your help.

Dan

[BitBlt]

I would guess that it's because you never set the new row values to your variable values. Do the addnew, then set each column in your recordset to the appropriate value, then do the update. After the addnew, you might have to do a movenext to set the current row pointer, but I'm not sure...you'll have to do a little experimenting.

Good luck!

[dwinn]

I am still having problems...would you mind explaining it in a bit more details please?

Thanks,

Dan

[BitBlt]

When you are using a Recordset object, you have to explicitly set the values. So you should have code that looks something like this (and I'm making up field names, because I can't see what columns your table actually has):

<snip>
rsDiag.AddNew
rsDiag.Fields("FieldName1") = ICDraw
rsDiag.Fields("FieldName2") = ICDDesc
' etc. until all the fields are filled
rsDiag.Update
<snip>

You could also make it a little more generic by using an index number instead of actual field names, like so:

<snip>
rsDiag.AddNew
rsDiag.Fields(0) = ICDraw
rsDiag.Fields(1) = ICDDesc
' etc. until all the fields are filled
rsDiag.Update
<snip>

Hope this is enough for you to go on. Again, good luck!

[dwinn]

Hi,

Thanks for your post.

I have got it working fine...I used the rsDiag.AddNew and then for each field:

rsDiag.Fields("FieldName") = Mid(LineData, 20, 19)

Thank you very much for your help.

Dan