Access 2000 Query–Very ImportantLocked
I am developing a small package in Access 2000 with Front end and back end both as Access and I have a techical doubt in that which is explained as below
While using the DoCmd.OpenQuery statement in VBA of access, to supress the messages access asks we use the SetWarnings command. This command suppresses all messages given while using Append query in VBA.
But while appending any rows, it also suppresses Error messages. The senario is as under
I have append query which appends some records in a table having Primary key, to avoid duplicates. But my append query, is trying to update some records which may create duplicates in the table. Since, table is indexed, duplicate records will not be appened to the table. So far so good.
If am notusing DoCmd.SetWarning fascility to suppress the messages, it will ask me to update the records, say 100. When I click Yes, to allow the append, system will try to add 100 records to table. but because of duplication of code only 50 records ar updatable. Now system will flash another message saying it cannot add 50 records due to key violation errors. And will ask to click Tes/No button there. If we click Yes, error is supressed and no error is displayed. But if we Click NO then, for every record, it will display the type of error occured. This is fine.
NOW, if I supress all messages i have with DoCmd.SetWarning , then all messages are supressed and I am not able to catch error there and ultimately, I am not able to detect the number of records affected by the save for that instance by the particular user.
Is there any way we can have the number of rows appended OR Trap the error message while using DoCmd.SetWarnings??
I will be highly thankful to you if you can solve my problem as early as possible