Condition If Table Exists...

[Brian via AccessMonster.com]

I have a macro that deletes two tables (created by queries) when a form is
opened. The form's inputs then run queries that re-create those tables.
The problem is when the form is opened and closed, the tables are deleted.
Then when the form opens again the tables aren't there for it to delete and
the macro fails. I thought a conditional statement would fix this, but I
don't know how.

 

[Ken Snell [MVP]]

You can do this "test" via VBA code, but not directly via a macro. You could
call a VBA function from a macro as part of a condition expression.

But why not avoid the problem entirely by replacing the "make table" queries
with "append" queries, and change from deletion of the tables to running
"delete" queries to empty the tables of data.

 

[Brian via AccessMonster.com]

Thanks.

How would I go about making append and delete queries?

 

[Ken Snell [MVP]]

Create a standard select query in design view. Then click on "Query Type"
icon on toolbar and select the query type you want. You'll need to provide a
bit more info as the wizard asks.

 

[Steve Schapel]

Brian,

A agree with Ken's suggestion. However, for the record, you can use a
Condition in your macro with the DLookup() function. For example, a
Condition like this:
DLookup("[YourField]","YourTable")
.... will cause the macro action to not run if YourTable doesn't exist.

 

[Ken Snell [MVP]]

ahhhh... neat trick, Steve!

--

Ken Snell
<MS ACCESS MVP>

Brian,

A agree with Ken's suggestion. However, for the record, you can use a
Condition in your macro with the DLookup() function. For example, a
Condition like this:
DLookup("[YourField]","YourTable")
... will cause the macro action to not run if YourTable doesn't exist.

--
Steve Schapel, Microsoft Access MVP

I have a macro that deletes two tables (created by queries) when a form
is
opened. The form's inputs then run queries that re-create those tables.
The problem is when the form is opened and closed, the tables are
deleted.
Then when the form opens again the tables aren't there for it to delete
and
the macro fails. I thought a conditional statement would fix this, but I
don't know how.