Is there a reason you couldn't base the chart of a query? Empty a table
and append still creates bloat, especially if this is going to be a
repeat highly used function.
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" -
Tremors II
http://www.regina-whipp.com/index.htm
I have a form that the user can select what criteria is needed for a
report that displays a chart. I've found out, unfortunately, that it is
basically impossible to change the RowSource dynamically at runtime for
a chart. What I did instead was create a dummy table for the chart to be
based off. Then when the user selects the criteria on the form, it
creates the MakeTable SQL statement and executes it to populate the
table. In the SQL statement, I rename the fields from the original table
to be a standard name in the dummy table. The RowSource is as follows:
TRANSFORM Count([Topic]) AS [CountOfTopic] SELECT [Demographic] FROM
[Demographic / Topic] GROUP BY [Demographic] PIVOT [Neighborhood];
"Demographic" is either Age ranges, Years Lived at an address, or
Gender.
I guess what I could do is just run a SQL statement to delete the
current rows in the table and then use an Append SQL statement to add
the new data to the table. Either way, I'll still get those warning
dialogs. I'll also look at the "CurrentDB.Execute strSQL,
dbFailOnError" command as that will probably give me the same result
then without the warning dialogs.
Thanks,
Merg
--
Today's problems don't worry me,
I haven't solved yesterday's yet.
A few things you should think on though -
1. DoCmd.SetWarnings is something I would avoid if at all possible as
you
can find yourself without warnings if something glitches before you
turn them
back on. AND, it affects ALL of your databases, not just the current
one.
2. If you MUST use DoCmd.SetWarnings be sure to put an error handler
in that
procedure and make the very first line of the error handler
DoCmd.SetWarnings True
so you turn them back on.
3. DoCmd.SetWarnings is NOT necessary in 99% of the time if you run
your
query by using the alternate:
CurrentDb.Execute strSQL, dbFailOnError
I would suggest using this instead of DoCmd.SetWarnings.
Also, there is a fourth consideration here.
Why are you using code to make a table? Does the definition change
each
time? Or, if it is the same, you should just delete all records from
it and
then append new records to it. This will save you on massive database
bloat
that can occur when you keep deleting and remaking tables.
--
Bob Larson
Free MS Access Tutorials and Samples at
http://www.btabdevelopment.com
__________________________________
:
Thanks. Easy enough.
- Merg
--
Today's problems don't worry me,
I haven't solved yesterday's yet.
Try..
DoCmd.SetWarnings False 'Turns them off
DoCmd.RunSQL etc...
DoCmd.SetWarnings True 'Turns them back on
--
Gina Whipp
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index.htm
I am using the DoCmd.RunSQL command to execute a MakeTable query. I
get
prompted with the standard "your current table will be deleted" and
"xx
rows will be added to your table" prompts. Is there anyway to have
it
automatically accept those options and move on?
Thanks,
Merg