By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,948 Members | 1,559 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,948 IT Pros & Developers. It's quick & easy.

Need VBA Help - item not found in this collection error

P: 3
I need help with the following code. The code is pulling records from table "Questions" and looping through the columns labeled "Questions #1" - #10. It works fine but only for the first ID number, I get an item not found error once it reaches the last column ("Question #10") for the first ID number, so basically my loop is not moving to the next ID number.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub SomeProcedure()
  5.     Dim db As DAO.Database, recIn As DAO.Recordset, recOut As DAO.Recordset, i As Integer
  6.  
  7.  
  8.     Set db = CurrentDb()
  9.     Set recIn = db.OpenRecordset("Questions", dbOpenDynaset, dbReadOnly)
  10.     Set recOut = db.OpenRecordset("Questions2", dbOpenDynaset, dbEditAdd)
  11.  
  12.     With recIn
  13.         .MoveFirst
  14.         Do
  15.             For i = 0 To .Fields.Count
  16.             If Left(.Fields(i).Name, 8) = "Question" Then
  17.                     recOut.AddNew
  18.                         recOut.Fields("Loan Number") = recIn.Fields("Loan Number")
  19.                         recOut.Fields("Total Questions") = recIn.Fields(i)
  20.                     recOut.Update
  21.                     End If
  22.             Next i
  23.          .MoveNext
  24.         Loop Until .EOF
  25.     End With
  26.     recIn.Close
  27.     recOut.Close
  28.     db.Close
  29.  
  30.  
  31. End Sub
Jun 24 '14 #1

✓ answered by twinnyfo

Try changing line 15 in your code to:

Expand|Select|Wrap|Line Numbers
  1. For i = 0 To .Fields.Count - 1
This is the problem. If you have 12 fields, they are numbered from 0 to 11, not 0 to 12.

Hope this helps.

Share this Question
Share on Google+
5 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,487
nbwest76,

First, please use the Code tags when posting your VBA.

Second, your variable i will cycle from 1 to the number of fields. however, fields are numbered from 0 to (n-1). So if you have 12 fields, they are numbered from 0-11. It will not be able to find field #12.

Hope this helps.
Jun 24 '14 #2

P: 3
twinnyfo, it finds all of the fields correctly. My problem is after it finds all of the fields for the first ID number it fails to move to the next ID number.
Jun 24 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,487
Try changing line 15 in your code to:

Expand|Select|Wrap|Line Numbers
  1. For i = 0 To .Fields.Count - 1
This is the problem. If you have 12 fields, they are numbered from 0 to 11, not 0 to 12.

Hope this helps.
Jun 24 '14 #4

P: 3
I was going crazy trying to figure this out! Thank you! It worked perfectly.
Jun 24 '14 #5

twinnyfo
Expert Mod 2.5K+
P: 3,487
Glad I could help. I'm sure I'll lean on you someday, too!
Jun 24 '14 #6

Post your reply

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