Turn Off Message on Append Query

Programming and Web Development Forums - MS Office Access - Microsoft Office Access

Skip to content

Turn Off Message on Append Query

MS Office Access
Post a reply

    Next

  • 1. How to transpose a table?
    I have a table with fields: Year, Sales, Cost of Good Sold and Profit. The table records financial data year by year. Now I want to make a report or a query that has column showing years and three rows showing the financial data. It means that I have transpose my current table. Supposed that I will increase additional fields such as Tax, number of Employees etc. Do you know how to do this? Thank you very much
  • 2. Dlast Function in Subquery
    I have this kind of table Code Date Qty Value Amount A N 1 5 5 A N+1 -2 Y B N 2 3 6 The first Qty for a given code is always positive. It is ordered by code them by date. If Qty is negative I want Value (Y) to be Equal to the preceding value (5). In other words if Qty is negative, Value (Y) must be equal to the value corresponding to the last positive Qty (5). I thinking of using DLast function in a subquery. More or less like this Dlast(alueableCode=Code and Da<e
  • 3. Access Discussion Group
    Can the Office Discussion Groups listed on the left sidebar be reformatted into a drop-down listing? It is very difficult to search the discussion threads for titles of questions that may be suitable to a user's problem because the thread window is too small, and resizing it means the message window on the right is too small to read. The left sidebar takes up too much room on the page and severely limits scanning the thread topics to find applicable questions/answers to problems. Thanks.
  • 4. Dialog box - paramater query
    Hello all. I have a Dialog Box to set parameters for two fields: one is "supplier" and the other is between [StartDate] and [EndDate]. The "supplier" parameter is working OK, but the dates parameter NOT. The query is base on two other queries which are based on two and one queries, respectively. I have set the criteria parameter entries in the "base" queries, i.e., in these last two+one queries, because they're the ones that contain the fields "StartDate" and "EndDate". It's not working. Two questions: 1) Must the criteria parameter entries be set in the "last" query? 2) I have three dates, three different fields, in the "base" queries, and I want to entry only one parameter to set the (same) date for these three fields. Is that possible like this? Thank you.
  • 5. sum with apped query
    i got this table with the actual stock of trays every day trays go out and came in to the factory. i can get the table of todays movements, but know i want to add or subtract to my actual stock of trays table.. i know who to do it movement by movement.. what i want is a list of movements to be calculated in just 1 click thank you

Turn Off Message on Append Query

Postby ChuckW » Sat, 22 Nov 2003 04:55:54 GMT 

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
Top

Re: Turn Off Message on Append Query

Postby Kelvin » Sat, 22 Nov 2003 08:17:45 GMT 

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
Top

Re: Turn Off Message on Append Query

Postby ChuckW » Sat, 22 Nov 2003 23:52:59 GMT 

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 


query.  You have to add
then
remember to run it back on.
macro command to turn
message
Top

Re: Turn Off Message on Append Query

Postby Kelvin » Sun, 23 Nov 2003 00:54:12 GMT 

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
Top

Re: Turn Off Message on Append Query

Postby ChuckW » Sun, 23 Nov 2003 03:45:34 GMT

elvin,

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


should have atleast
options the same)
preview)
it is like pushing
is an append query,
result table. If it
shown.
run when you use an
query? If so that is
of data, setting
with no data. The
regular query.
message
trying
runs
things:
the
want.
two
know
in
about
query.
Top

Re: Turn Off Message on Append Query

Postby Kelvin » Sun, 23 Nov 2003 05:47:44 GMT

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

"ChuckW" < XXXX@XXXXX.COM > wrote in message
news:07e301c3b05f$a6006010$ XXXX@XXXXX.COM ...


Top

Similar Threads:

1.Turning off message "Can't Add all the records in the Update or Append Query" 

I get this message - and I know why I get it. 

But I am trying to turn it off and handle the error myself 
and avoid this pesky prompt. 

Anyone know how to suppress this message? 
On Error does not seem to work although it returns a 2501 
error code. 

Ken Higgins

2.Problem turning off action messages in runtime - append query not working 

Hello All,

First of all I would like to say thank you for all of the help I have
received here.  I have been teaching myself Access for about 4 years
now and I've always been able to find a solution here - until now.
This one is driving me crazy.

I am making my first attempt at creating a runtime application.  I am
using Access 2003 Developer Extensions.  Initially I developed the
database without planning on creating a runtime app but things have
progressed and I wanted to distribute it to those who don't have MS
Access.

My problem is trying to run an append query without getting the confirm
messages.

I have a main form with a subform.  On the subform is a button that
appends records from the subform into a header and detail table (if the
record does not exist) and opens another form to display the records.
Too append the records I am using two queries.  This has always worked
for me perfectly well until I tried the runtime version on a PC without
Access.

I researched and found different things to try and all of them either
do not append the records or give a parameter error.

The full code behind the button is:

Private Sub CmdOpenDetails_Click()
On Error GoTo Err_CmdOpenDetails_Click

'Verify that Race Date, Race Track and Race Name are entered.

    If IsNull(Me.Track) Then
        MsgBox "Please enter a track.", vbCritical, "No Track entered."
        Me.Track.SetFocus
        GoTo Done
    Else

    If IsNull(Me.RaceDate) Then
        MsgBox "Please enter a Race Date.", vbCritical, "No Race Date
entered."
        Me.RaceDate.SetFocus
        GoTo Done
   Else

    If IsNull(Me.RaceName) Then
        MsgBox "Please enter a Race Name.", vbCritical, "No Race Name
entered."
        Me.RaceName.SetFocus
        GoTo Done
    End If
    End If
    End If


    DoCmd.RunCommand acCmdSaveRecord

    Dim stDocName As String
    Dim stLinkCriteria As String

   On Error GoTo Err_CmdOpenDetails_Click

    DoCmd.OpenQuery ("qryappendtblraceheader")
    DoCmd.OpenQuery ("qryappendracedetail")

    stDocName = "subfrmracedetails"

stLinkCriteria = "([TrackName]=" & "'" & Me![Track] & "') AND
([RaceName]= """ & Me![RaceName] & """) AND [RaceDate]=" & "#" &
Me![RaceDate] & "#"

    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdOpenDetails_Click:
    Exit Sub

Err_CmdOpenDetails_Click:
    MsgBox Err.Description
    Resume Exit_CmdOpenDetails_Click

Done:

End Sub

I have attempted to turn off the action confirms with:

the execute method  -  this caused a "need 3 parameters error"
Setoption "Confirm Action Queries", False - this goes to the next form
but without appending the records
Docmd.setwarnings False - same result as setoption
Application.SetOption "Confirm Action Queries", 0 on load

My latest try was creating a macro turning off the messages and then
turning them back on.  That also moved to the next form but did not
append the records.

I really don't understand what I'm doing wrong and I've run out of
things to try.

I would really appreciate any help at all with this and if I haven't
provided enough information please let me know.

Thank you,

Lee

3.Turn off 'Can't Append" Message 

A button activates several append queries that modify and 
add data to a table based on several other tables. The 
tables are designed to prevent dups and I want the table 
to reject certain records.  

How do I tell Access to not tell the user that records 
cannot be appended?  In other words, I want it to do its 
thing without bothering the user with the details.
Thanks,
Rick O

4.How do I turn off the error (violations) messages in an append que 

When I run an append query, there are some records that can not be appended, 
due to key violations; which is what I want it to do. But how do I prevent 
the pop up message from appearing

5.How to turn off the printing message off? 

Hi

Is it possible to turn off the printing message from VBA 
for access?

Thank you

6. Turning off warning message when running query

7. Query messages turning them off

8. Can query messages be suppressed ro turned off

Post a reply

Return to MS Office Access

 

Who is online

Users browsing this forum: No registered users and 85 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group