Print Records in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report based on a query that has x number of records in it, what I
would like to do is loop thru each record in the query and print the report
then move onto the next record and so on. I am aware that this is a loop
statemnet just not sure of the format.
the query is called printme adn the report is called printmereport

How would I achieve this

Thanks
 
Try this
Open recordset, loop through the records, print the report with filter from
the recordset
The where condition, it the filter you apply to the report, you need to
specify the name of the field in the report and in the recordset.

Function FunctionName()
Dim MyDB as database, MyRec as Recordset

Set MyDB=Codedb()
Set MyRec=MyDB.OpenRecordset("Select * From printme")
While Not MyRec.Eof
' If the Where condition apply on a string field use this
Docmd.OpenReport "printmereport"",,,"MyFieldInReport= '" &
MyRec!FieldInRecordset & "'"
' If the Where condition apply on a Number field use this
Docmd.OpenReport "printmereport"",,,"MyFieldInReport= " &
MyRec!FieldInRecordset

MyRec.MoveNext
Wend

End Function
 
Hi, Nigel.

Set a reference to the DAO library (if you don't already have it
referenced). If your table has a primary key, then you could try the
following:

Public Sub printAllRecsOneAtATime()

On Error GoTo ErrHandler

Dim recSet As DAO.Recordset
Dim fld As DAO.Field
Dim sPKey As String
Dim idx As Long
Dim fOpenedRecSet As Boolean

sPKey = "ID"
Set recSet = CurrentDb().OpenRecordset("printme")
fOpenedRecSet = True
Set fld = recSet.Fields(sPKey)

If (Not (recSet.BOF And recSet.EOF)) Then
recSet.MoveLast
recSet.MoveFirst

For idx = 1 To recSet.RecordCount

DoCmd.OpenReport "printmereport", acViewNormal, , sPKey & " = "
& fld.Value ' Number value
'DoCmd.OpenReport "printmereport", acViewNormal, , sPKey & " =
'" & fld.Value & "'" ' String value.

If (Not (recSet.EOF)) Then
recSet.MoveNext
End If
Next idx

End If

CleanUp:

Set fld = Nothing

If (fOpenedRecSet) Then
Set recSet = Nothing
fOpenedRecSet = False
End If

Exit Sub

ErrHandler:

MsgBox "Error in printAllRecsOneAtATime( )." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub

Change the sPKey value (ID) to the name of the primary key of your table.
Use the first DoCmd line of code for a numeric primary key or use the second
(commented out) DoCmd line of code for a string value primary key.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Back
Top