Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 43
  1. #16
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    "frmStatementFilter" is a form that is open at the time this code runs, and "txtEmailCust" is a textbox on that form. "CustomerAccount" is a field in the recordset that I want to send PDF's out based on, so "rs!CustomerAccount" is the value from the recordset. So what's happening there is that on each pass through the recordset, the current customer account number from the recordset is being put into that textbox. Like the link I gave you earlier, in the report's open event is this code:

    Me.Filter = "CustomerAccount=" & Forms!frmStatementFilter.txtEmailCust


    Me.FilterOn = True

    Which causes the report to filter itself to whatever customer is in that textbox. You would have to change all the names to match yours.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  2. #17
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Right then, I am slowly getting it. By the way your daughter may have woken up to snow today !!!
    So does this mean I can only achieve what I want to do from an open form with the data in it? What I was kind of hoping is that I could have a report(I have one already but its data is not filtered by a query) and just get my code to separate by "Location".
    I also have a query that asks for a search to be input in to an input box. So for instance I type in "thea*" and I get a report then based on all equipment in our operating theatres. Could I get it to use the query to filter the data one location at a time, output it (pdf) and then change the value of the query. Ultimatly giving me data for all locations but grouped.
    Cheers

  3. #18
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That technique relies on a form, but that form doesn't really need to be related, just have a textbox (which can be hidden) that can be used for this. You could also use a global variable in VBA. To answer your question, you can dynamically set the parameters of a query, though I'm not sure it works on bracket type criteria. The code I'm thinking of is here:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

    But I think the other method would be simpler. Can you post a sample of the db? If so I can probably get it going for you.

    The kid should be able to handle the snow; she was born in the middle of a storm that dropped 5 feet of snow on us (close to 2 meters?). She grew up in the stuff!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #19
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    cheers.
    Give me an hour, just going for some food.!!!

  5. #20
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Hi Paul,
    Sorry for the delay! Family commitments took a hold!
    I will PM the database to you, it nothing cofidential and the data is hardly earth shattering but it is work based data so I ought to trwat it with a mediocum of respect etc.
    You will see I have a form which is my test form etc. You will see that in this test I have left the code referances etc the same to keep things simple when comparing it to the code from the web link you supplied. Unfair to sort out multiple problem that are created needlessly and so on!!
    You will see the report that is in capital letters is the one I ultimatley want to output to separate PDF files in a one click action but using the "Location" as the PDF file name. I have another database that I have used for testing Lebans code and know how to get that to create the PDF but not individual PDFs.
    You will see in the data Table that I have clearly deleted a lot of data entries, again to keep things simple and the data file small etc while testing.
    Thank you so much for your help, I do appreciate it

  6. #21
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Oh. No attachment option in PM facility!
    How best can I get my file over to you?
    Cheers
    Dave

  7. #22
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Okay made these changes and get 27 individual files created, as expected. Relevant code in form, notable changes in red:

    Code:
      Set RS = MyDB.OpenRecordset("SELECT DISTINCT Location FROM [Equipment List]")
    
        Do Until RS.EOF
          Me.txtProgress = RS!Location
          DoCmd.OutputTo acOutputReport, "Equipment List", acFormatPDF, "C:\datafiles\Test" & RS!Location & ".pdf"
          RS.MoveNext
        Loop
    
      End If
    Code added to report:

    Code:
    Private Sub Report_Open(Cancel As Integer)
      Me.Filter = "Location = '" & Forms![Exp to excel].txtProgress & "'"
      Me.FilterOn = True
    End Sub
    I have 2007 here with the built-in PDF capability, you'd have to substitute the Lebans code for the DoCmd.OutputTo line. I would clean up the code and put in proper exit/error handlers, but that should work.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #23
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Since you mentioned snow in your pm, attached is a pic from early this morning, at which point we had gotten about a foot and a half. It's continued snowing all day. RuralGuy would laugh at this, as he lives higher in the mountains and this would probably qualify as a "light" snow storm for him. For us, it's more than average, but not that uncommon.

    My daughter has mentioned how much trouble the snow causes over in the UK.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #24
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Thank you for your help. Just tinkered about with the code and got it to work with Lebans code outputing to PDFs. Thank you very much for your help. I am going to experiment some more and see what happens etc a learn a little more.

    As for snow, you must be mistaken. That would be classified as an iceberg surely! You are even measuring it with an 'icebergometer'. Hell now thats snow!
    Great pic. I have no impressive pics of the UK to hand. Nothing compares to that.
    Cheers again.
    I will be back to tap you up for some more help if I can if you dont mind.
    thanks.

  10. #25
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're calling my wife an 'icebergometer'?!?!?

    I saw a pic of the UK a month or so ago when you were having big storms. It was a satellite picture and the entire UK was white with snow. Here it is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #26
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Hey no way re the wife comment!
    Yes that satellite picture seems to be one of our typical summer days here is the UK!!
    We seem to have 5 mins of snow and we are at a stand still or 5 mins of sun and the same. Yea we did have a shoking period of around 1 to 2 weeks about a month and a half a go. Seemed longer than usual and more wides spread. Still no way compares to your amount.
    Thanks for your help.

  12. #27
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Hi Folks,
    I have Dim'd a variable called 'recCount'.
    What I would like to do is display the totaly records converted. In other words where would I place something like: 'recCount = recCount + 1' so that it increments the counter with each pass of the loop?
    I can display it no probs, that much I have worked out.
    Cheers
    D

  13. #28
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You are what we call a "night owl" over here; up late at night! Placing that anywhere within the loop, between "Do..." and "RS.MoveNext" should work fine. The link had this right after the Do line:

    lngCount = lngCount + 1
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #29
    twosides is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    23
    Cheers
    You are right. Late nights to try and create that 30 hour day!
    The code there, and when put in a few other places just seems to give me a 'Location' count of files created. What I would like is a counter that increments with each record handled so that I get an overall total sum of records copied over to all my pdfs. It will form part of my checking.
    Cheers Paul,
    Dave

  15. #30
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The problem is that you're not handling each record, only each location. You could use a DCount() within your loop to get a count of the records for a given location, and increment that. That said, you could simply use a DCount() on the source of the report to get a total count.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Create CrossTable Query report
    By ysrini in forum Reports
    Replies: 1
    Last Post: 02-19-2010, 11:20 AM
  2. How to create and print unique report for each entry???
    By Stelios Panayides in forum Reports
    Replies: 2
    Last Post: 10-05-2009, 08:54 AM
  3. Create a recordset for a report
    By vjboaz in forum Programming
    Replies: 0
    Last Post: 11-18-2008, 03:28 PM
  4. Create a date 'from' - 'to' on Report
    By Jule in forum Reports
    Replies: 7
    Last Post: 12-07-2006, 02:34 AM
  5. Create a calendar report?
    By valkyry in forum Reports
    Replies: 0
    Last Post: 09-14-2006, 11:34 PM

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