Results 1 to 14 of 14
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Printing All Records

    Hi everyone, I have got a report based on client name and date, the criterias on the report are a Date (selected from combo on form) and a client name, (selected from combo on form), if I have 38 clients on that specific date, I click 38 names, preview the report then print, I have tried grouping the report on client, this just lists all clients on the report, I would like to keep 1 x print per client regardless if more than one page, is there a method where I can click 1 command button and it prints each client from the date on each clients report rather than listing the clients continuous ??, hope everyone can understand!! much appreciated regards DMT Dave

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You could loop through each record in turn and send that for printing then move onto the next record

    OR easier still print all records in one batch but design the report to start a new page for each record.
    You can even reset the page numbering so instead of showing e.g. Page 1 of 38 it will say Page 1 of 1 or Page 1 of 2
    To do that, google Access Reports Group Page Numbering
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Dave,

    You need to loop through the client ids for that date and for each client to do your printing:

    Assuming you have a query that returns the list of clients for the date selected called qryClientsSelectedDate you could try:
    Code:
    cmdMybutton_Click()
    dim rClient as dao.recordset
    
    set rClient=currentdb.openrecordset("qryClientsSelectedDate")
    
    if rClient.recordcount=0 then goto Exit_Print
    
    rClient.movefirst
    
    do until rClient.EOF
    
    docmd.openreport "rptClients",acViewNormal, "[ClientID]=" & rClient("ClientID")  'assumes ClientID is numeric
    
    rClient.movenext
    
    loop
    
    
    
    Exit_Print:
    Set rClient=Nothing
    
    end sub
    Cheers,
    Vlad

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi ridders52, thanks for the info, will let you know the result

  5. #5
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Vlad, thank you your reply, will give that a try, I do work a lot with recordsets so your vba code idea makes sense , will let you know how I get on regards Dave

  6. #6
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Guys, I have tried the following code, which works perfect apart from one of the postcodes only show 6 records on the report and there are 8 records for that postcode, to test this, I changed the last letter of the postcode then it generated 5 on one page and 1 on another, do you know why it won't list the 8 records for that postcode ??, note there is another postcode record that shows 9 on the report and there is 9 on the system!, lastly, I created a group by postcode on the report and went to properties of the postcode group and changed "force new line" to after selection, again all works fine apart from one particular postcode, triple checked for characters and spaces and all the same!!!!! thanks guys

    Dim MyDate as Date

    MyDate = Format(Me.cboShipmentDateIndex1,"mm/dd/yyyy")

    Set rs2 = CurrentDb.OpenRecordset("Select * FromtblEdit WHERE ShipmentDate = #" & MyDate & "#")

    Do Until rs2.EOF

    DoCmd.OpenReport "rptShipmentInName",acViewPreview, "[PostCode] = " & rs2.Fields("PostCode")

    rs2.MoveNext
    Loop

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try to put a MoveFirst before entering the loop:
    Code:
    Dim MyDate as Date
    
    MyDate = Format(Me.cboShipmentDateIndex1,"mm/dd/yyyy")
    
    Set rs2 = CurrentDb.OpenRecordset("Select * FromtblEdit WHERE ShipmentDate = #" & MyDate & "#")
    r2.movefirst
    Do Until rs2.EOF
    
    DoCmd.OpenReport "rptShipmentInName",acViewPreview, "[PostCode] = " & rs2.Fields("PostCode")
    
    rs2.MoveNext
    Loop
    Cheers,
    Vlad

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Cheers Vlad, I will try that thank you so much will get back to you regards Dave

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Agree with vlad and I'd take it further....
    Also space missing between tblEdit and FROM
    FWIW this is how I'd do it

    Code:
    Dim MyDate as Date, strPostcode As String
    
    MyDate = Format(Me.cboShipmentDateIndex1,"mm/dd/yyyy")
    
    Set rs2 = CurrentDb.OpenRecordset("Select * FROM tblEdit WHERE ShipmentDate = #" & MyDate & "#")
    
    With rs2
    
    .MoveLast
    .MoveFirst
    
    Do Until .EOF
    
    strPostcode = !Postcode
    DoCmd.OpenReport "rptShipmentInName",acViewPreview, "PostCode='"& strPostcode & "'"
    
    .MoveNext
    Loop
    .Close
    
    End With
    
    Set rs2=Nothing
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    In my first reply (post #2) I recommended a different approach which means no recordsets are required.

    Print all reports in one batch but using report grouping reset the numbering at the start of each group (in your case postcode)
    Over the years, I've regularly used the approach to print out huge numbers of reports of the same type without needing to loop through recordsets
    It works very well

    Here are two links showing different approaches to the idea:

    This is the standard approach using arrays and is the method I always use.
    It looks complex but is easy to use even if you don't understand the code
    http://access.mvps.org/access/reports/rpt0013.htm

    The second method is so simple that its very easy to understand...but I haven't tried it
    http://www.sokhawin.com/reset-page-n...p-in-a-report/
    I'd be interested to know if that really does work - it looks good to me
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi guys, I have another one I can't seem to achieve, adding a signature to an email body, the following is where I am at, the getboiler I found on the net and email from my database, I use emails a lot from access both attaching pdfs and direct data to mailbody, any idea's on signatures as I use about 8 different signatures ?? ie orders@ enquiries@ dave@ etc Note: Signatures are definitely located in the "MySig" path

    Modules / Public Subs

    Public Function GetBoiler (ByVal, sFile As String) as String
    Dim fso as Object, ts as Objecto
    Set fso = CreateObject("ScriptingFileSytsemObject")
    Set ts = fso.GetFile(sfile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
    End Function

    COMMAND BUTTON

    Dim MyApp as Outlook.Application
    Dim MyItem as Outlook.MailItem
    Dim MySig, Signature, MyBody as String

    MySig = Environ ("AppData") & "\Microsoft\Signatures\orders@.htm"

    If Dir(MySig <> "" Then
    Signature = GetBoiler (MySig)
    Else
    Signature = ""
    End IF

    Set MyApp = CreateObject("Outlook.Application")
    Set MyItem = MyApp.CreateItem(olMailItem)
    MyBody = MyBody & "Test Mail From Me"
    With MyItem
    .To = "dave@home.com"
    .Subject = "TEST MAIL FROM ME"
    .body = MyBody & Chr(10) & Chr(10) & _
    Signature
    .Display
    End With

    This generate the mail but doesn't add signature!!!!!!! thanks again guys for all of your help on the report, I hope I correctly gave you comments and stars

  12. #12
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Dave, have you stepped through the code (the if block where you retrieve the signature) and see if the string variable gets filled? You seem to be missing a ) on the Dir function but that might just be a forum bug.

    Cheers,
    Vlad

  13. #13
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Vlad, yes, the ) that Is missing is because I struggled to copy and paste the code so just typed it out and missed putting the bracket in, the code has got it though, I put a msgbox on the return the path and file name of the signature orders@ and confirms it exists, all works ok and opens a new email, just won't add signature, I did find that the signatures are in hidden folders so I have chosen to untick show hidden files and folders to find them initially, any ideas on how to add signature ??? much appreciated Dave

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Post within CODE tags to retain indentation and readability as well as special characters.

    Examples in links in your other thread https://www.accessforums.net/showthread.php?t=71867 did not work? Did you search forum? I see a number of threads on the topic. Here is one https://www.accessforums.net/showthread.php?t=68563
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Having trouble printing 2 records per page?
    By SamuelDaneliuk in forum Access
    Replies: 5
    Last Post: 11-14-2017, 05:08 PM
  2. Printing Specific Records
    By spyldbrat in forum Reports
    Replies: 2
    Last Post: 12-28-2015, 01:23 PM
  3. Replies: 18
    Last Post: 06-03-2014, 10:17 AM
  4. printing multi records
    By shenix2005 in forum Reports
    Replies: 7
    Last Post: 08-20-2010, 09:56 AM
  5. Best way of displaying and printing records?
    By Orabidoo in forum Access
    Replies: 1
    Last Post: 05-11-2009, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums