title: Access VBA - Problems looping through fields in Access last record and exporting to Word bookmarks?
body: <p>Thanks for looking at this.</p>
  
  <p>I have a recordset with 60 fields and I want to export the <strong>last</strong> record for each field into a Microsoft Word file with 60 bookmarks called <code>g1</code> to <code>g60</code>.</p>
  
  <p>Of course I would like to loop through these but can't make it work.</p>
  
  <p>Essentially what I want is this:</p>
  
  <pre><code>Dim wApp As Word.Application
  Dim wDoc As Word.Document
  Dim wDoct As Word.Document
  Dim rs As DAO.Recordset
  Dim rng As Word.Range
  Dim intI As Integer
  Dim fld As DAO.field
  
  Set wApp = New Word.Application
  Set wDoc = wApp.Documents.Open("C:\Users\Peter\Documents\testdoc.docm", ReadOnly:=False)
  Set wDoct = wApp.Documents.Open("C:\Users\Peter\Documents\Trends.docx")
  Set rs = CurrentDb.OpenRecordset("Overall")
  
  Set rs = CurrentDb.OpenRecordset("Grades")
  
  If Not rs.EOF Then rs.MoveLast
  
  wDoc.Bookmarks("g1").Range.Text = Nz(rs!PlanQ, "")
  wDoc.Bookmarks("g2").Range.Text = Nz(rs!PlanQMin, "")
  wDoc.Bookmarks("g3").Range.Text = Nz(rs!PlanUnsat, "")
  wDoc.Bookmarks("g4").Range.Text = Nz(rs!BriefQ, "")
  wDoc.Bookmarks("g5").Range.Text = Nz(rs!BriefQmin, "")
  wDoc.Bookmarks("g6").Range.Text = Nz(rs!BriefUnsat, "")
  ' and so on up to 60
  wDoct.Save
  wApp.Quit
  </code></pre>
  
  <p>This works but needs to be looped of course - I tried this, but it errors out:</p>
  
  <pre><code>Dim wApp As Word.Application
  Dim wDoc As Word.Document
  Dim wDoct As Word.Document
  Dim rs As DAO.Recordset
  Dim rng As Word.Range
  Dim intI As Integer
  Dim fld As DAO.field
  
  Set wApp = New Word.Application
  Set wDoc = wApp.Documents.Open("C:\Users\Peter\Documents\testdoc.docm", ReadOnly:=False)
  Set wDoct = wApp.Documents.Open("C:\Users\Peter\Documents\Trends.docx")
  Set rs = CurrentDb.OpenRecordset("Overall")
  
  Set rs = CurrentDb.OpenRecordset("Grades")
  
  If Not rs.EOF Then rs.MoveLast
  
  intI = 1
  
  With rs
  Do Until .EOF
  For Each fld in rs.Fields
  wDoc.Bookmarks("g" &amp; "intI").Range.Text = Nz(rs!fld.Name, "")
  
  Next fld
  intI = intI + 1
  loop
  End With
  
  wDoct.save
  Wapp.quit
  </code></pre>
  
  <p>Any ideas will be very much welcome, otherwise I have a lot of typing ahead of me. :-) </p>
  
  <p>Thank you for your time!</p>
  
  <p>Peter</p>
  
  <p>This is the current code that fail like mentioned below:
  <strong>UPDATE</strong> This is the exact copy paste. </p>
  
  <pre><code>Public Sub Looptest()
  
  Dim wApp As Word.Application
  Dim wDoc As Word.Document
  Dim rs As DAO.Recordset
  Dim index As Integer
  Dim item As Variant
  
  Set wApp = New Word.Application
  Set wDoc = wApp.Documents.Open("C:\Users\Peter\Documents\testdoc.docm", ReadOnly:=False)
  
  Set rs = CurrentDb.OpenRecordset("Grades")
  
  If Not rs.EOF Then rs.MoveLast
  
  For Each item In rs.Fields
      index = index + 1
  
      Dim bookmarkName As String
      bookmarkName = "g" &amp; index
  
      Dim bookmarkValue As Variant
      bookmarkValue = Nz(rs(item.Name).Value, "")
  
      Debug.Print "Try set bookmark '" &amp; bookmarkName &amp; "' to '" &amp; bookmarkValue &amp; "' now."
  
      Dim bookmarkRange As Word.Range
      Set bookmarkRange = wDoc.Bookmarks(bookmarkName).Range
  
      bookmarkRange.Text = bookmarkValue
  
      Set bookmarkRange = Nothing
      Next item
  
  wApp.DisplayAlerts = False
  wDoc.SaveAs2 "C:\Users\Peter\Documents\" &amp; rs!ID &amp; "_gradesheet.docm"
  wDoc.Close
  wApp.Quit
  </code></pre>