Can you do this with a macro

R

Robert Gillard

I want to run a macro that (in plain English says) if tableA exists then
open formA, if it does not then open formB.
I thought this would be a conditional macro using Iif, but I do not know how
to say "if the table exists".
Can any body help with this please

Bob
 
K

Ken Snell [MVP]

You cannot do this just with a macro. You'd also need to use a VBA function
that identifies if the table exists, and then have the macro call that
function and test the result from it. Have you worked with VBA procedures
before?
 
R

Robert Gillard

Ken,
Regretablely I do not have any experience in this area. Is this a good place
to start ?

Bob
 
L

Larry Linson

Why don't you give some detail on what you have and what you are trying to
accomplish. Tables that may or may not be present are not indicative of
simple, straightforward application design. Perhaps someone can offer some
suggestions for a different approach.

Larry Linson
Microsoft Access MVP
 
K

Ken Snell [MVP]

Robert -

I have been tied up all day on numerous work projects and did not get a
chance to respond. I will attempt to post something tomorrow morning (my
time).
 
K

Ken Snell [MVP]

OK -

Before I jump into a VBA answer, let me ask a few questions about what
you're doing. In my experience, it's a bit unusual to need to know if a
certain table exists in order to decide which form to use. Typically, tables
should be relatively static entities, other than temporary tables that you
might create for the purpose of binding a report or form to it until the
form or report is closed, and then the table is deleted. In these cases, you
specifically create a table (needs to be done by a VBA procedure) and then
delete it (also by VBA), so your code/macro would know that the table
exists.

As you've posted that you're not familiar with VBA, I am a bit puzzled by
why a table might not exist in your database if you're not creating/deleting
them by using VBA.

It's very feasible to have your macro call a VBA function that would tell
you whether a table exists, and then, as I'd mentioned earlier, use that
value as part of your test in the macro's Condition expression.

But, there may be a different approach to take for what you want to do. Can
you tell us why a table might/might not exist? What causes the table to be
there or not be there? What are you doing with the form when you open it
(regardless of which table exists)?

Let's get a better picture of what you're wanting to accomplish, and then
we'll get the best solution for you.
--

Ken Snell
<MS ACCESS MVP>
 
R

Robert Gillard

Main Overview
I have a tableX, accessed via a form (from a main menu). The form acts as a
type of catalogue and people can mark an item (tick a box) if they wish it
to be sent to them. A button at the bottom of the form emails a report to
the dispatch desk.

More Detailed View
When they select the catalogue from the main menu, a copy of the tableX is
created. It is this duplicate table (call it tableY) they mark for the items
they need. As there are over 400 items to look through, people will often do
it in 2 or 3 time slots. At the bottom of the form there is also a button to
save the existing request so that they can carry on with it at a later date.
This saves tableY until next time.. the problem I have is getting it to
open. The intended idea was next time they opened the catalogue, Access
would check to see if there was a partial order in the form of table Y, if
so it would open it so they can carry on. If not it opens tableX which
immediately creates tableY and lets them create a fresh / new order.
Again the intention was, when the order is emailed through a report is
created and sent with the email, afresh table is created tableZ listing just
the ordered items (and in future new orders are appended to the table). Then
table Y is deleted.
As these tables are held on individual pc's (linked by a Network) it is
intended that tableX will be updated centrally once a week. So I did not
want to write any data to it, in case it was was lost on the weekly update.

Sorry it was so long, I hope you are still with me.. I have got all of the
above working via standard queries or macro's..
 
K

Ken Snell [MVP]

So you're creating (via a maketable query, I assume) a copy of a table that
is used as the source of choices (tableY) and then use that to get the final
choices for an order. You copy data from the tableY to tableZ via an append
query, I assume? And then you use a DeleteObject macro to delete tableY?

tableY is being created on the user's PC, so it is not shared by other
users, right?

So let me suggest a way that you can make this work without having to delete
and create tableY, and thus avoid the need for the VBA function that you'd
otherwise need.

Use this type of process.

Create tableY and never delete it. Instead, in your macro that currently
creates tableY, use a condition that gets the current record count in tableY
via a DCount function. If the DCount function returns a value equal to zero,
then tableY is empty and needs to be filled with data from tableX. Use an
append query to copy tableX into tableY, and then open your form.

However, if the DCount function returns any value other than zero (<>0),
then tableY has data in it and no appending of data is needed. Just open the
form.

The macro action would be something like this:

Condition: DCount("*", "tableY") = 0
Action: OpenQuery
Query Name: NameOfQueryToAppendTableXToTableY

Condition: < blank >
Action: OpenForm
Form Name: NameOfFormBoundToTableY

Then, after you copy the choices from tableY into tableZ, run a delete query
that deletes all records from tableY and makes it empty again, ready for the
next cycle.

This eliminates the need to delete, create, and test for existence of
tableY.
 
R

Robert Gillard

Ken
Thank you for all of the time and effort you have put in on my
behalf.... an excellant solution, just what I needed.

Many thanks

Bob
 
B

blairuk1

Yes you can !

Use this function as your condition:

DCount("*","MSYSOBJECTS","[Name]='<yourtable>'")>0

eh voila !
 
Top