Supress Error - Microsoft Office Access can't append all the records...

[Hendy88]

Hello all,

I'm semi-new to Access and am wondering if there's a way to stop an
error message from appearing everytime I run a Query. The Query I
started from the "Design Mode" appends records from one table to
another, however there are a few error/warning messages that appear
everytime that I run/open it.

The first message is: You are about to run an append query that will
modify data in your table.

The second message is: You are about to append XXXX row(s).

The third message is: Microsoft Office Access can't append all the
records in the append query.

I've gone into Tools - Options - Edit/Find and unchecked the three
Confirm conditions (Record Changes, Document Deletions, and Action
Queries), however this only supressed the first two messages but the
third one still appears. I'm somewhat familiar with behind the scenes
VB code, so would I need to perhaps insert a "Module" and write a
Private_Sub? Or is there another checkbox maybe somewhere in the
Tools - Options that I could uncheck to stop this message?

Thanks in advance!
Steve

 

[Richard]

Hi Steve

You may want to try this,

just before running the query, suppress the warning with:

Docmd.SetWarnings False

open query

Docmd. setwranings True ' Reset

Hope this helps
Richard

 

[Tom Wickerath]

Hi Steve,

The third message is: Microsoft Office Access can't append all
the records in the append query.

The cause of this problem should be investigated further. Some possibilities
include trying to add a record that attempts to duplicate a value in a field
that is indexed uniquely, or having a default value of zero on a numeric
foreign key field, where referential integrity is in place and you are not
supplying a value in this field for one or more records.

Richard suggested using:
DoCmd.SetWarnings False
and
DoCmd.SetWarnings True

immediately before and after running your query. While this will work, it
can also be rather dangerous, especially if you do not include proper error
handling with DoCmd.SetWarnings True included. Without proper error handling,
the code could bomb out before it reaches the line to restore warnings. I
have a Word document available that demonstrates various methods of running
action queries in code:

http://home.comcast.net/~tutorme2/samples/ActionQueryExamplesWithSetWarnings.doc


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Hendy88]

Not sure if I quite understand where to put the DoCmd.SetWarnings
False statement. I current don't have any VB code at all. I'm just
running the OpenQuery setup from a Macro off the menu (not a custom
written macro in VB). When I open the query in "SQL View" I see this:

INSERT INTO [Deleted Salvage]
SELECT [Deleted Salvage - Linked].*
FROM [Deleted Salvage - Linked];

When I try to add the command to the beginning:

DoCmd.SetWarnings False
INSERT INTO [Deleted Salvage]
SELECT [Deleted Salvage - Linked].*
FROM [Deleted Salvage - Linked];
DoCmd.SetWarnings True

It won't even let me save it as it says: "Invalid SQL statement;
'DELETE', 'INSERT', etc.

Or is there another way to type it into this "SQL View" window?

Thanks

 

[Douglas J. Steele]

In your macro, put SetWarnings False as the instruction before you run the
query.

I've been told thatMacros will automatically set the Warnings back (I never
use macros, so I don't know), but just to be save, put a SetWarnings True as
an instruction afterwards.

 

[Tom Wickerath]

Hi Steve,

The methods that I gave you can be run from VBA code only. So, for example,
Sub Test3() in my Word document might be the click event for a command button
named "cmdUpdateCity" on a form, as in:

Option Compare Database
Option Explicit

Private Sub cmdUpdateCity_Click()
On Error GoTo ProcError

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblEmployees SET tblEmployees.City = ""Tukwila"""
DoCmd.SetWarnings True

' Additional code can be inserted here after re-enabling warnings

ExitProc:
DoCmd.SetWarnings True
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdUpdateCity_Click..."
Resume ExitProc
End Sub


Of course, this example is a bit silly, since it would update the city for
*all* employees (ie. the query includes no WHERE clause to act as a criteria).


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________