The code works fine for 2003 but on 2010 it doesn't

[aman]

The following print function works fine for 2003 but when run on windows 2010 then it doesn't work,

Private Sub btnPrintRequests_Click()
    On Error GoTo Err_btnPrintRequests_Click
    
    If cmbDept.Value <> "" Then
    
    Else
        MsgBox "Select a Dept name.", vbCritical, "Dept Name is not selected"
        cmbDept.SetFocus
        Exit Sub
    
    End If
      
    If MsgBox("The System will now attempt to print two Set of Reports on your default printer." & vbCrLf & vbCrLf & _
           "If you want to change your Default Printer or Its settings then do so; and then click OK, " & vbCrLf & _
           "Click Cancel, If you do not want to Print this report Now. " & _
           "", vbInformation + vbOKCancel, "Change Your Printer Settings before clicking OK [Or Cancel]") = vbCancel Then
          
         Exit Sub
    End If
    
    Dim stDocName1 As String
    Dim stDocName2 As String
    Dim mysCriteria As String
    Dim topLabelOnReport As String
        
    If cmbDept = "Plan Manager" Or cmbDept = "MH(Marlborough House)" Or cmbDept = "ADS(Aztec West)" Or cmbDept = "FNZ" Then
    stDocName2 = "ElevateFrontSheet"
    stDocName1 = "ElevatePickList"
    Else
    stDocName2 = "FrontSheet Report"
    stDocName1 = "PickList Report2"
    End If
    If cmbDept.Value = "ADS 3 WEEKS PURPLE" Or cmbDept.Value = "ADS 3 Weeks" Then
    mysCriteria = "[H_PrintStatus]='No' AND [RequestedForDept]= '" & cmbDept.Value & "' AND datevalue(RequestStartDateTime)= #" & Format(Date, "mm/dd/yyyy") & "#"
      
    Else
    mysCriteria = "[H_PrintStatus]='No' AND [RequestedForDept]= '" & cmbDept.Value & "'"
    End If
    topLabelOnReport = cmbDept.Value & " Dept ONLY"
   
    DoCmd.OpenReport stDocName1, acViewNormal, , mysCriteria, acDialog, topLabelOnReport
    DoCmd.OpenReport stDocName2, acViewNormal, , mysCriteria, acDialog, topLabelOnReport
   
   If cmbDept.Value = "ADS 3 WEEKS PURPLE" Or cmbDept.Value = "ADS 3 Weeks" Then
   Sql = "update tbldocumentrequest set h_Printstatus='Yes' where RequestedForDept='" & cmbDept.Value & "' and datevalue(RequestStartDateTime)= #" & Format(Date, "mm/dd/yyyy") & "#"
    DoCmd.SetWarnings False
    DoCmd.RunSQL Sql
    DoCmd.SetWarnings True
    End If
Exit_btnPrintRequests_Click:
    Exit Sub
Err_btnPrintRequests_Click:
    If Err.Number = 2501 Then
    Else
        MsgBox Err.Description
    End If
    
    Resume Exit_btnPrintRequests_Click
    
End Sub

I tried to test line by line and the following lines of code works for 2010 but i can't set ant criteria for report and can't open the report to print it.

Private Sub btnPrintRequests_Click()
    On Error GoTo Err_btnPrintRequests_Click
    
    If cmbDept.Value <> "" Then
    
    Else
        MsgBox "Select a Dept name.", vbCritical, "Dept Name is not selected"
        cmbDept.SetFocus
        Exit Sub
    
    End If
      
    If MsgBox("The System will now attempt to print two Set of Reports on your default printer." & vbCrLf & vbCrLf & _
           "If you want to change your Default Printer or Its settings then do so; and then click OK, " & vbCrLf & _
           "Click Cancel, If you do not want to Print this report Now. " & _
           "", vbInformation + vbOKCancel, "Change Your Printer Settings before clicking OK [Or Cancel]") = vbCancel Then
          
         Exit Sub
    End If
    
    Dim stDocName1 As String
    Dim stDocName2 As String
    Dim mysCriteria As String
    Dim topLabelOnReport As String
        
    If cmbDept = "Plan Manager" Or cmbDept = "MH(Marlborough House)" Or cmbDept = "ADS(Aztec West)" Or cmbDept = "FNZ" Then
    stDocName2 = "ElevateFrontSheet"
    stDocName1 = "ElevatePickList"
      MsgBox "PM"
    Else
    stDocName2 = "FrontSheet Report"
    stDocName1 = "PickList Report2"
      MsgBox "ADS"
    End If
    
      
    
Exit_btnPrintRequests_Click:
    Exit Sub
Err_btnPrintRequests_Click:
    If Err.Number = 2501 Then
    Else
        MsgBox Err.Description
    End If
    
    Resume Exit_btnPrintRequests_Click
    
End Sub

I hope anyone can help me out in this.

Thanks a lot

 

[speakers_86]

So it breaks at the Docmd.Openreport?

 

[aman]

Yes it does.

 

[speakers_86]

If you remove the Where clause, will the report open?

Try to eliminate the possibility that there is something wrong with the report itself. Use some other fashion to open the report and see if you get expected results.

Also, just curious, what is the extension of your db? Is it accdb or mdb?

 

[aman]

I have come to the conclusion that when I write the following code then it works:

If cmbDept = "Plan Manager" Or cmbDept = "MH(Marlborough House)" Or cmbDept = "ADS(Aztec West)" Or cmbDept = "FNZ" Then
    stDocName2 = "ElevateFrontSheet"
    stDocName1 = "ElevatePickList"
    DoCmd.OpenReport stDocName1, acViewNormal, , "[H_PrintStatus]='No' AND [RequestedForDept]= '" & cmbDept.Value & "'", acDialog, topLabelOnReport
    DoCmd.OpenReport stDocName2, acViewNormal, , "[H_PrintStatus]='No' AND [RequestedForDept]= '" & cmbDept.Value & "'", acDialog, topLabelOnReport
    MsgBox "PM"
    end if

But when I add Datevalue criteria in this then it doesn't as follows;

 stDocName2 = "FrontSheet Report"
    stDocName1 = "PickList Report2"
    DoCmd.OpenReport stDocName1, acViewNormal, , "[H_PrintStatus]='No' AND [RequestedForDept]= '" & cmbDept.Value & "' AND datevalue(RequestStartDateTime)= #" & Format(Date, "mm/dd/yyyy") & "#", acDialog, topLabelOnReport
    DoCmd.OpenReport stDocName2, acViewNormal, , "[H_PrintStatus]='No' AND [RequestedForDept]= '" & cmbDept.Value & "' AND datevalue(RequestStartDateTime)= #" & Format(Date, "mm/dd/yyyy") & "#", acDialog, topLabelOnReport

So I need to change the Datevalue function so that in the criteria its is there but in different format.

Thanks

 

[aman]

Can anyone please figure out my problem?

Thanks

 

[aman]

Now I tested the following thing without Datevalue function but it doesn't work. That means it doesn't accept Date condition:

stDocName2 = "FrontSheet Report"
    stDocName1 = "PickList Report2"
    DoCmd.OpenReport stDocName1, acViewNormal, , "[H_PrintStatus]='No' AND [RequestedForDept]= '" & cmbDept.Value & "' and RequestDate=#"& format(Date,"mm/dd/yyyy") &"#", acDialog, topLabelOnReport"
    DoCmd.OpenReport stDocName2, acViewNormal, , "[H_PrintStatus]='No' AND [RequestedForDept]= '" & cmbDept.Value & "' and RequestDate=#"& format(Date,"mm/dd/yyyy") &"#, acDialog, topLabelOnReport"

Any idea why?

 

[RailwayKen]

?? Missing reference libraries?

(I once had a perfectly good statement using the Left() function error because a referenced object library-- one with nothing to do with "Left()"-- was missing on a particular computer.)