1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Copy data fields in same table to other fields in same table MS Access

Discussion in 'Business Applications' started by Hnlevitt, Jan 15, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. Hnlevitt

    Hnlevitt Thread Starter

    Joined:
    Jan 15, 2009
    Messages:
    12
    MS Access:
    I have several fields in a form that uses two tables. I would like to set up a command button so that when you are on a current record, it will copy the field information from the record you are on to other fields in the same record. I do not know the SQL commands to set this up.

    For example, there are name fields, address fields, and phone to be copied. Reason: One is contact information; one is survey information. In most cases the information is the same but on rare instances, the information needs to be entered (when different).
    This is not an update for all records in the database. This is based individually on each record.
    Not sure if you need to do On current, on click, or after update.
    Not sure if default value in form can be used.
    Please help.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Hnlevitt, welcome to the forum, this post nearly got lost as it had slipped down to the 2nd page.
    I do not normally recommend copying data from one table to another as that is unnecessary duplication of data. It might be better if you had a 3rd Table that just held the data that could be common or not and either use an existing Record ID when it is common or add a new Record when it is not.
    However I can see the logic of why you want to do it and you can easily do what you want using the On Click Event of a Command Button.
    It just involves some very simple VBA code.
    If you can give me the names of the Fields that you want to Copy From and To I can create it for you.
     
  3. Hnlevitt

    Hnlevitt Thread Starter

    Joined:
    Jan 15, 2009
    Messages:
    12
    In the the form titled frmContacts, there are two tables. One is called tblContacts. It holds ID, DeceasedID (which is connected to the table titled tblDeceased), and the fields I would like to copy from are: Salutation, ContactFirst,ContactMI,ContactLast,ContactSuffix, Contact_Address1, Contact_Address2, Contact_City, Contact_State, Contact_Zip, Phone. I would like to copy these fields to: BuyerTitle, BuyerFirst, BuyerMI, BuyerLast, BuyerSuffix, Buyer_Address1, Buyer_Address2, Buyer_City, buyer_State, Buyer_Zip, and Buyer_Phone

    Any help you can give is greatly appreciated. I can move these to a third table but I would still need to know how to have data copied into these fields. Whichever is best. thanks.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Hnlevitt, a 3rd table is usually best but we can go with what you want for now.
    Assuming that the Fields are all on the Same Form in your Command Buttons "On Click" event try this for the first field
    me.Salutation = me.BuyerTitle

    If that works OK you can probably do the rest yourself.
     
  5. Hnlevitt

    Hnlevitt Thread Starter

    Joined:
    Jan 15, 2009
    Messages:
    12
    thanks that seems to do the trick. However you have to put the destination fields = original fields first i.e.
    me.buyerfirst = me.contact_First

    How do stay on the same record without it going to next record?
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Sorry about that, woolly thinking.
    Just add all of the other Fields code to the same button.
    I am not sure about your question about going to the next record.
     
  7. Hnlevitt

    Hnlevitt Thread Starter

    Joined:
    Jan 15, 2009
    Messages:
    12
    when I click on the command button to copy data fields in the form, it automatically adds a blank record. how do I keep it on the same record and not have extra record created?
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Are you on a Record when you Click the Button?
    Does it put the Values in the Current Record or the New record?
    Try setting the Form's "Cycle" Property to "Current Record"
     
  9. Hnlevitt

    Hnlevitt Thread Starter

    Joined:
    Jan 15, 2009
    Messages:
    12
    I am on the current record when I click the button. it seems to insert a new record before the current record.
    How do you set the Form's "cycle" property to "Current record"?
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Open the Form in Design Mode and on it's Properties look for Cycle, it will be set to All Records.
    Is the Form in Conntinuous Mode?
    If it is the Command Button may need to be on the Record itself, not in the header or footer.
    Can you post a zipped copy?
     
  11. Hnlevitt

    Hnlevitt Thread Starter

    Joined:
    Jan 15, 2009
    Messages:
    12
    here is a zipped copy. I just want to know how to stay on current record of the form after you click the copy button (=>)
     

    Attached Files:

  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK I have had a look at your FRMContacts form and it is not changing record when I click the Button (on my copy), what it is doing is updating the Underlying Table Record, but not the Form because you have given the Form Fields Different Names to the Underlying Record's Fields.
    You have picked up the change in the Contact Fields with "Me.Contact_First" instead of "Me.ContactFirst" but in the Buyer Fields where you are trying to put the data you are using the Underlying Field names i.e. "BuyerFirst" whereas the Form Name for that field is "txtBuyerFirst".
    If you change the names to what they should be on the Form it should solve your problem.
    You can also Update the changes to show that the record hasn't changed (at least on my copy) by clicking Main Menu>Records>Refresh after you click the => Button.
     
  13. Hnlevitt

    Hnlevitt Thread Starter

    Joined:
    Jan 15, 2009
    Messages:
    12
    First I would like to thank you for all your help. when I enter a record and press the command button, it still creates a blank record.

    when you refer to clicking Main Menu>Records>Refresh, where do I acutally do this? I'm a little confused.
    Is there a command to place after all fields are copied?
    i.e. docmd.domenu ... or gotorecord or ???

    Not sure where you are telling me to refresh or how to do this exactly?
    thanks again.
     
  14. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Try this version.
     

    Attached Files:

  15. Hnlevitt

    Hnlevitt Thread Starter

    Joined:
    Jan 15, 2009
    Messages:
    12
    where exactly should i be looking for code changes?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/790911

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice