?
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
Medium Priority
?
454 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 1000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

719 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