Why does my report keep closing?

[sk1hotpepr]

I have a stored procedure that provides input params to an access report. for some reason, the report will close as soon as it opens on another user station. I don't know if it's a sql server problem - user permissions, an access problem (pretty sure it is). Can anyone help? this is my code in the access form.

...
Set rst = cmd.Execute
DoCmd.OpenReport "qryProjCritreport", acViewPreview
DoCmd.Close acForm, "ProjInfoQueryForm"
rst.Close
Set rst = Nothing
Set cmd = Nothing

Exit Sub

cmdOK_Click_err:
'Error handler
Select Case Err.Number
Case 2501 'No Records Found'
Resume Next
Case Else
MsgBox "An unexpected error has occurred." & _
    vbCrLf & "Please note the following details:" & _
    vbCrLf & "Error Number: " & Err.Number & _
    vbCrLf & "Description: " & Err.Description, vbCritical, "Error"
End Select
Resume cmdOK_Click_err
End Sub

This code is in the NoDate event of the report:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrorHandler
MsgBox "There are No Records", vbOKOnly
Cancel = True
ExitReport:
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume ExitReport
End Sub

The user permissions for the SQL database are Public, with EXECUTE permissions and are set up through Connectivity Only option of SQL server.

Any help much appreciated!

 

[danvlas]

Well, given that the report actually opens, I would believe you are trying to open the adp file by more users at a time.
Adp is not an mdb and I think you can open it only once.
Are all users opening their own copy of the adp or they open it from a single network location.

Just a thought, as I haven't seen this behaviour so far...

If it didn't open at all, I would think there was some wrong parameters passed to the sp, or a syntax error in filter, or something like this.


HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html