Solved

limit print of report to 25 records from the subform and open next report with the next 25 records if there are  more

Posted on 2013-11-23
17
449 Views
Last Modified: 2013-12-01
Hi all.

I have developed a quotation system. There is a disadvantage when printing the report. The report contains a lot of linked images that are associated with the products. Therefore I want the report does not contain more than 25 ID records from the subform. The report should be limited to 25 ID records from the subform, and then close and open a new report if more records are available (the following records from the subform) also limited to 25 records.

Otherwise the report crashes (empty picture fields) if there are more records in the report.

I'll hope some one could help me out with this! Thanks allready for helping me out with this.
0
Comment
Question by:mvdwal
  • 9
  • 8
17 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39672319
You can add counter to your source query with this function:
http://support.microsoft.com/kb/94397/en-us
or with something like to:
cntr: Dcount("*","YourQuery","YourID <=",[YourID])
int((cntr-1)/25) will give you report number
you can call your reports with this code:
Dim RC As Integer, i As Integer
Dim C As Long
C = DCount("*", "YourQuery")
RC = (C - 1) / 25
For i = 0 To RC - 1
    DoCmd.OpenReport "YourReport", acViewPreview, , "(cntr-1)/25 = " & i
Next i

Open in new window

0
 

Author Comment

by:mvdwal
ID: 39672482
Hi thanks for your help. Is This a good solition? You must reset the counter each time to 0. This is not a good idea. There are more people working in the database. Or i have to use the counter seperate for Every customer by adding the customerid?
0
 
LVL 39

Expert Comment

by:als315
ID: 39672494
If you like to use first method, you must reset counter every time. I prefer dcount and similar ways. It may be not so fast, but in this case you will not have problems.
If you have many people working, you should use splitted DB (with individual front end).
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:mvdwal
ID: 39672505
I think a counter for each subform is a great solution?  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21522494.html

We allready splitted the database in front and backend.

If I use a counterfield on each subform, how can I call it for the right records in the report? I'm not a coding expert.
0
 
LVL 39

Expert Comment

by:als315
ID: 39672519
You should not do anything with your subform. Just use the code when you are start your report.
0
 

Author Comment

by:mvdwal
ID: 39675610
Private Sub Report_Open(Cancel As Integer)
    If Not IsLoaded("Add an Offer and Details") Then
        MsgBox "Open this report using the Preview button on the Add an Offer and Details form."
        Cancel = True
    End If
    
    Dim TotalRec1 As Long

TotalRec1 = DCount("OfferDetailID", "Offer Details", "OfferDetailID" = "(-1)/25")

End Sub

Open in new window


Of course I'm doing something wrong
0
 
LVL 39

Expert Comment

by:als315
ID: 39677233
Your code is inside report. My code should be before report. On Form's button, for example.
0
 

Author Comment

by:mvdwal
ID: 39677673
Hi thanks for your reply. I can not figure it out.
Private Sub Knop222_Click()
Dim RC As Integer, i As Integer
Dim C As Long
C = DCount("*", "OfferDetailID")
'C = DCount("*", "Offer Details", "OfferDetailID <=", [OfferDetailID])


RC = (C - 1) / 25
For i = 0 To RC - 1
    DoCmd.OpenReport "Offer 1Pic_NL", acViewPreview, , "(cntr-1)/25 = " & i
Next i
End Sub

Open in new window


Normaly I open a report with a macro and use a filter : "[OfferID]=" & [Formulieren]![Add an Offer and Details]![OfferID]

I have the button placed on the main form (orderid) and I want 25 records from the subform (OfferDetailID) in the report. Instead of all. What do I need to change in the code?
form.JPG
offerid.JPG
0
 
LVL 39

Expert Comment

by:als315
ID: 39677772
May be you can upload sample DB with necessary tables filled with dummy data?
Code may be:
Private Sub Knop222_Click()
Dim RC As Integer, i As Integer
Dim C As Long, Fltr as string
C = DCount("*", "OfferDetailID", "[OfferID]=" & [Forms]![Add an Offer and Details]![OfferID])
'C = DCount("*", "Offer Details", "OfferDetailID <=", [OfferDetailID])


RC = (C - 1) / 25
For i = 0 To RC - 1
    Fltr = "[OfferID]=" & [Forms]![Add an Offer and Details]![OfferID] & _
             "AND (cntr-1)/25 = " & i
    DoCmd.OpenReport "Offer 1Pic_NL", acViewPreview, , Fltr
Next i
End Sub

Open in new window

You should add cntr field to report's source query
0
 

Author Comment

by:mvdwal
ID: 39678034
Thanks! I tried the code. I'll get the message that it can't find OfferDetailID

C = DCount("*", "OfferDetailID", "[OfferID]=" & [Forms]![Add an Offer and Details]![OfferID])

Open in new window


the above code is highlighted
0
 
LVL 39

Expert Comment

by:als315
ID: 39678572
Thats why I've asked upload sample. OfferDetailID - name of your query. Correct it.
0
 

Author Comment

by:mvdwal
ID: 39678708
Can you be more specific Please ? I don't understand what you mean.... Do you mean the query of the report?
0
 
LVL 39

Expert Comment

by:als315
ID: 39679884
Yes, correct, this is name of this query
0
 

Author Comment

by:mvdwal
ID: 39680129
Thanks I did specify the query.

Access displays the Specify Parameter Value dialog box when I open the report (cntr).

Then my own message that there is no data.

Private Sub Knop222_Click()
Dim RC As Integer, i As Integer
Dim C As Long, Fltr As String
C = DCount("*", "Offer", "[OfferID]=" & [Forms]![Add an Offer and Details]![OfferID])
RC = (C - 1) / 25
For i = 0 To RC - 1
    Fltr = "[OfferID]=" & [Forms]![Add an Offer and Details]![OfferID] & _
             "AND (cntr-1)/25 = " & i
    DoCmd.OpenReport "Offer 1Pic_NL", acViewPreview, , Fltr
Next i
End Sub

Open in new window

cntr.JPG
highlighted.JPG
0
 

Author Comment

by:mvdwal
ID: 39680806
Hi, here's a sample db
Database1.accdb
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39681990
I don't understand some parts in your queries and report (you should not use lookup fields in report and add only necessary fields to query), but you can look at sample, where my idea was realized.
Database1.accdb
0
 

Author Comment

by:mvdwal
ID: 39683498
Thanks a lot! I'll try the solution!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question