• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

How do I print a report twice in VB?

We have an application that generates a report based on a table created with VB and a couple of queries, taking user input of name and dates.  When the report is run from within the application, we don't get accurate data.  Here's the interesting thing.

If I close the report and immediately open the database window (F11) and run the report from there, the information is exactly what I want.

I've wrestled with this thing for months now and can't find a reason and I'm sick of playing with the code.  I'm trying to figure out 1) why is it happening and 2) How can I, after running the bad report, conceal that from the user and then immediately run the same report again, which seems to be the trick.
0
pdfenton
Asked:
pdfenton
  • 4
  • 2
  • 2
  • +3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Also some good (free) stuff here ...

http://www.lebans.com/Report.htm

including the Report Utilities ...

mx
0
 
harfangCommented:
Hello,

You are trying to find a workaround for something that shouldn't happen. When you run a report, it creates snapshots of the required information; it is always up to date.

I suspect your application has an issue with the sequence of events. For example, it might attempt to change data while the report is already opening.

I suggest we try to fix that instead of trying to find a workaround. Can you show us the relevant code run before and during the report generation?

(°v°)
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.

 
hnasrCommented:
Until you find a better solution: Issue command twice.

DoCmd.OpenReport "Report2", acViewNormal
DoCmd.OpenReport "Report2", acViewNormal
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I agree with harfang - there is something else going on, since your second report should always show the dame data as the first, assuming that you aren't using criteria or filters that could possibly be changed on the second one.
0
 
hnasrCommented:
"How do I print a report twice in VB?"
Just print it twice:)
0
 
pdfentonAuthor Commented:
I also agree with Harfang and will post the code shortly.  Been occupied with holidays, etc.  I want to be sure I explain all that's going on with this.  It's complex.
0
 
pdfentonAuthor Commented:
I hope I can explain this clearly.  The file ContractorsSchedule.txt is the report we're producing, minus the clients name and phone number.  Most of the data comes from various tables containing data on the client and his information, his project, etc.  Also from tables hold data referring to the various tasks for each part of the project.  Basically, every task has a name, start date, and end date.  Keyed to the client.

The Procedure CreateConstructionTable() builds a table containing the client information, project, task, start and end dates.  It then modifies that table to create entries for each day of the week.  That code is below and is called from from the code that runs when the user clicks on "View report".

User input consists of Name of the contractor, start and end dates of the period he wants to see.  It's basically the contractor's daily to-do list.  What to do and where to do it.

I hope this is clear.

Public Sub CreateContractorScheduleTable()
  
  Dim dbs As dao.Database, rst As dao.Recordset, tasks(11) As Variant
  Dim n As Integer, y As Integer, i As Integer
  
On Error GoTo HandleErr
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "contractorsschedule" 'run the query and make the table
  DoCmd.SetWarnings True
  
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("contractorsscheduletemp")  ' open the table
  rst.MoveLast
  rst.MoveFirst   'count the records
  
  rst.Edit
  For n = 1 To rst.RecordCount
    If IsNull(rst!End) Then         ' there is no end date
    rst.Edit
    
      Select Case rst![DayofWeek]
        Case 1  ' Monday
          rst![StartOfWeek] = rst![Start]
        Case 2  ' Tuesday
          rst![StartOfWeek] = rst![Start] - 1
        Case 3  ' Wednesday
          rst![StartOfWeek] = rst![Start] - 2
        Case 4  ' Thursday
          rst![StartOfWeek] = rst![Start] - 3
        Case 5  ' Friday
          rst![StartOfWeek] = rst![Start] - 4
        Case 6  ' Saturday
          rst![StartOfWeek] = rst![Start] - 5
        Case 7  ' Sunday
          rst![StartOfWeek] = rst![Start] - 6
          
      End Select
      rst.Update
    End If
    
    If Not IsNull(rst!End) Then     ' there is an end date

      If rst!End > rst!Start Then   ' it's past the start date
        i = rst!End - rst!Start     ' how many days is the task?
        For y = 1 To i

          With rst
            tasks(0) = !Contractor
            tasks(1) = !Start
            tasks(2) = !End
            tasks(3) = !Client
            tasks(4) = !HPhone
            tasks(5) = ![DayofWeek]
            'tasks(6) = !Improvement
            tasks(7) = !Task
            tasks(8) = !JobID
            tasks(9) = !ForeColor
            tasks(10) = !BackColor
            
            rst.Edit
            Select Case rst![DayofWeek]
              Case 1  ' Monday
                rst![StartOfWeek] = rst![Start]
              Case 2  ' Tuesday
                rst![StartOfWeek] = rst![Start] - 1
              Case 3  ' Wednesday
                rst![StartOfWeek] = rst![Start] - 2
              Case 4  ' Thursday
                rst![StartOfWeek] = rst![Start] - 3
              Case 5  ' Friday
                rst![StartOfWeek] = rst![Start] - 4
              Case 6  ' Saturday
                rst![StartOfWeek] = rst![Start] - 5
              Case 7  ' Sunday
                rst![StartOfWeek] = rst![Start] - 6
                
              End Select
              
            rst.Update
            
            rst.AddNew
            rst!Contractor = tasks(0)
            rst!Start = tasks(1) + y
            rst!Client = tasks(3)
            rst!HPhone = tasks(4)
            rst![DayofWeek] = Weekday(rst!Start, vbMonday)
            'rst!Improvement = tasks(6)
            rst!Task = tasks(7)
            rst!JobID = tasks(8)
            rst!ForeColor = tasks(9)
            rst!BackColor = tasks(10)
            
            Select Case rst![DayofWeek]
              Case 1  ' Monday
                rst![StartOfWeek] = rst![Start]
              Case 2  ' Tuesday
                rst![StartOfWeek] = rst![Start] - 1
              Case 3  ' Wednesday
                rst![StartOfWeek] = rst![Start] - 2
              Case 4  ' Thursday
                rst![StartOfWeek] = rst![Start] - 3
              Case 5  ' Friday
                rst![StartOfWeek] = rst![Start] - 4
              Case 6  ' Saturday
                rst![StartOfWeek] = rst![Start] - 5
              Case 7  ' Sunday
                rst![StartOfWeek] = rst![Start] - 6
            End Select
            
            rst.Update
          End With
        Next y
      End If
    End If
    rst.MoveNext
  Next n

  rst.MoveLast
  rst.MoveFirst
  For n = 1 To rst.RecordCount    ' delete the Sunday records
    If rst![DayofWeek] = 7 Then
      rst.Edit
      rst.Delete
    End If
    rst.MoveNext
  Next n
  rst.Close
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("ContractorsScheduleTemp", dbOpenDynaset)
  rst.Sort = "Start"
  rst.Close

  Set rst = Nothing
  Set dbs = Nothing
  

ExitHere:
  Exit Sub

' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 11-19-2003 17:20:05 'ErrorHandler:$$D=11-19-2003  'ErrorHandler:$$T=17:20:05
HandleErr:
  Select Case Err.Number
    Case 3265 ' Item not found in this collection
'      Resume Next
    Case 3021  ' no records
      Exit Sub
    Case Else
      MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "User.CreateContractorScheduleTable" 'ErrorHandler:$$N=User.CreateContractorScheduleTable
  End Select
' End Error handling block.
End Sub

*** After the above runs, the below continues***

' now delete the records that are older than his start date
  ' or later than his end date

  DoCmd.SetWarnings False
  DoCmd.OpenQuery "qDeleteContractorsReportRecords"
  DoCmd.SetWarnings True

  Dim qdf As dao.QueryDef, rst As dao.Recordset
  Dim db As dao.Database
  

' below was suggested by Experts Exchange.
  
  Dim qdfCurr As dao.QueryDef
  Dim prmCurr As dao.Parameter
  Set db = CurrentDb
  Set qdfCurr = CurrentDb.QueryDefs("qDeleteContractorsReportRecords")
  For Each prmCurr In qdfCurr.Parameters
    prmCurr.Value = Eval(prmCurr.Name)
  Next prmCurr
  qdfCurr.Execute dbFailOnError
  
  
  Set qdfCurr = Nothing
  Set db = Nothing
  Set prmCurr = Nothing
  Set rst = Nothing
  
  ' from here is my code
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("ContractorsScheduleTemp")
    rst.MoveLast
    rst.MoveFirst
    
  DoCmd.OpenReport "contractorsschedule", acViewPreview

Open in new window

0
 
pdfentonAuthor Commented:
I'm not sure the report example uploaded so here it is again.
contractorsSchedule.txt
0
 
pdfentonAuthor Commented:
And just to restate the problem... 9 times out of 10, the above produces a report with data outside the user specified date range.  If, however, I immediately go to the database window, "reports", and run the report from there, the report prints correctly.
0
 
krisscoCommented:
That sounds strange

What are the data type(s) for your date columns created by the "contractorsschedule" query?

As for printing twice, and ignoring the first run of the report, this can be accomplished by suppling a bogus where clause to your report.

Your above VBA could end with:
 DoCmd.OpenReport "contractorsschedule", acViewPreview, , "'ignore'='ignore'"
 DoCmd.OpenReport "contractorsschedule", acViewPreview

In your report's module, place the following code:

Private Sub Report_Page()
If Me.Filter = "'ignore' = 'ignore'" Then docmd.Close acReport, me.Name
End Sub

This way, you trick the report into closing itself when you supply a certain breed of "true" criteria ('ignore'='ignore' will always evaluate to true). The first time it opens (with criteria), it closes itself.

-Kris
0
 
krisscoCommented:
Now that I think about it, I believe that a report's filter property wraps itself in parentheses, so you may want to use:

me.filter like "*'ignore. . .*"

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now