Using an array instead of a table in an Access Report

[lburgher]

I noticed a thread (now closed) asking how to use an array as a source for an Access report, for which no useful answer was given, and the guy gave up and put the data into a table. I also didn't find any useful hits on Google, so I decided to figure it out for myself. I applogize to the closed thread questioner for being nearly 5 years late in offering a solution.

1) Leave the RecordSource property blank
2) Create your controls in the Detail section of the report
2) Declare an integer variable en the VBA to use as a pointer into your array (nCurrentRow), and of course create the array(s).
3) In the Detail_Print event handler, include this code:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
  nCurrentRow = nCurrentRow + 1 ' Advance to next row of your array (assumes base of 1, not 0, adjust accordintly)
  txtControlName1 = YourArray1(nCurrentRow) ' populate the control(s)
  txtControlName2 = YourArray2(nCurrentRow)
  Me.MoveLayout = True ' advance to the next detail print position
  Me.NextRecord = nCurrentRow = ubound(YourArray) ' Signal End of File when the last array element has been printed
  Me.PrintSection = True ' This line is optional because true is the default
End Sub

Note that this works as long as you're not using "keep together" options which require retreating, reformatting and reprinting of rows.

[NeoPa]

This is not an article.

If the other thread was closed then I suspect it wasn't actually a Bytes thread, but an import from elsewhere. Without even a link it's very hard to know.

This type of response is fine for a question thread. It's just not an article. It would need a great deal more work to become one. See HowTo Section Submission Guidelines.