Results 1 to 9 of 9
  1. #1
    kmorgan is offline Novice
    Windows 8 Access 2016
    Join Date
    May 2018
    Posts
    5

    Copying specific fields of one record into another existing record.

    Hi all,



    (first forum post so be nice)

    Im having a nightmare trying to achieve what i want in access 2016.

    This form here is used to mark the status of rentals within the company, as you can imagine a order of 20 separate rentals on the same file will need the same "CUSTOMER DETAILS/HIRE" section filled in.
    the idea is to fill out the "CUSTOMER DETAILS/HIRE" section once then hit a copy button, search the next rental then hit a paste button to quickly update these separate rentals.
    Any help with this matter would be hugely appreciated because ive tried so much code that hasn't worked for me.

    Thank you very much!

    Click image for larger version. 

Name:	Capture.PNG 
Views:	19 
Size:	38.5 KB 
ID:	34141

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Run an append query.
    the criteria would be the key of the source order, and append all fields to a new record.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It would be helpful to readers, and you, if you provided a description of the "business" your database is intended to support.
    Start at 30,000 ft and gradually add some details.

    Here is a link to a free, generic data model that involves video rentals that shows a tables, relationships and fields/attributes of a typical rental business. There are several free data models available at Barry Williams' site.
    You can add, remove, ignore parts as required. It is intended only as a starting point to review your setup. You can change or read video as audio to suit your needs.

    While a form serves as the user interface, it is the underlying data structure (tables and relationships) that is most critical.
    Can you post a jpg of your tables and relationships?

    Good luck with your project.

  4. #4
    kmorgan is offline Novice
    Windows 8 Access 2016
    Join Date
    May 2018
    Posts
    5
    Hi Orange,

    Thanks for the reply!
    The company supplies audio communications rentals and both sales, the audio systems are typically headset systems hence the serial numbers.
    what im trying to do here with this database is supply a easy to use upgrade on the original asset management database for the rental side of the business.

    The table this form retrieves information from is called the Audio Rentals Database. this is the only table i use and i was wondering if there was any code i could use to create a copy and paste buttons for the "CUSTOMER DETAILS/HIRE" section.

    step by step this is what i planned so you can understand where i am comping from

    open up the form
    find the rental by searching the serial number
    update the "STATUS" on the right
    fill out the details in "CUSTOMER DETAILS/HIRE" section
    click a copy button for those fields
    search the next rental serial number
    update the status
    click a paste button to fill in the
    "CUSTOMER DETAILS/HIRE" section

    I hope this helps understand my dilemma


  5. #5
    kmorgan is offline Novice
    Windows 8 Access 2016
    Join Date
    May 2018
    Posts
    5
    Quote Originally Posted by orange View Post
    It would be helpful to readers, and you, if you provided a description of the "business" your database is intended to support.
    Start at 30,000 ft and gradually add some details.

    Here is a link to a free, generic data model that involves video rentals that shows a tables, relationships and fields/attributes of a typical rental business. There are several free data models available at Barry Williams' site.
    You can add, remove, ignore parts as required. It is intended only as a starting point to review your setup. You can change or read video as audio to suit your needs.

    While a form serves as the user interface, it is the underlying data structure (tables and relationships) that is most critical.
    Can you post a jpg of your tables and relationships?

    Good luck with your project.
    Hi Orange,

    Thanks for the reply!
    The company supplies audio communications rentals and both sales, the audio systems are typically headset systems hence the serial numbers.
    what im trying to do here with this database is supply a easy to use upgrade on the original asset management database for the rental side of the business.

    The table this form retrieves information from is called the Audio Rentals Database. this is the only table i use and i was wondering if there was any code i could use to create a copy and paste buttons for the "CUSTOMER DETAILS/HIRE" section.

    step by step this is what i planned so you can understand where i am comping from

    open up the form
    find the rental by searching the serial number
    update the "STATUS" on the right
    fill out the details in "CUSTOMER DETAILS/HIRE" section
    click a copy button for those fields
    search the next rental serial number
    update the status
    click a paste button to fill in the
    "CUSTOMER DETAILS/HIRE" section

    I hope this helps understand my dilemma

  6. #6
    kmorgan is offline Novice
    Windows 8 Access 2016
    Join Date
    May 2018
    Posts
    5
    Quote Originally Posted by ranman256 View Post
    Run an append query.
    the criteria would be the key of the source order, and append all fields to a new record.
    Hi Ranman,

    I am trying to update existing records so creating new ones wouldn't help here i dont believe?

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show us you table design-- post a jpg.
    What info do you have regarding the original asset management database ?
    That would be helpful, and is often easier to add new functionality rather than create a new, separate database that must be sync'd with the original.

  8. #8
    kmorgan is offline Novice
    Windows 8 Access 2016
    Join Date
    May 2018
    Posts
    5
    Quote Originally Posted by orange View Post
    Please show us you table design-- post a jpg.
    What info do you have regarding the original asset management database ?
    That would be helpful, and is often easier to add new functionality rather than create a new, separate database that must be sync'd with the original.
    The original database is not going to be used going forward. the new one has been made and been populated with information the only thing i need to finish it all off is the copy and paste function which i have described, im not sure if this is the image you're after but here is a jpg of the table the information comes from minus two fields, System Status and Comments/notes.
    Is there any code advice you can supply regarding buttons for the form?
    Click image for larger version. 

Name:	Capture.PNG 
Views:	15 
Size:	22.0 KB 
ID:	34150

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    You maybe should normalize data structure. Conventional approach is to have a Customers table (CustID_PK, CustName, CustAddress, CustPhone). Then the only value saved to Rentals is the value of CustID_PK into CustID_FK.


    If you must repeat the data for each new record during data entry, use code to set DefaultValue property. Review https://www.accessforums.net/showthr...t=defaultvalue

    However, if you are trying to update existing records and really want to duplicate the customer info, then code needs to set global variables or TempVars or enter values into UNBOUND controls. Any of these methods can retain data even when navigating records and code can refer to them to populate record. Post your attempted code for analysis.


    Multiple similar name fields also indicates non-optimal design.


    Recommend no spaces nor punctuation/special characters (underscore only exception) in naming convention. All caps not advised either. Better would be DueBackDate or Due_Back_Date.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-28-2016, 03:17 PM
  2. Replies: 16
    Last Post: 02-06-2013, 03:06 PM
  3. Replies: 5
    Last Post: 08-24-2012, 10:32 AM
  4. Replies: 0
    Last Post: 02-25-2011, 09:40 AM
  5. Copying selected fields to a new record
    By Lyle Bitikofer in forum Forms
    Replies: 0
    Last Post: 12-13-2009, 04:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums