Error when executing OnOpen report

[ZikO]

Hi Guys,

I am using ACCESS 2010, win 7 Home Premium 64bit

I have a really a difficult to handle error when executing my report. I put a code into OnOpen section but ACCESS states:

The expression OnOpen you entered as the event property produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control.

Additionally it says in description:

This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs.

I am genuinely confused by that. I have also provided the code in the case if I am missing something I don't know yet. In a debug window, I have executed the code line by line and it worked. Whenever I try to open the report, the error occurs. Should I be aware of something when I write code for reports?

Thanks for all help.

Below, the code in my report "module":

Private Sub Report_Open(Cancel As Integer)
    Call CreateTempTable
End Sub

Private Sub CreateTempTable()
    On Error GoTo ErrorHandler
    Dim strTable As String
    Dim strScTable As String
    Dim strSQL As String
    Dim strSQLDelete As String
    
    strTable = "tbl_tempRoznica"
    strScTable = "qr_roznica"
    DoCmd.DeleteObject acTable, strTable
    
    ' Updating table
    strSQL = "SELECT " & strScTable & ".* INTO " & strTable & " FROM " & strScTable & ";"
    CurrentDb.Execute strSQL
    RefreshDatabaseWindow
    Exit Sub
    
ErrorHandler:
    If Err.Number = 7874 Then
        Resume Next ' Try to delete non-existing table, so resume next
    End If
End Sub

 

[spikepl]

This type of error can sometimes be derived from something entirely different.

1. Compile your code (CTRL G->Debug->Compile) and see what it says -the error might be somewhere else.
2. Backup your code and start deleting functions /subroutines until the error goes away -then you know where it is. While doing all this comment out all On Error statements

Edit: I think you can also get this error if you put some garbage in the event handler property Form Property Sheet-> Event tab-> check that what is in this column makes sense

 

[Poppa Smurf]

1. Create your sub CreateTempTable() as CreateTempTable2() module then run the module and check if there is any error. You could use F5 or F8 to step through the module.

2. Also in your Private Sub CreateTempTable() add a message to check if you actaully enter this area.

3. You do not need this code DoCmd.DeleteObject acTable, strTable as the Select statement will delete the existing table and make a new one.

 

[JHB]

Could it be because the record source for the report is the table you try to delete?
If so set the record source for the report to "" before you delete the table and then after the table is created, set the record source for the report to the table again.
Else explain why you are deleting tables when the report open, can't it be done before?