2501 - Open Report Action Canceled

[Melagan]

....but only when more than one user is logged into the database.

Access 2000 formatted tables.
Code from a form to generate SQL then open a specific report; the report takes the SQL and uses it as recordsource. Here first is the code behind the form, frmActive

Sub cmdOK_Click()
Dim strDocName As String
Dim strSQL As String
gstrActiveSQL = ""            'Global variable in modPublic
strDocName = "rptActiveFiles"

If Me.optLennar.Value = -1 Then
  gstrActiveSQL = "SELECT e.[Escrow Number], e.[Escrow Status], u.First_Last, e.[Opening Date], e.PDC,"
  
  gstrActiveSQL = gstrActiveSQL + " e.[PDC Broker], u.[Last Name]"
  
  gstrActiveSQL = gstrActiveSQL + " FROM tblUsers u INNER JOIN Escrows e on u.Initials = e.[EO Initials]"
  
  gstrActiveSQL = gstrActiveSQL + " WHERE u.Title='Escrow Officer' AND (u.Function='Escrow'"
  
  gstrActiveSQL = gstrActiveSQL + " Or u.Function='Lennar') AND e.[Recording Status]='In Progress'"
  
  gstrActiveSQL = gstrActiveSQL + " AND e.[Escrow Status] Not Like '*Cancelled*'"

Else
  
  gstrActiveSQL = "SELECT e.[Escrow Number], e.[Escrow Status], u.First_Last, e.[Opening Date], e.PDC,"
  
  gstrActiveSQL = gstrActiveSQL + " e.[PDC Broker], u.[Last Name]"
  
  gstrActiveSQL = gstrActiveSQL + " FROM tblUsers u INNER JOIN Escrows e on u.Initials = e.[EO Initials]"
  
  gstrActiveSQL = gstrActiveSQL + " WHERE u.Title='Escrow Officer' AND (u.Function='Escrow'"
  
  gstrActiveSQL = gstrActiveSQL + " Or u.Function='Lennar') AND e.[Recording Status]='In Progress'"
  
  gstrActiveSQL = gstrActiveSQL + " AND e.[Escrow Status] Not Like '*Cancelled*'"
  
  gstrActiveSQL = gstrActiveSQL + " AND IIF([Escrow Status] = 'New Home', -1, 0) = 0"
End If

DoCmd.OpenReport strDocName, acViewPreview
   DoCmd.SelectObject acReport, strDocName, False
    DoCmd.Maximize

End Sub

And now the code behind the report, rptActiveFiles

Option Compare Database
Option Explicit
Private m_RowCount As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
m_RowCount = m_RowCount + 1
    If m_RowCount / 2 = CLng(m_RowCount / 2) Then
            Me.Detail.BackColor = 16638405 'Change value to the color you desire
        Else
            Me.Detail.BackColor = 14013909 'Change value to the color you desire
    End If
    DoCmd.Close acForm, "frmActive"
End Sub

Private Sub Report_Close()
DoCmd.SelectObject acForm, "frmSwitchBoard", False
DoCmd.Restore
End Sub

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ErrorHandler

    MsgBox "There are currently no records.", vbOKOnly, "No Records"
    Cancel = True
    DoCmd.Close acForm, "frmActive"

ExitHandler:
    Exit Sub
    
ErrorHandler:
    MsgBox Err.Description
    Resume ExitHandler

End Sub

Private Sub Report_Open(Cancel As Integer)
    Me.RecordSource = gstrActiveSQL
End Sub

So the question - why, in my development copy, would this procedure work perfectly, but in the live copy where multiple users are logged in, I get the 2501 runtime error? When I debug, the docmd.openreport line is highlighted on frmActive. Any ideas? Thank you in advance.



~Melagan
______
"It's never too late to become what you might have been.

 

[genomon]

I explicitly handle this error in automation; we discovered that even if the OnNoData event cancel = true is set, some of our printers (HPs on the network) would still lock the application on this error.

Outside of a dog, a book is man's best friend. Inside of a dog it's too dark to read.

 

[Melagan]

Unfortunately, having commented out the Sub Report_NoData(Cancel as Integer) subroutein, I'm still getting a 2501 runtime error.

This isn't a question of the report having data or not - 99% of the time, the SQL written above *will* generate a recordset with data.

Again, the only different between my development and production copy of the database is that the production copy obviously has multiple users logged in at the same time. Incidently, when I launch multiple copies / logins in my development copy, the same thing happens.

Any other ideas?

~Melagan
______
"It's never too late to become what you might have been.

 

[MaxEd]

My guess would be that multiple code changes of the same object is not possible unless they have a local front end that changes instead. Is your database separated between the data and the front end GUI?

-Laughter works miracles.

 

[PHV]

DoCmd.Close acForm, "frmActive"
is called for each row in the report's RecordSource ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Melagan]

Yes, the front-end and back-end are seperated, but all of the users are logging into the same copy OF the front-end. Perhaps this is the issue.

PHV - noted. Thank you for that; I've moved the docmd.Close method to the correct event =)


~Melagan
______
"It's never too late to become what you might have been.