Back Table gives empty Report with VBA. why?

S

Sergio Reis

I've a long time problem that I can't solve



1. I´ve got a back end table where the records are saved in VBA like this::





Public Function teste()



Dim MyTable As DAO.Recordset, MyDatabase As DAO.Database

Set MyDatabase = OpenDatabase("\\Codrvr05\qual\Processo\test.mdb")

Set MyTable = MyDatabase.OpenRecordset("Process")

..

MyTable.Index = "primaryKey"

...

MyTable.AddNew

MyTable![Record1]=forms![form1]![a]

MyTable.update

MyTable.Close

MyDatabase.Close



DoCmd.OpenReport "Final_Report", acViewPreview



Exit function





The report is in the front aplication based on the Linked MyTable.



THE PROBLEM IS that most of the times the report goes empty because, I
think, it opens before the information is "truly" saved and so available. To
get around this problem I sometimes put a MsgBox after the MyDatabase.Close
and before the DoCmd.OpenReport ( in order to get same delay, giving some
time so that the report gets the information)



Is there any way of assuring the report information (without using the
HasNodata Report Propertie), delaying the OpenReport action until the
information is realy available?
 
A

Allen Browne

This could be a timing issue. You created a new connection to the back end
(OpenDatabase), and operated on that connection. If the front end already
has a connection (cached), it may not see the new record immediately.

You could test that idea by introducing a delay, or forcing a refresh. But
it surely would be more efficient to perform the operation using the
connection you already have.

Presumably you have a linked table, and the report is based on that? If so,
insert the new record into the linked table instead of opening the backend.
You can do that with:
Set MyTable = dbEngine(0)(0).OpenRecordset("Process")

Or it might be easier to execute an append query statement:
strSql = "INSERT INTO Process (...
dbEngine(0)(0).Execute strSql, dbFailOnError

Either way, the current connection would probably be aware of the change,
and so feed the report correctly.
 
Top