473,322 Members | 1,510 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

MS Access 2002 - print 30 records at a time

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
3 4937
ADezii
8,834 Expert 8TB
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
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
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

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

Similar topics

1
by: Steve Claflin | last post by:
I have a database with a moderate number of records in several tables (the biggest table at the moment is about 800 records). In development it got moved between 2K and XP repeatedly. Several...
4
by: Tom Dauria | last post by:
I have an application that will be distributed remotely. In the Access application I am opening Word documents and mail merging. The Word documents are linked to a tmpLetter table. In my code I...
0
by: Miguelito Bain | last post by:
hi everybody- i've got a conundrum... i inherited some old databases, and i'm trying to convert them. i run office xp with access 2002, and all of the databases i manage are either in 97...
0
by: Malcolm Cook | last post by:
I've discovered: Using "File > Print Preview" in a form's datasheet view with a server Filter crashes access after previewing... ....that is, unless ServerFilterByForm has been turned off...
3
by: Leinad Kong | last post by:
I'm using Access 2002, as front-end and back-end as well: 1) I faced database corrupted problems, when more than 1 user edit concurrently. I'm using All-records Locking, and open-exclusively as...
8
by: David Kistner | last post by:
I'm fairly new to Access (I've worked with Oracle and MySQL in the past). I was asked to build an application for a small office and told that I had to use Access 2002. I was VERY uncomfortable...
2
by: SKarnis | last post by:
We are trying to rebuild a current Access 2002 (XP) mdb program to VB.NET with a SQL database - we are having problems with a suitable combobox. There are many threads discussing multiple column...
13
by: salad | last post by:
Hi Guys: I was stuck. I needed to send a report to a file. My beautiful report(s) in Access were going to require loss of formatting with RTFs, a PITA in WordMailMerge, sending it as a text...
13
yolenman
by: yolenman | last post by:
Hello - This is my first posting to this group, so please bear with me. Also note, that while I'm intelligent, databases are not in my field of knowledge. I'm working with a small limousine...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.