Cannot navigate pages in print preview

[RMI]

I have a function in VBA that:
1 - creates a temp db
2 - creates a table in temp db
3 - copies data from 2 different tables into that temp table
4 - links the temp table into the currentdb
5 - opens a report on that table (and others) in print preview
6 - loops until the report is closed
7 - removes the link
8 - closes the other db
9 - deletes the other db


exits

Now, when I have the report in print preview, I cannot use the page navigation buttons at the bottom to change pages. I can use CTRL-P to print it (usually to a PDF) so that I know it is formatted correctly.

I am confused and a Google search didn't seem to come up with anything I could use.

Thanks for any help!
RMI

[hertfordkc]

I have a function in VBA that:

4 - links the temp table into the currentdb
5 - opens a report on that table (and others) in print preview
6 - loops until the report is closed
7 - removes the link
exits

Now, when I have the report in print preview, I cannot use the page navigation buttons at the bottom to change pages. I can use CTRL-P to print it (usually to a PDF) so that I know it is formatted correctly.
RMI

Why not exit the function and have a VBA onclose function in the report that cleans everything up?
Just a guess.

[RMI]

Why not exit the function and have a VBA onclose function in the report that cleans everything up?
Just a guess.

I will rework the process and let you know how it goes.

Thanks for pointing out the obvious...

Edit:

Option Compare Database
Option Explicit
Dim oDB As DAO.Database
Dim sTable As String
 
Private Sub Report_Open(Cancel As Integer)
    Dim sFN As String
    Dim tdfTable As TableDef
 
    ' Set Variables
    sTable = "ContactLog"
 
    ' Create a temp database
    sFN = CurrentProject.Path & "\Temp-" & Format(Now(), "yyyymmddhhNnss") & ".accdb"
    modMain.CreateDatabaseDAO (sFN)
 
    ' Open the database
    Set oDB = OpenDatabase(sFN)
 
    ' Create the table
    oDB.Execute "CREATE TABLE " & sTable _
        & " (lname CHAR, fname CHAR, cdate DATETIME, ctype CHAR, reason MEMO, outcome MEMO);"
 
    ' Link the table to this database
    Set tdfTable = CurrentDb.CreateTableDef(sTable)
    tdfTable.Connect = ";Database=" & sFN
    tdfTable.SourceTableName = "ContactLog"
    CurrentDb.TableDefs.Append tdfTable
    Set tdfTable = Nothing
 
    ' Insert data from contacts
    CurrentDb.Execute "INSERT INTO " & sTable & " ( lname, fname, cdate, ctype, reason, outcome ) SELECT tblContacts.lname, tblContacts.fname, tblContacts.cdate, tblContacts.ctype, tblContacts.reason, tblContacts.outcome FROM tblContacts INNER JOIN tblStudents ON (tblContacts.fname = tblStudents.fname) AND (tblContacts.lname = tblStudents.lname) WHERE tblStudents.Active = True"
 
    ' Insert data from weekly reports
    CurrentDb.Execute "INSERT INTO " & sTable & " ( lname, fname, cdate, ctype, reason ) SELECT tblDaily.lname, tblDaily.fname, tblDaily.tdate AS cdate, 'W' AS ctype, tblDaily.comments AS reason FROM tblDaily WHERE tblDaily.comments Is Not Null"
 
    ' Close the temp database
    Set oDB = Nothing
End Sub
 
Private Sub Report_Close()
    ' Remove the link to the temp table
    CurrentDb.TableDefs.Delete sTable
 
    ' delete the database
    Kill CurrentProject.Path & "\Temp-*.accdb"
End Sub

So, everything worked fine until it tried to delete the temp db...

Apparently there is a link to the temp db that set odb=nothing and clearing the table link doesn't fix.

I am lost. What am I missing.

Thanks for your help so far!!!

[RMI]

Can anyone help?

[Access_Blaster]

You could try and set your record source to ""

On a form it might look like this.

Private Sub Form_Close()
Dim strSQL As String
Me.RecordSource = ""
strSQL = "Drop Table sTable"
CurrentDb.Execute strSQL, dbfailonerror
Kill CurrentProject.Path & "\Temp-*.accdb"
End Sub