Make a query or code check if a record exists & prompt user for action

[KennyJaymes]

Hi all, I am a relitive newcomer to VB-A so please treat me like a dummy. I am hoping to learn from your kind replies.

I have an append query that is part of a chain of querys used to import data from a outside database.

Each record in both my own and the outside db have a unique Catalogue Number.

I need some way of checking if this catalogue number already exists in my own database and if it does,
I would like the query to be paused while a message box pop's up and warns the user that Catalogue Number <what ever it happens to be to be shown to the user> already exists and wont be appended, the user then clicks on an OK button, and the code then continues until either it happens again or the import is completed.

Is this going to be very hard to do and could someone suggest how I might do it. Code examples would be great if I am not being to cheaky or imposing on everyone (I know you are all busy people).

Thank you in advance,

Kenny
[sig][/sig]

 

[jagilman]

An error trap is probably the easiest way. Make sure the table you are trying to append to has an index that wont allow dups. Then get the access err code for the error. In your error trap check for that error and display your message. If it is a different error, take the appropate action.
You will need for the append process to be part of a sub using a recordset object so you can continue with the next record instead of just falling out of the query.

If you need help just say so!
[sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[MichaelRed]

KennyJaymes,

There are many approaches to this - however, imho, stopping a dynamic process for button pushing is not one of the recommended ones. This ties some poor person (You?) to sit in front of a machine wainting for something to happen - and then - when it (something happens) he/she needs to &quot;Push the button&quot; which only acknowledges that the something did happen. DULL AND BORING!!

My suggestion is to 'log' the errors to a file however you may. Also, you should note that queries do not respond well to errors. Generally, they either fail completly (e.g. do nothing) or they ignore problems and continue about their business - w/o much regard to you (your program).

You mention that you are using 'several' queries to accomplish the &quot;import&quot;. I have usually found that when the &quot;several&quot; queries are required for the import process, it is reasonable to write &quot;CODE&quot; which accomplishes the import in a single pass process. This also lends itself to the trapping of problems and keeping the offending records in some place which is available to the user for review (and possibly correction/change of the problems) w/o needing to review the entire import process. Of course, some of this is predicated on the overall process - not just the desire to 'know' that some records are not being appended.

Another prospect might include importing the &quot;New&quot; data into a temporary table, doing whatever grooming is necessary, and then generating the old &quot;Duplicates&quot; query between the &quot;Current&quot; and &quot;New&quot; tables. This query could be the basis of a report which would serve as the &quot;Notification&quot; that records were not appended ... It could also be used as a &quot;filter&quot; for the final append query - which would supress the errors

Hope this makes some sense to you.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[WP]

Kenny,

What is your e-mail address and I'll mail you an example.

WP [sig]<p>Bill Paton<br><a href=mailto:william.paton@ubsw.com>william.paton@ubsw.com</a><br><a href=

http://www.neptune400.co.uk>www.neptune400.co.uk</a><br>

[/sig]