Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79

    Unanswered: Loop through Query and print records

    I have a Query "QryLookup" that contains the RecordID of all the residents in our Nursing Home. I need help to write code that will loop through QryLookup and print these reports for each resident and then move on to the next.

    Sometimes there won't be a record for that report and I need it to ignore it and just move on to the next report


    This is the code I use to print one record at a time for these reports but obviously I don't want to bring each resident up to the form and print individually.

    Dim stDocName As String

    stDocName = "rpADLGrid 1-15"
    DoCmd.OpenReport stDocName, acNormal, , "[RecordID]=" & Me!RecordID
    Dim stDocName As String

    stDocName = "rpADLGrid 16-31"
    DoCmd.OpenReport stDocName, acNormal, , "[RecordID]=" & Me!RecordID

    stDocName = "rptContinenceTracking"
    DoCmd.OpenReport stDocName, acNormal, , "[RecordID]=" & Me!RecordID

    stDocName = "rptNsgRToiletingFlowSheet"
    DoCmd.OpenReport stDocName, acNormal, , "[RecordID]=" & Me!RecordID

    stDocName = "rptNsgRehabFlowSheet"
    DoCmd.OpenReport stDocName, acNormal, , "[RecordID]=" & Me!RecordID


    Thanks for your time

    Perplexed

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    rptContinenceTracking
    rptNsgRToiletingFlowSheet
    Oh good lord - my (long gone) days of nursing home health care come flooding back. I don't see any fluid balance reports - or is that called toileting flow these days?

    Anyway - I digress (not for the first time).
    Presumably you need to print each report in turn for each resident in turn? You can't just print report 1 for all the required residents and then report 2 for all the required residents... and so on and collate? Assuming no (gotta ask!) then:
    You need to pop the reuslts of QryLookup into a recordset and loop through that until the EOF property of the recordset is TRUE (i.e. you've reached the end). At each pass you want to run through your code substituting the relevent recordset field value for Me!RecordID. Does that sound ok (you can find info in help under DAO and ADO recordsets) or do you need some code help? Have a shufty first though - always more satisfying if you crack it yourself.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - an alternative (must be the wine getting the creative juices going) would be to create a Meta-Report (TM). This would be one big report based on QryLookup with all your other reports as sub reports in a resident header section. Idea?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Oh good lord - my (long gone) days of nursing home health care come flooding back. I don't see any fluid balance reports - or is that called toileting flow these days?

    methinks hes just taking the pish
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79
    Thanks,

    That's useful information. I see you are familiar with my world. I have been giving Medical Records stacks of reports to collate into the books and she says it take 3 hours so I told her I would try to fix it.

    Good exercise. I always like to see what else I can make it do.

    Must have those toileting flowsheets

    Perplexed

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well - there's no problem printing off a copy of each report for each resident in turn. Let us know how you get on and get in touch if you get stuck

    Us carers stick together Mark - it's like being in a guild. Once you've cleaned up some of the things we have you have a special bond. You can see it in the depths of the eyes - a haunted, faraway look marks us out.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79

    Red face Stuck on recordset and looping

    Hi,

    I tried the mega report idea and it did each report in a batch and moved to the next report but every page had the same name on it, I must have goofed it up.

    I absolutely surrender with the "pop the results into a recordset and loop through" idea. I think it would be easier to slip medical records 20 bucks to keep sorting.

    Please help

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    I absolutely surrender with the "pop the results into a recordset and loop through" idea
    The basic jist is:
    Code:
    Dim rsDat As RecordSet
    
    Set rsDat = CurrentDB.OpenRecordSet("Select [PatientID] From [tblPatients] Where [IsAlive] = True")
    
    Do
        Docmd.OpenReport "rptPatients",acViewNormal,,"PatientID = " & rsDat(0)
        rsDat.MoveNext
    Loop Until rsDat.EOF
    acViewNormal means "Print it" which may catch the user by surprise (I was actually asked by a client once to warn them when pages are going to print ... immediately following pressing a PRINT BUTTON !!!! )

    Personally, I prefer sending "print outs" to Snapshot.
    Code:
    Do
        Docmd.OpenReport "rptPatients",acViewPreview,,"PatientID = " & rsDat(0) 
        DoCmd.OutputTo acOutputReport, "rptPatients", acFormatSNP, , True
        Docmd.Close acReport,"rptPatients",acSaveNo
        rsDat.MoveNext
    Loop Until rsDat.EOF
    Snapshot is a portable document format for reports, much like PDF. Info about the viewer, available from Miscrosoft, here

    Let us know how it works out!

    tc

    Edit:
    I forgot to warn you that this is "Air Code" so you may need to do a bit of debugging and perhaps hit the help file to correct my syntax!

  9. #9
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79

    Looping through record set

    Thanks fellas for your help. It is much appreciated.

    I almost have what I need. The code below cycles through the record set and prints reports in the proper order. However I need to be able to tell it to ignore a report if that RecordID does not appear in that report. Right now it prints a record with no identifying information.

    Any ideas?
    Perplexed

    Private Sub cmdMonthlyrpt_Click()

    Dim rsDat As Recordset

    Set rsDat = CurrentDb.OpenRecordset("Select [RecordID] From [QryLookup] Where [recordID] = True")

    Do
    DoCmd.OpenReport "rptADL Grid - All", acViewNormal, , "RecordID = " & rsDat(0)

    DoCmd.OpenReport "rptContinenceTracking", acViewNormal, , "RecordID = " & rsDat(0)

    DoCmd.OpenReport "rptNsgRToiletingFlowSheet", acViewNormal, , "RecordID = " & rsDat(0)

    DoCmd.OpenReport "rptNsgRehabFlowSheet", acViewNormal, , "RecordID = " & rsDat(0)

    rsDat.MoveNext
    Loop Until rsDat.EOF

    End Sub

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Not sure how this would work with you printing directly (I always use preview) however I sometimes have the report check its own HasData property and close down if it doesn't (the Open event has a Cancel parameter that you can set to true). You'll need to sort out some error trapping if you go with that but it might be worth testing first - I would imagine it would stop the print but, as I say, I don't know for sure.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tcace
    acViewNormal means "Print it" which may catch the user by surprise (I was actually asked by a client once to warn them when pages are going to print ... immediately following pressing a PRINT BUTTON !!!! )
    Lol.

    There once was some code on www.thedailywtf.com something like the below:
    Code:
    Private Sub DoStuff(Param1 AS string, Serious AS Boolean)
     
    If Serious = True Then
         'Do stuff - invloved altering data as I recall
    End If
     
    End Sub
    Classic
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    Indiana, USA
    Posts
    79
    Quote Originally Posted by pootle flump
    Hi

    Not sure how this would work with you printing directly (I always use preview) however I sometimes have the report check its own HasData property and close down if it doesn't (the Open event has a Cancel parameter that you can set to true). You'll need to sort out some error trapping if you go with that but it might be worth testing first - I would imagine it would stop the print but, as I say, I don't know for sure.

    HTH
    This does shut down the printing all together.
    Hadn't considered the idea though. Maybe a way around shut down


    MsgBox "There is no data for this report. Canceling report..."
    Cancel = -1

    Perplexed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •