By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,903 Members | 2,075 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,903 IT Pros & Developers. It's quick & easy.

MS Access 2002 - print 30 records at a time

P: 3
I know that I am overcomplicating this. I have records that need to be printed. Lets say 536 records, some on two or more pages in the report. I want to print 30 records, then 30 more till the printer has printed the whole report. I believe that VBA will be the best way to do this automatically with the click of a button. The reason I want to print 30 records at a time is that I have to put these in groups of 30 and I have to do the math in my head as to 30 records later and would be much easier just to print 30 at a time as our printer will offset each print job so that you can tell the difference between the print jobs. I know this has to be easier than what I am trying to piece together on my own. If someone could please help I would appreciate it, thanks.

Chris
Apr 24 '06 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,638
I implement a similiar functionality in Form View. Hope this helps:

Private Sub cmdPrintRecordRange_Click()
On Error GoTo Err_cmdPrintRecordRange_Click

Dim StartRecord As Integer, EndRecord As Integer
Dim strSQL As String, Response As Integer
Dim intRecordsSelected As Integer, Msg As String

StartRecord = InputBox("ENTER First Record to Print")
EndRecord = InputBox("ENTER Last Record to Print")

'Change the RecordSource of the Form temporarily
strSQL = "SELECT * From [tblStorLoc] WHERE [StorLocID] Between " & StartRecord & " AND " & EndRecord
Me.RecordSource = strSQL

Dim MyDB As Database, MyRS As Recordset
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
MyRS.MoveLast
intRecordsSelected = MyRS.RecordCount
MyRS.Close

Msg = "You have selected " & intRecordsSelected & " Record(s) for Printing. Do tou wish to "
Msg = Msg & "Print them at this time?"

'Wish to proceed
Response = MsgBox(Msg, vbYesNo + vbQuestion, "Printout Verification")
If Response = vbYes Then
Me![txtStart] = StartRecord 'Show Start Record on Form for reference
Me![txtFinish] = EndRecord 'Show End Record on Form for reference
'Printout the selected Records
DoCmd.PrintOut acPrintAll
Else
'Show ALL Records again
Me.RecordSource = "SELECT * From [tblStorLoc]"
End If

Exit_cmdPrintRecordRange_Click:
Exit Sub

Err_cmdPrintRecordRange_Click:
MsgBox Err.Description, vbExclamation, "Error in cmdPrintRecordRange()"
Resume Exit_cmdPrintRecordRange_Click
End Sub
Apr 24 '06 #2

P: 3
I tried modifying your code but keep running to errors, first of all it would not let me dim mydb as database, then it went downhill from there. Here is the code I have so far.

Sub Print30()
On Error GoTo Err_Print30

Dim StartRecord As Integer, EndRecord As Integer
Dim strSQL As String, Response As Integer
Dim intRecordsSelected As Integer, Msg As String

'StartRecord = InputBox("ENTER First Record to Print")
'EndRecord = InputBox("ENTER Last Record to Print")

'Change the RecordSource of the Form temporarily
'Dim MyDB As Database, MyRS As Recordset
Set MyDB = CurrentDb()
'set MyQuery = Current
strSQL = "SELECT * From [willcalls]"
Set MyRS = MyDB.OpenRecordset(strSQL, dbforwardonly)
MyRS.MoveLast
intRecordsSelected = MyRS.RecordCount
MyRS.Close
b = intRecordsSelected
MsgBox "records: ", vbOKOnly, "records"

For a = 1 To b Step 30
StartRecord = a
EndRecord = a + 29
strSQL = "SELECT * From [tblStorLoc] WHERE [StorLocID] Between " & StartRecord & " AND " & EndRecord
'Me.RecordSource = strSQL

'Dim MyDB As ADODB.Connection, MyRS As ADODB.Recordset
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
MyRS.MoveLast
intRecordsSelected = MyRS.RecordCount
MyRS.Close

'Msg = "You have selected " & intRecordsSelected & " Record(s) for Printing. Do tou wish to "
'Msg = Msg & "Print them at this time?"

'Wish to proceed
'Response = MsgBox(Msg, vbYesNo + vbQuestion, "Printout Verification")
'If Response = vbYes Then
'Me![txtStart] = StartRecord 'Show Start Record on Form for reference
'Me![txtFinish] = EndRecord 'Show End Record on Form for reference
'Printout the selected Records
DoCmd.PrintOut acPrintAll
Next
'Else
'Show ALL Records again
'Me.RecordSource = "SELECT * From [tblStorLoc]"
'End If

'Exit_cmdPrintRecordRange_Click:
'Exit Sub

Err_Print30:
MsgBox Err.Description, vbExclamation, "Error in Print30()"
'Resume Exit_cmdPrintRecordRange_Click
End Sub
Apr 24 '06 #3

P: 3
I got it with lots of research and I thought I would post the code. to help another programmer out. I call this funtion using an access macro with therun code command. This will print out the one currently open report with groups that are the size that the user enters.

Expand|Select|Wrap|Line Numbers
  1. Public Function Print30()
  2.  
  3.     Dim Count As Integer
  4.     Dim Prints As Integer
  5.     Dim iAmmount As Integer
  6.     Dim sSource As String
  7.     Dim sReport As String
  8.     Dim sSql As String
  9.     Dim sArray(160, 160) As String
  10.     Dim Message, Title, Default
  11.     Dim sFieldName As String
  12.     sReport = Reports(0).Name
  13.     sSource = Reports(0).RecordSource
  14.     Message = "How many tickets would you like in each print packet?"    ' Set prompt.
  15.     Title = "# of Prints?"    ' Set title.
  16.     Default = "20"    ' Set default.
  17.     ' Display message, title, and default value.
  18.     iAmmount = InputBox(Message, Title, Default)
  19.     Set db = CurrentDb
  20.     sSql = "SELECT * FROM " & sSource
  21.     Set rst = db.OpenRecordset(sSql)
  22.     sFieldName = rst.Fields(0).Name
  23.     rst.MoveLast
  24.     Count = rst.RecordCount
  25.     Loops = Int(Count / iAmmount) + 1
  26.     Set rst = db.OpenRecordset(sSql)
  27.     With rst
  28.         Do Until .EOF
  29.  
  30.             For a = 1 To Count Step iAmmount - 1
  31.                 If a = 1 Then Prints = 0
  32.                 sArray(0, Prints) = .Fields(sFieldName)
  33.                 For b = 1 To iAmmount - 1 Step 1
  34.                     If .EOF Then
  35.                         rst.MoveLast
  36.                         sArray(1, Prints) = .Fields(sFieldName)
  37.                     Else
  38.                         .MoveNext
  39.                     End If
  40.                 Next
  41.                 If .EOF Then
  42.                 Else
  43.                     sArray(1, Prints) = .Fields(sFieldName)
  44.                     .MoveNext
  45.                     Prints = Prints + 1
  46.                 End If
  47.             Next
  48.         Loop
  49.     End With
  50.     Set rst = db.OpenRecordset(sSql)
  51.     For i = 1 To Prints Step 1
  52.         With rst
  53.             DoCmd.OpenReport sReport, , , "[" & sFieldName & "]>=" & _
  54.             sArray(0, i) & " and [" & sFieldName & "]<=" & sArray(1, i)
  55.         End With
  56.     Next
  57.     Set rst = Nothing
  58. End Function
Apr 28 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.