Turn Off Message on Append Query

[ChuckW]

Hi,

I want to turn off the message that says "You are about
to paste XX rows..." when I am running an Append query.
Can someone tell me how to do this?

Thanks,

Chuck

 

[Kelvin]

You cannot turn off the messages directly from the query. You have to add
code to do it. If you are calling the query from code then
DoCmd.SetWarnings False would turn it off. Just remember to run it back on.
If you are calling the query from a macro, there is a macro command to turn
the warnings off. again, remember to turn it back on.

Kelvin

 

[ChuckW]

Kelvin,

Thanks for your help. There is another thing I am trying
to do. I am building a push button application that runs
reports for people. I want this query to do two things:
One is to run a report that people view on a screen, the
other is to populate a table. This is why I was using
the append query. However, when I changed the type of
query to an append, it no longer runs the report I want.
Can I do both? I also don't want the append message to
occur. Is it possible to run a macro that does these two
things or do I need to right VBA code which I don't know
very well.

Thanks,

Chuck

 

[Kelvin]

You can create a macro to do multiple steps. Your macro should have atleast
4 steps based on what you described.

1) SetWarnings (No)
2) OpenQuery (Name of your append Query, leave other options the same)
3) OpenReport (Name of you report, set view to print preview)
4) SetWarnings (Yes)

The open query command does not actually open a query, it is like pushing
the run command when writing a query, so if your query is an append query,
then it will just perform the append and not open the result table. If it
was a regular query then the resulting table would be shown.

I do not understand what you mean by the report will not run when you use an
append query. Is the record source for the report the query? If so that is
wrong. Since an append query does not reuslt in a table of data, setting
the report to use this query as the source will fill it with no data. The
source for the table should either be a table or a regular query.

Kelvin

 

[ChuckW]

kelvin,

Everything worked great. This was very helpful. I do
have one more question for you. The table I am trying to
populate with my append query has the following fields:
Name, Date, Product, Cost. The user runs this report
once a week. I have now set it up so that it is also
populating a separate table with my append query. The
problem is that I do't want the data duplicated in case
she needs to run the report again later in the week.
There is a special data restrictor in the query which
will take all transactions from the current week.

There is no primary key available with my original data
source. What is unique is that no customer will order the
same thing on the same day with separate transactions. I
tried to create an autonumber primary key which did not
accept duplicates but this did not worked. Ideally I'd
like for query to not populate the data again but not
give a message to the user saying the data would not
populate.

Any thoughts?

Thanks,

Chuck

 

[Kelvin]

I would create another field in your source table, something like Updated as
a yes/no type. Set them all to Yes for now. Then in you append query set a
criteria to only append records where this field is No. Then have an update
query to update this field to Yes after the append query runs. Then the
next time the user hits the append button, nothing will happen since the
records now have a Yes in the field.

Kelvin