Code to get data in columns into rows of another table

[Greg04]

Hi , I am using below code to fetch numbers in columns of a table ....into rows of another table....
It works fine for first row of the source table and fetches first entry but doesn't loop through next row...

Function getDC()
   Dim db As DAO.Database, _
      recIn As DAO.Recordset, _
      recOut As DAO.Recordset
'
    'Dim f As DAO.Field
'
    Set db = CurrentDb()
'
    Set recIn = db.OpenRecordset("daycount", dbOpenDynaset, dbReadOnly)
'
    Set recOut = db.OpenRecordset("DC", dbOpenDynaset, dbEditAdd)
'
   With recIn
      .MoveFirst
      Do
         'for f in .Fields
         For i = 5 To .Fields.Count
            'If Left(.Fields(i).Name, 1) = "D" Then
                recOut.AddNew
                recOut.Fields("F No") = _
                   .Fields("Flt Nb")
                recOut.Fields("begin") = _
                    .Fields("Start Date")
                recOut.Fields("ops") = .Fields(i)
                recOut.Update
'
         Next i
         .MoveNext
      Loop Until .EOF
    End With
'
    recIn.Close
'
    recOut.Close
'
    db.Close
'
End Function

I get a runtime error items not found in this collection...Someone pls help !! It's urgent

[zmbd]

1) Please read the following document's trouble shooting steps these will help you solve most of the common errors http://bytes.com/forums/feedback/913...g-vba-sql-code

>> You will also find that placing STOP command in your code (say at line 13) and using the [F8] key to step thru your code will also help find the errors in code that does compile per the above link's T.S. steps.

>> You must perform these basic T.S. steps yourself on your computer. We cannot duplicate your setup.

2) Please use the [CODE/] tool to format your posted script.

I've done this for this time AND I have removed the extra spacing. I don't know if this was due to how you cut-n-pasted; however, it does make the code hard to follow.

Amongst other things, the code formatting provides us line numbers... leading to...

3) Line 18: consider using a For...Each construct against the fields in current record.
+ You can then test directly against the field name propery.
+ You can then use the current field's value directly to set the value in the other table if the field is the one you desire.

Line 25: recOut.Fields("ops") = .Fields(i)
Are you sure you have a field that will match the index value of "I"? Once again the STOP command I suggested before will help you to determine if that is the issue.

Line 37 , You did not open it, you do not close it

4) "items not found in this collection" most likely a typo in the field name.
Also I would refer to the field as:
recOut![F No]
Just a little easier to type


5) If you can explain what/why you are doing this, there may be an easier method such as an update/append action query.

6) Using the term urgent: Please reserve that word for situations where you may lose your life or your job.

Keep in mind, this is a volunteer site and there is no guarantee (especially during the holidays) that your post will be replied to within any given timeframe.

[hvsummer]

@Greg04: Welcome to Bytes.com

first thing i saw in your code could be wrong

  recOut.Fields("F No") = .Fields("Flt Nb")
  recOut.Fields("begin") = .Fields("Start Date")

if your field's name has space, please put it in [] to be sure no weird error appear.
or rename your field's name into F_No.

runtime error items not found in this collection

there are some situation that recordset give back this error
first, your query did not give any result, mean .EOF or .BOF = true
second, you call your recordset object with wrong name/index (could be var i larger than field index)

--> first step to resolve that you have to alway trap "If not (.EOF or .BOF) then " in your code when you work with recordset.
--> the second check your field name, field index with stop command to debug liek zmbd suggestion.
--> third step to open your query mannually to see if 2 query return any recordset.
--> check your code again in this part below

   With recIn
      .MoveFirst
      Do
         For i = 5 To .Fields.Count
                recOut.AddNew
                recOut.Fields("F No") = .Fields("Flt Nb")
                recOut.Fields("begin") = .Fields("Start Date")
                recOut.Fields("ops") = .Fields(i)
                recOut.Update
         Next i
         .MoveNext
      Loop Until .EOF
    End With
 

recout.fields("somename") could give error on collection if your query return no match string name.
you can use

RecIn!FieldName 'with [] if name has space
.Fields(i)
.Fields.Item(i)

with index will return recordset faster.

[zmbd]

HVSummerif your field's name has space, please put it in [] to be sure no weird error appear.
or rename your field's name into F_No.

I certainly agree that spaces should be avoided in field names along with anything other than alphanumeric and the underscore (personal preferences); however, using the dot-field construct, the open/close quote marks duplicate the open and close brackets - it shouldn't hurt to include them and would be a good practice to get in to doing if Greg04 is going to continue to use spaces in the field names :-)

Greg04 It works fine for first row of the source table

HVSummeryour query did not give any result, mean .EOF or .BOF = true

--> third step to open your query mannually to see if 2 query return any recordset.

Here we do agree that Greg04 needs to verify that the query is actually returning the number of records intended; however, "recIn" certainly returned at least one record; thus, the dot-EOF/BOF did not equal true at the start and in either case, a "recordset.movenext" command at the eof would generate a "DAO.Recordset, 3021, No current record" error not a "not in this collection" error.

ZMBD4) "items not found in this collection" most likely a typo in the field name.

HVSummer--> the second check your field name, field index with stop command to debug liek zmbd suggestion.

agreed

HVSummer--> check your code again

This we both agree on, Greg04 has a glitch in the code that the steps I've provided in Post#2 will help pinpoint.

Once Greg04 has performed the basic trouble shooting steps as outlined in the link and reports back the findings thereof, we'll be in a much better position to suggest the next steps to take to accomplish the goal and I suspect there's either a design flaw in the database or there is an easier method to accomplish the task(s) at hand.