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
440 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:mvdwal
Comment Utility
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
Comment Utility
You should not do anything with your subform. Just use the code when you are start your report.
0
 

Author Comment

by:mvdwal
Comment Utility
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
Comment Utility
Your code is inside report. My code should be before report. On Form's button, for example.
0
 

Author Comment

by:mvdwal
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Expert Comment

by:als315
Comment Utility
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
Comment Utility
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
Comment Utility
Thats why I've asked upload sample. OfferDetailID - name of your query. Correct it.
0
 

Author Comment

by:mvdwal
Comment Utility
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
Comment Utility
Yes, correct, this is name of this query
0
 

Author Comment

by:mvdwal
Comment Utility
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
Comment Utility
Hi, here's a sample db
Database1.accdb
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
Comment Utility
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
Comment Utility
Thanks a lot! I'll try the solution!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now