Can you trap error when running update query in VBA code with SetWarnings turned off?

[VicM]

Hi,

I just discovered, accidentally, that when running an update query in VBA code with SetWarnings turned off, any record with an error doesn't get updated. When I tested the update query by itself, the error message indicated there was a validation violation. But when checking every field in the design of the table, no fields had Validation Rules or Validation Texts.

I traced my problem to a text field, which contained data in the table to be updated, but the updating field was blank. Since the table's properties for that field included [Required = No] and [Allow Zero Length = No], I subsequently set the [Allow Zero Length = Yes]. That allowed the update to work.

My confusion is because I've read several suggestions indicating to not set the Allow Zero Length property to Yes.

There are several text fields in the update query and now because there was no warning, the possibility exists that many times those records were not updated when a field was to be changed from containing data to being empty. The update procedure has occurred monthly over many years. I shudder to think of corrupt data that may reside in the DB.

Is there any way to trap that kind of error without turning the SetWarnings on, which would cause a message to be displayed to the user?

Any suggestions will be greatly appreciated.

Vic

 

[VicM]

Folks,

After additional thought and review of my code, I discovered that if the offending text field was empty, I set it to a zero length string ("") before executing the update query. When I change the code and set the field to Null, the update query functions as expected without me having to change the table's field property to Allow Zero Length.

As it turns out, I had already set all the other text fields in my code to null and not "".

Basically this thread is solved.

Thanks,
Vic

 

[lameid]

Instead of turning warnings on and off with docmd.setwarnings and running with docmd.openquery you can use the DAO object model instead.

Use the Execute method of the database object to run your SQL, below strSQL is a string variable that contains the SQL of the code you want to run. It may work with a query object but it has been too long since I have coded that way for me to remember but I want to say no. Also you would likely have an object declared rather than just running on currentdb.

Since this is DAO connecting directly to the database, any user defined functions in the query will not work. For those you are stuck with the application object model for data (e.g. Application.docmd).

currentdb.execute strSQL, dbFailOnError 'The key to getting an error is the dbFailOnError constant as the second parameter