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
452 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 40

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 40

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 40

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 40

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 40

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 40

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 40

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 40

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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