Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    transfer data (Access 2000)

    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.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: transfer data (Access 2000)

    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>

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: transfer data (Access 2000)

    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.
    Charlotte

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: transfer data (Access 2000)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: transfer data (Access 2000)

    <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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •