Catching an error message that I do not know the error number of

[M_S_Jones]

Good morning,

I'm running an append query as part of a stack of queries in a macro. The data that the append query is retrieving comes from a much older system that contains the occasional instance of records with matching ID fields. In such cases, it isn't feasible for us to find which is the correct one and thus we are happy to just ignore both records whenever this happens. But we still want the rest of the data to be appended into a table in the current Access database. When the query is run, an error message is displayed informing me that:

"Microsoft Access can't append all the records in the append query"

If I press the 'Yes' button on this message box, only the records with unique key fields are appended to the destination table; which is exactly what is desired. My question is: can this process be automated? That is, can I capture this message box using an error trap and select 'Yes' as my response using VBA code, so that I could call it in the macro to prevent the button having to be pressed using the cursor?

Thanks for any help you can offer,

Matthew

 

[boblarson]

For this you need to use the

DoCmd.SetWarnings False
Run your query here
DoCmd.SetWarnings True

However, you do need to put an error handler in and in the first line of your error handler, you put

DoCmd.SetWarnings True

so that you don't find yourself without the warnings should the code fail before getting to the reset of the warnings.

 

[M_S_Jones]

Okay, thanks for that. Before I add this code though, can I just confirm that the effect of this would be as if I had pressed the 'Yes' button on the message box?

 

[boblarson]

Yes, that is what happens.

 

[M_S_Jones]

Thanks for that, exactly what I wanted!

Thanks again.