Recordset variable with SQL

[Luc McCutcheon]

Hi

I have an Access 2000 project link with a SQL Server 2000
database. In my SQL Server I have a table with some fields
who I would like to print but a different page number for
each recordset. So I had type that vba programs to do it
but when the program do the SET LastPage line I receive
that error message: Object variable or With bloc variable
not set.

Private Sub cmdPPreviewR_Test1_DblClick(Cancel As Integer)
On Error GoTo Err_cmdPPreviewR_Test1_DblClick

'Variable declaration
Dim stDocName As String
Dim Nb As Integer
Dim Page As Integer
Dim PageTotal As Integer
Dim Dbprtqty As Database
Dim LastPage As Recordset

'Database Name
Set Dbprtqty = Application.CurrentDb

'Report Name
stDocName = "R_ReelLabelsJauneSN"

'Record variable
Set LastPage = Dbprtqty.OpenRecordset("T_PrtQty",
DB_Opentable, DB_READONLY)

'Goto First Record
LastPage.MoveFirst

'Fixe varaible Page to 1 Before Starting the Loop
Page = 1

' Start Loop
Do

'Amount of Pages to Print for that Record
Nb = CLng(Me.Meters) / 2500 + 1

'Print the right Page with the right Qty of Pages
DoCmd.PrintOut stDocName, Page, Page, acHigh, Nb, False

'Prepare for Next Page
Page = Page + 1

'Goto Next Record
LastPage.MoveNext

'Condition to Stop the Loop
Loop Until LastPage.EOF = True


Exit_cmdPPreviewR_Test1_DblClick:

Exit Sub

Err_cmdPPreviewR_Test1_DblClick:
MsgBox Err.Description
Resume Exit_cmdPPreviewR_Test1_DblClick

End Sub


I will appreciate if somebody are able to help me.

Thanks

 

[Larry Linson]

Just an idle question? Wouldn't it be simpler to use a Report?

Larry Linson
Microsoft Access MVP

 

[Luc McCutcheon]

That program have been created to print a report. The
report variable is stDocName and the report name is
R_ReelLabelsJauneSN.

The idea for that program is to print some labels for
products identification. The informations for the lables
are in a table who we have 5 fields per row. In one of
those fields we have an amount (in meters) of the product
we do. The amount of meters fields is not always the same
it depends of the amount ordered by our customer. But if
that amount exceed 2500 meters we have to produce more
than one labels because our production machine are not
able to make rolls over 2500 meters.

The only thing I would like to be able to do is to print
that report with the proper amount of labels with out
doing a calculation myself for each product. That program
is link on a button in a form just to print a report.

Now what we do we make a print preview and for each page
of the report and we print it one by one with the proper
amount of page base on the amount of meters for each
order. But when we have a lot of orders it takes a long
time to do it and I'm sure we are able to do it
automatically.

Hope it explain better the reason of the program and the
main idea on the structure of the program.