updating table with new records from another table before deletion

[Frankie via AccessMonster.com]

I have, among others, the following tables :
tblprospects -PK [Prospect] - String value
tblContactsProspects -FK - [Prospect]
tblClients -PK [Client] - String Value
tblontactsClients -FK [Client]

What I would like to do :
When a propspect is labelled "Client" on tblProspects thru an After Update
event on FormFileCreation( this form is used for creating new Clients
files which is the first step of making a prospect become so), I want this
record and its associated records in tblContactsProspects to be added to
tblClients and accordingly to tblContactsClients, then get suppressed where
it came from.
I suppose there is a way to do it thru chained queries but I am getting a
little confused.

Can someone give me a lead for that little project since I am not an expert
in Access ??
Thanks in advance for your help.
Frankie
Access 2003

 

[Lynn Trapp]

Frankie,
If I understand you correctly, you are wanting to store the same
information in tblClients that you are storing in tblProspects. If that's
correct, then there is no need to do that and, in fact, it is a bad idea.
Merely add a field to the one of your tables that identifies a person as
either a Client or as a Prospect (or both if that's a possibility).

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html