Return values

[Monty]

I have two approaches to a problem I'd like to ask about. I have a
DoCmd.RunSQL statement that inserts a new record into a database that
contains about two dozen fields. The SQL is run when a user presses
the 'Add' button on a form. Everytime the button is pushed, the
message

Microsoft Office Access set 1 field(s) to Null due to a type
conversion failure, and it didn't add 0 record(s) to the table due to
key violations, 0 record(s) due to lock violations, and 0 record(s)
due to validation field violations.

Is this error message telling me that all three conditions exist (type
conversion failure, key violation, and lock violation)?

Secondly, how can I trap this error message or the return status of
the DoCmd.RunSQL so I can process it myself?

Thanks

 

[John W. Vinson]

I have two approaches to a problem I'd like to ask about. I have a
DoCmd.RunSQL statement that inserts a new record into a database that
contains about two dozen fields. The SQL is run when a user presses
the 'Add' button on a form. Everytime the button is pushed, the
message

Microsoft Office Access set 1 field(s) to Null due to a type
conversion failure, and it didn't add 0 record(s) to the table due to
key violations, 0 record(s) due to lock violations, and 0 record(s)
due to validation field violations.

Is this error message telling me that all three conditions exist (type
conversion failure, key violation, and lock violation)?

It's telling you that there were...


1 field(s) to Null due to a type conversion failure errors
0 record(s) to the table due to key violation errors
0 record(s) due to lock violation errors
0 record(s) due to validation field violations.

Secondly, how can I trap this error message or the return status of
the DoCmd.RunSQL so I can process it myself?

Don't use RunSQL. Instead use the Execute method and trap any errors:

Dim db As DAO.Database
On Error GoTo Proc_Error
<define your SQL string in the variable strSQL>
db.Execute strSQL, dbFailOnError
<the rest of your code>

Proc_Exit:
Exit Sub
Proc_Error:
<handle any query or other errors>


It would be well to figure out why you're getting the type conversion error
and fix it.


John W. Vinson [MVP]