Can I disable validation popups in a maketable query.....?

[Dupatt]

I may be going about this totally wrong but I have a "appttable" that
controls a report and I need to keep the data new from an appointment book
entry so the scheduler can open the report to check on times available for
new appointments; I have created a make table query that overwrites the
"appttable" when the user completes filling the form. I am using ID# and
date as joint primary key in the table to prevent double entries. This
works great except the popup which tells you the existing table will be
deleted comes up and my users would panic if they saw that. Can I hide these
on a maketable overwrite?
I hope this makes sense and if there is a better way of doing this please
show me. I appreciate all that I learn just from reading all of the
solutions you Access "geniuses" come up with.

 

[Arvin Meyer [MVP]]

Go to Tools >>> Options >>> Edit/Find and uncheck Confirm Action Queries.

You can also run your Make-Table query in code using the Execute method:

CurrentDB.Execute "SELECT * INTO MyTable FROM ... "

 

[John W. Vinson]

I may be going about this totally wrong but I have a "appttable" that
controls a report and I need to keep the data new from an appointment book
entry so the scheduler can open the report to check on times available for
new appointments; I have created a make table query that overwrites the
"appttable" when the user completes filling the form. I am using ID# and
date as joint primary key in the table to prevent double entries. This
works great except the popup which tells you the existing table will be
deleted comes up and my users would panic if they saw that. Can I hide these
on a maketable overwrite?
I hope this makes sense and if there is a better way of doing this please
show me. I appreciate all that I learn just from reading all of the
solutions you Access "geniuses" come up with.

MakeTable queries are *very* rarely a good option - and I certainly don't
think they are in this case.

If you're assuming that you must have an appttable created afresh in order to
generate a report, that assumption IS WRONG. You can - and should - base your
Report on a Query selecting those appointment records (which might not be
those from only the single most recent appointment!).

If you *must* have a separate table, then don't use a MakeTable; keep a
permanent stored appttable, and run a Delete query to empty it and an Append
query to fill it.

John W. Vinson [MVP]

 

[Dupatt]

Thank you very much for your help.
I have gone the delete and append route before and it worked well but will I
still get the "scarey looking" delete warnings (some of my users are
paranoid)? I will go back to your suggestion and use it.
While I am here I have another question about using the activeX calendar
control. Can more than one date field use the same calendar? Is it
"unacceptable" to use more than one calendar?
Thanks again.

 

[Dupatt]

One thing I forgot to ask. Will my joint primary key of Id# and Date be
deleted also? I need to be sure no duplicates get in there.

 

[John W. Vinson]

Thank you very much for your help.
I have gone the delete and append route before and it worked well but will I
still get the "scarey looking" delete warnings (some of my users are
paranoid)? I will go back to your suggestion and use it.

Follow Arvin's selection of using the querydef Execute method. Perhaps you
could post your code - we can't see it, and it's more than a bit difficult to
advise.

While I am here I have another question about using the activeX calendar
control. Can more than one date field use the same calendar? Is it
"unacceptable" to use more than one calendar?
Thanks again.

Please ask the question in a new thread - I've never actually had occasion to
use the calendar control. Don't see why you couldn't use two or more but I'm
not certain!

For your other question - please amplify. A Delete query without criteria will
delete all records in the table; the primary key makes absolutely no
difference to that. And a unique two-field index will do just that - ensure
that that particular combination of field values cannot be duplicated.

John W. Vinson [MVP]

 

[Dupatt]

Sorry about not giving you code to see but I am on my home computer and my
database is on my work computer.
You have answered my questions to my satisfaction, so thank you again, and
goodnight.

 

[Arvin Meyer [MVP]]

Using both Access 2002 and 2003, I get Action queries performed without any
warning notices. If that doesn't work for you, use the CurrentDB.Execute
method, or as John Vinson suggests, just delete the data and refill with an
Append query, using the Execute method if you can't stop your warning
messages.

You can also turn your warning messages off in code with:

DoCmd.SetWarnings False

then turn them back on with

DoCmd.SetWarnings True

It also works with a macro.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I have unchecked the comfirm action queries yet I get this popup

 

[Dupatt]

Thanks for all of your help. I think I have it fixed with the delete and
append suggestion.