Report wont open in preview mode

[buratti]

Well the title of this post is not exactly true, but only when certain circumstances are met. You see, my database is configured where the Access window itself is set to a pretty small size. I have 1 main navigational form that is roughly 1.5" tall by about 6" wide. I hide the navigation pane, maximize the navigation form, and then resize the Access window to the same dimensions mentioned before (all within the startup code). I learned that when resizing the Access window smaller than a certain size (as the size i am using), Access will "auto hide" the ribbon. This gives my database the appearance of a standalone application. Then all my other forms, reports and anything else that will even be opened are set to popup.
Now the problem I am having is that when the Access window is this small, I cannot open a report in preview mode. Report mode opens just fine, but as soon as I switch to preview, it closes. If i resize the Access window to something larger, it works just fine. Its only when the window is small. Also I should note that it is only this one report that is having this problem. Any other report that I try to open in preview mode, open just fine regardless of the Access window size. The obvious solutions that I have tried are listed below, but have not worked. I have tried using docmd.maxamize, move and movesize in the onOpen event, but did not work.
Any suggestions on how to fix this. I find it weird that this one report is singled out to having this problem. I matched the properties of this report to others that are working and they are all pretty much the same. The only thing I can think is that this report is very unique and even maybe a little untraditional in the design and layout, but it does open when the Access window is a larger size.

 

[vbaInet]

Do you have any code in the Load, Open, Activate and No Data events of the report? If you do, can we see them.

If it's only happening on that one report then I would advise that you recreate the report.

 

[buratti]

Sorry for the delay... Yes I do have code in the on load of the report that is displayed below. Like I stated before, this report is untraditional in design, and before I get an earfull about how its wrong to design/code it the way I did, let me explain why I did what I did a little first...

First off, the report displays a summary of all employees work, along with their hours for a given week. The appearance I was trying to acheive is basically a chart like format (not to be confused with a pie or graph chart though). Basically, I needed all employees listed across the top and the work/hours for each day of the week listed under each employee. The only way I could figure out to acheive this design (even with help through these forums) was to have an unbound main report and several copies of the same sub-report for each "column" under each employee. The sub-report is bound to the table which holds the hours and other data for each day/each employee. Then the on load event changes the recordsource of each sub-report to reflect which employee column it is under. Along with code that sets the employees to display in the current report, and work week to filter for. I have added a more discriptive explination with each line of code below to further understand what is going on.

Now I understand that a property like recordsource cannot be changed in the onload even of a report when viewing in preview mode, so a google search helped me solve this by in the control that opens this report, I code it where I first open it in "report" view, where the onload code can run without error, then the next line of code is to open it in "preview" mode, and it opens just fine (well that is if the Acces window is not too small).

Here is all the code:

Private Sub Report_Load()
Dim Weekstarting As Date
 
'the 7 day work week is sent to the report in openargs 
CurrentWeek = Nz(Me.OpenArgs, Date)
 
'The following function just takes any date, in this case the date sent in openargs, and returns the first day of the fiscal work week.  In our case the Thursday before the date being sent
Weekstarting = WeekStartDate(CurrentWeek)
 
'here is the sub that changes the recordsource of each sub-report
SetformState (Weekstarting)
 
End Sub
 
Private Sub UpdateSubforms(columnNumber As Integer, WorkWeek As Date)
Dim Header As Integer
Header = columnumber
'Cannot recall what the header variable is used for.  Probably just an early design idea that I gave up on, and just never deleted this code.
If columnNumber < 8 Then
    Header = columnNumber
Else
    Header = columnNumber - 7
End If
 
'Here is where i actually change the recordsource of the subreport...
If Int(Me("employeeID" & columnNumber).Caption) <> 0 Then
    Me("Employee" & columnNumber & "Subform").Report.recordsource = "SELECT EmployeeLog.*, EmployeeLog.EmployeeID AS IDtxtFormat FROM EmployeeLog" _
            & " WHERE EmployeeID = " & Int(Me("employeeID" & columnNumber).Caption) & " And " _
            & "Work_date >= #" & WorkWeek & "# And work_date < #" & WorkWeek + 7 & "# " _
            & "Order By Work_Date"
Else
    Me("Employee" & columnNumber & "Subform").Report.recordsource = "SELECT EmployeeLog.*, EmployeeLog.EmployeeID AS IDtxtFormat FROM EmployeeLog" _
            & " WHERE EmployeeID = " & Int(Me("employeeID" & columnNumber).Caption)
End If
 
End Sub
 
Sub SetformState(Weekstarting As Date)
 
'Set the title to the current work week
Me.lblDay.Caption = Format(Weekstarting, "dddd")
Me.lblDate.Caption = Format(Weekstarting, "M/DD/YYYY")
Me.lblDate2.Caption = Format(Weekstarting, "M/DD/YYYY")
 
'set the values/names of the employee headings to what they were when this work week was data was created
SetCombovalues Weekstarting
 
'Now change the recordsource of each sub-report to reflect the employee listed above it (entered in the last line of code)
Dim i As Integer
i = 1
'Like stated before, there will never be more than 14 employees in the report, and there are 14 copies of the same sub-report, so loop for each sub-report
Do While i < 15
    UpdateSubforms i, Weekstarting
    i = i + 1
Loop
 
End Sub
 
Sub SetCombovalues(Weekstarting As Date, Optional Page As String)
Dim rscomboOrder As Recordset
Dim i, j As Integer
Dim ID As Integer
Dim pay As Integer
Dim strSQL As String
 
'Open a recordset containing the employees to fill the report with
Set rscomboOrder = CurrentDb.OpenRecordset("Select * from Payroll Where Workweek = #" & Weekstarting & "# Order by sort")
rscomboOrder.MoveFirst
      'There will never be more than 14 employees so loop through the recordset 14 times  to set 1 employee to each column
      For j = 1 To 14
            If Not rscomboOrder.EOF Then
                Dim EmployeeName As String
                EmployeeName = DLookup("Fname", "[Employees]", "id =" & Nz(rscomboOrder![EmployeeID]))
                Me("employeeName" & j).Caption = Nz(EmployeeName, "")
                Me("employeeID" & j).Caption = Nz(rscomboOrder![EmployeeID], "")
 
                rscomboOrder.MoveNext
            Else
                Me("employeeName" & j).Caption = ""
                Me("employeeID" & j).Caption = 0
            End If
    Next
End Sub

 

[vbaInet]

Don't worry burrati, I know that you know your stuff so I won't give you an earful of bad design "rants".

If you comment out the entire code does it open up ok?

Have you thought about using a Crosstab query?

 

[buratti]

Commented the code out and still wont open (well, when the Access window is small). To be honest, I have never used a crosstab query before, and not sure what they are or how to use them. I'm not saying I'm a pro at Access yet, but I have come a long way since I started and a little disappointed at myself for not knowing them (especially when you just gave me the compliment about knowing my stuff). I am assuming you are asking regarding a revisit of the design of the report. Am I correct?
But back to the issue at hand... I did find a half-a*s workaround... It's not prefered, but I can live with it. If I send the report directly to the printer (acviewNormal) and not view it in preview mode beforehand it does work/print correctly. if you feel motivated and want to find the actual cause/solution to this weird problem, I will give all the info you need and continue to troubleshoot it myself also, but if not then its no problem.
Thanks

 

[vbaInet]

Don't be hard on yourself. You are trying to workaround one of the quirks of Access and it's not the easiest of tasks.

Upload a test db and I will see what I can do.