I'm using the report's 'On load' event which runs a small bit of code that
calls a procedure that does most of the work.
Private Sub Report_Load()
MyModule.MyProc edure
Me.RecordSource = "MyJustCreatedT able"
End Sub
As you see the record source for the report will be a table created by the
sub procedure. The way I want this to work and the way I have written the
sub, is to delete the table first (if it exists) and then create the table
and populate with records as determined by the rest of the code. The
portion of code in the sub that deletes the table is as follows:
Dim chkForExistTbl As TableDef
For Each chkForExistTbl In dBase.TableDefs
If chkForExistTbl. Name = "MyJustCreatedT able" Then _
dBase.TableDefs .Delete "MyJustCreatedT able"
Next
When I open the report from the navigation pane, the report opens just fine
with the just created table and records. Everything A O.K. While the
report is open in 'Report View' and I try to go to 'Print PreView' it would
seem my code runs again and generates the following error:
Run Time Error '3211'
The database engine could not lock table 'MyJustCreatedT able' because it
is already in use by another person or process.
When I click Debug, it is stalled on the line that is trying to delete the
table:
dBase.TableDefs .Delete "MyJustCreatedT able"
I assume this happens because the report itself is the process that is
'using' the table i.e. the table is it's recordsource.
In the above 'On load' event I added the following as the very first line of
code:
Me.RecordSource = ""
in the hopes that when my code runs while going into print preview, it
wouldn't see the table as being 'in use' and would allow my code to delete
the table. Again, this code works fine when opening the report from the
navigation pane. Everything A O.K. But now when I try to go into print
preview I get the following error:
Run time error '2191'
You can't set the Record Source property in print preview or after
printing has started.
So it would seen that print preview insists on the report as having it's
recordsource property set right at the get go.
My next thought was to make the first bit of code in the event check to see
if the report was loaded or not. (I had hoped (fingers crossed) that the
report wouldn't report itself as being loaded right at the beginning of the
event when opened from the navigation pane.) This is where I used a
modified version of your example (thank you) below as the first line of
code in the event:
If CurrentProject. AllReports("MyR eport").IsLoade d is true then exit sub
My thinking was that if the report was indicated as being loaded, then I
wouldn't bother running any of the code since this would be the event
triggered by going into print preview. Of course this doesn't work as the
report is indicated as being loaded before my code runs so I can't
distinguish from opening the report from the navigation pane or going into
print preview.
In either case of whether I have the report's recordsource set or not, after
I clear the error dialog with 'End' I go into print preview just fine. So I
could handle the problem with an error handler and suppress the error but I
am trying to be concise about this and not generate an error in the first
place.
I'm teaching myself Access from books/this newsgroup and I may be making an
incorrect assumption. As I understand, a report needs to have a
recordsource from an existing table (or query, which is itself based on
existing tables). This is why I create the table in the first place. I also
delete it and recreate it every time the report is run since the underlying
data (which is coming from a query) may have changed. Perhaps this an
incorrect assumption and I can create a temporary 'virtual' table within my
code to use as a recordsource for the report so I don't have to delete
anything?
Thanks,
Gord
"lyle fairfield" <ly************ @gmail.comwrote in message
news:9c******** *************** ***********@b1g 2000hsg.googleg roups.com...
Did you tell us where the code exists?
In a standard module?
In the report's module? (if so is it event code? what event?)
In a class module?
Did you tell us what the code is? Exactly what is it?
How about posting this code and any related code such a procedure
which is called from the code?
What are the "error problems"?
In fact, code is not run once if you take too deep a breath, let alone
twice. It may be run twice if you have placed it in an event that
occurs twice, or more.
CurrentProject. AllReports("Sup plier Phone Book").IsLoaded is true if
the "Supplier Phone Book" report is open, false if it is not, error
2467 if there is no report named "Supplier Phone Book", (Access
2007) .
On Aug 2, 10:52 pm, "Gord" <x1gor...@telus .netwrote:
I'm using 2007 and am self teaching. The next reply from TerryBell would
indicate that the code is run if you take too deep a breath.
