transfer data (Access 2000)

[pilgrimswake]

How to transfer the data to another table and then then delete them ?

Can somebody help me with my task? I have to solve the following problem.I have to find a client from the form frmClients, based on the table tblClients,
then open another form called FCustomers, based on the table customers,and in the new record insert some of the fields as Company,LastName and city,and after that delete the client from the form frmClients.The idea is that after a client has become a customer, he should exist no more in the table tblClients and appear in the table fCustomer sunder a new number.Both tables have an Autonumber.
First i chose the client in the form frmClients, and then in a control of that form I am opening the other form like that:
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "FCustomers"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , "", acNewRec
DoCmd.GoToControl "customerid"


After opening the form FCustomers with a new record, i want:
1. insert the fields Company, LastName and city which are common to the two tables
2. delete the client from the table tblClients.

How can i delete the client under question and how can i insert the values of the client under question to the new customer?

I preferred to state the task what must be achieved instead of telling of my unsucsefful efforts,since they will be of no use to anyone,
but the solution i may have may be sound and interesting to everybody.

[patt]

Why not just have the one table with an extra field telling you whether its a Client or a Customer. Then in the form (which is filtered to show Clients only) its only a matter of setting the field to say the record is a Customer.
Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

[charlotte]

I'm a bit confused by the difference between a customer and a client. It sounds like the same thing to me, but for goodness sake, take Pat's advice and don't try moving records between tables! There are too many possible ways to wreck your data doing what you suggest. Just add a field to the table of clients and use that to tell you whether this person/company is a client, a customer, or merely a prospect. You won't have nearly as much trouble maintaining the database with that approach.

[d_rasley]

I think I would take Pat's idea a step further and add a Yes/No field for each possible contact classification, so that if a client also becomes a customer or some such multiple classification you could handle it with ease.

<table border=1><td></td><td>ContactType1</td><td>ContactType2</td><td>All other contact details</td><td><center>x</center></td><td></td><td>All other contact details</td><td></td><td><center>x</center></td><td>All other contact details</td><td><center>x</center></td><td><center>x</center></td></table>

This type of structure should also make it relatively easy should you have to add another classification.

[charlotte]

<img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> That denomalizes the table! No, if you need for a client to fall into multiple categories at once, you need an additional table to hold a single record for each clientID for each category they fall into, with the clientID as the join key. That is infinitely flexible. Adding fields to the client table is a major maintenance issue over time and makes querying the data much harder.