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

MS Office Access


  • 1. Percentage Query
    I need to do a percentage query with the following fields from my table Department Last Name First Name Active (this is a yes/no button which is checked if active) Termination Date I need to run a query to show the percentage of employees who where terminated during a given time period (eg 1 Jun to 30 Jun 04). This query needs to be run for each department and the start and end date will vary depending on which month the query will be run in. Not sure if I need to do a between date or just by month.
  • 2. Comparing dates from different records
    I have a date ordered field and have been asked to identify the number of days since the last 24 hour or more period with no orders. So if I have orders on 5/1/07, 5/2/07, none on 5/3/07 and then an order on 5/4/07 have to create the value 5/3/07 using the dates where I do have orders, and compare it to the current date (which I think would just be a straight subtraction). I can't figure out how to idenitfy the gaps between orders since each record has only a singel date field. Thanks in advance for any suggestions (PS I'm not much of a VB coder but can copy and paste :-)

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

Postby Ken Higgins » Thu, 04 Sep 2003 23:58:31 GMT

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

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

Postby Allen Browne » Fri, 05 Sep 2003 00:18:50 GMT


Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users -  http://www.**--****.com/ 

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

Postby Tom Ellison » Fri, 05 Sep 2003 00:36:54 GMT

Dear Ken:

The way I'd suggest handling this is to remove the attempts to add
rows illegally.  There are several causes for this, from which you
have apparently identified those that cause the problem.  These

- attempting to add a row which duplicates a unique key in the
destination table

- attempting to add two or more rows that share a unique key in the
destination table

- attempting to add a row that would violate a foreign key

- attempting to add a row containing a null value in a required column

These, and any other violations, should be removed from the set of
rows being added.  Don't do this by deleting them from the source
table, but by filtering them from the set being appended.  The same
filtering can be used to create a report of the rows not added, or to
handle them in some other way, just reversing the logic.

On Wed, 3 Sep 2003 07:58:31 -0700, "Ken Higgins"

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Similar Threads:

1.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.
Rick O

2.Turn Off Message on Append Query


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?



3.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

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

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

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."
        GoTo Done

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

    If IsNull(Me.RaceName) Then
        MsgBox "Please enter a Race Name.", vbCritical, "No Race Name
        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 Sub

    MsgBox Err.Description
    Resume Exit_CmdOpenDetails_Click


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,


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.Access 2003 runtime mode-can't turn off warning messages for macro

In Access 2003 I turn off all the warning messages in the Edit/Find tab under 
Tools + Options for my data base.  I then package it with the Access 2003 
Developer Extensions Tools Wizard into a  .dme runtime package.  When I run 
it on another computer other than mine, a computer with Access on it or one 
without Access on it, I get all the warning messages for macros and queries, 
as if I never turned off the Warning messages in the Edit/Find tab.  Any 

6. How to turn off the printing message off?

7. Turn off delete record message using code.

8. Turning off warning message when running query

Return to MS Office Access


Who is online

Users browsing this forum: No registered users and 59 guest