Error Msg during append

[Kenln]

I have a query in VBA which works no problem.

However if the data is invalid (doesn't match relationships etc.) I get an error (continue/don't continue) message. This is not an actual Error with an error number????

A) Can I trap this? How ?
B) Can I change the message?

Thank you for your help,

 

[Moniker]

The problem is a key violation, which means you're doing one of the following:

1) You're trying to put a duplicate value into the primary key, which you cannot do
2) You're trying to put a duplicate value into any index that has Unique = Yes, meaning no duplicates

 

[Kenln]

Yep! I thought that was the problem. It can also be caused by trying to append data that has referential integrity enforcement and no matching record. Do you know how I can trap this or change the message?

 

[RuralGuy]

Do you have error handling code in the procedure than uses the query?

 

[boblarson]

Another possibility is to do a check first to see if it already exists.

 

[Kenln]

Yes, and if I try to import data (into a table using transfertext) that does not match the table field names the error handling works okay. I am trying to ensure that:
- The user has not selected and improper file - Okay with error handler
- All of the required files exist - Okay with code using the 'DIR' command.
So far I have this worked out.

My problem comes in if the imported data (which is in a temp table using transfertext) does not match or have valid relationships. I get the continue/don't continue message (see attachment) but do not know how to trap this or change the message.

 

[Kenln]

Sorry here is little more info:
A) The user selects a file to import
B) I check that related files exist using 'DIR'
C1) I use 'transfertext' on the files one at a time importing them into temporary tables
C2) If the import fails I get an actual error which I trap and notify the user - 'Hey bad data',

- So far I'm okay

D) I use an append query to move the data from the temp table to the real table and if I get a relationship message this is not a true error but rather something not very intuitive for non-Access users. This is where I have gotten stuck.

 

[RuralGuy]

I hate to suggest SetWarnings False as a solution. Surly this error can be trapped. Maybe one of the guru's has a solution. What version of Access are you using?

 

[Moniker]

After my 5-10 minutes of experimenting, I couldn't seem to trap this one. SetWarnings False will in fact skirt around it, but it won't tell you what/how many got missed. One way to do it would be to expand on what Bob suggested and check on a record-level basis. Another possibility is to import everything into a temporary holding table, and then perform an update on that query that sets required fields to default values (Use Nz to remove NULLs, for example), and then perform an append from the temporary holding table to the final destination table. That will at least prevent the NULL issue, but it depends on how many fields you're dealing with, which ones allow duplicates, etc.

 

[Kenln]

I just found out about the SetWarnings False too. The temp table has default values for each field so the null problem has been avoided (I think). I am still left with the possiblility of the data not being correct.

I'll keep working on it. If anyone has any ideas please let me know.

 

[RuralGuy]

If you use the DAO CurrentDB.Execute then the RecordsAffected property will tell you what happened.

 

[Kenln]

I an not sure how to use this. Could you give a little more detail?

 

[gemma-the-husky]

i used to use
setwarnings false/true around a
docmd.openquery

trouble is, it suppresses errors without informing you it failed

ruralguy repeatedly told me to use currentdb.execute, so that you can intercept the errors, which i now do - its so useful

just a warning - some threads here point out that execute USED TO work as a "transaction commit/undo", but no longer does