Error: Microsoft Access has encountered a problem and needs to close.

[aman]

Hi guys

When I write the following code then it prints off the documents but after that it throws the error message

"Microsoft Access has encountered a problem and needs to close.We are sorry for the inconvenience."

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
        
    stDocName2 = "FrontSheet Report"
    stDocName1 = "PickList Report2"
    End If
 
  topLabelOnReport = cmbDept.Value & " Dept ONLY"
   
    DoCmd.OpenReport stDocName1, acViewNormal, , , acDialog, topLabelOnReport
    DoCmd.OpenReport stDocName2, acViewNormal, , , acDialog, topLabelOnReport
  
    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 hope anyone can help me to solve this problem.

Thanks

 

[aman]

Please can anyone help me to solve this error message. What do I need to do to get rid of it. I have searched about this error message a lot from internet but could't figure out my problem.

Any help would be much appreciated.

Thanks

 

[JeffBarker]

Hi there, I can see a number of problems with this code, although none of them may be the key to your problem - they're just things that jump out at me as being wrong!

1) In your first "If" statement:

If cmbDept.Value <> "" Then

Else
MsgBox "Select a Dept name.", vbCritical, "Dept Name is not selected"
cmbDept.SetFocus
Exit Sub
End If

There's no first option - so if cmbdept.value <> "" Then do this Else. Is that something classified you've removed from the code for the purpose of the forum?

2) There are a number of random "End If" statements dotted around, with no beginning "If"'s:

Here:

stDocName2 = "FrontSheet Report"
stDocName1 = "PickList Report2"
[B]End If[/B]

And here:

DoCmd.SetWarnings False
DoCmd.RunSQL Sql
DoCmd.SetWarnings True
[B]End If[/B]

3) In the following statement:

DoCmd.SetWarnings False
[B]DoCmd.RunSQL Sql[/B]
DoCmd.SetWarnings True

You're telling Access to run Sql - but I don't see where you actually set Sql as something??

As I said, I'm no expert so these problems may or may not be the key to your problem, but they just strike me as a bit odd.

On another note, have you tried compacting and repairing your DB? That may help get rid of this error.

 

[aman]

Hi JeffBarker

Below is the actual code I am using :

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("DRS 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 hope you can figure out why the error message comes after the documents get printed off.

Thanks

 

[JeffBarker]

Hm, as I said I'm no expert so I can't see anything there that would cause any problems - but then my knowledge is fairly limited, so perhaps one of the more experienced forum members would be able to help you on the code front.

Have you tried putting a stop on the code and the stepping through it to see where the error occurs?