Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

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

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
mvdwal
Asked:
mvdwal
  • 9
  • 8
1 Solution
 
als315Commented:
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
 
mvdwalAuthor Commented:
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
 
als315Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mvdwalAuthor Commented:
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
 
als315Commented:
You should not do anything with your subform. Just use the code when you are start your report.
0
 
mvdwalAuthor Commented:
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
 
als315Commented:
Your code is inside report. My code should be before report. On Form's button, for example.
0
 
mvdwalAuthor Commented:
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
 
als315Commented:
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
 
mvdwalAuthor Commented:
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
 
als315Commented:
Thats why I've asked upload sample. OfferDetailID - name of your query. Correct it.
0
 
mvdwalAuthor Commented:
Can you be more specific Please ? I don't understand what you mean.... Do you mean the query of the report?
0
 
als315Commented:
Yes, correct, this is name of this query
0
 
mvdwalAuthor Commented:
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
 
mvdwalAuthor Commented:
Hi, here's a sample db
Database1.accdb
0
 
als315Commented:
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
 
mvdwalAuthor Commented:
Thanks a lot! I'll try the solution!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now