suppress macro messages

[DanDungan]

Hi,

In Access 2000 on Windows XP, I'm using a macro to import files to a
table. I use an update query to scrub the file and an append query to
move the records from the temp table to the production table. These
generate messages that the macro echo off command doesn't suppress.

Then the user has to click repeated on the dialog boxes that pop up.
If they choose a no answer accidentally, the process fails and their
information isn't available to them.

I've converted the macro to vba but the boxes continue to pop up. How
can I suppress these query messages or write vba to accomplish this
import without the messages.
Thanks

Dan

Here's the macro.
Function mcrImportData()
On Error GoTo mcrImportData_Err

DoCmd.Echo False, ""
DoCmd.OpenQuery "qry_Delete_data", acNormal, acEdit
Call FunCopyRename
Call ImportEveryFile
DoCmd.OpenQuery "qry_DeleteNullData", acNormal, acEdit
DoCmd.OpenQuery "qry_UpdateDataRemoveQuotesFromRefNum", acNormal,
acEdit
DoCmd.OpenQuery "qry_AppendDataToHistory", acNormal, acAdd
DoCmd.Echo True, ""


mcrImportData_Exit:
Exit Function

mcrImportData_Err:
MsgBox Error$
Resume mcrImportData_Exit

End Function

 

[Tony Toews]

I've converted the macro to vba but the boxes continue to pop up. How
can I suppress these query messages or write vba to accomplish this
import without the messages.

DoCmd.OpenQuery "qry_Delete_data", acNormal, acEdit

The problem with DoCmd.RunSQL is that it ignores any errors. Either
of the following will display any error messages received by the
query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror..
For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText You can then remove the
docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/