Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    New record - Move data from one field to another in new record

    I am completely new to access. I have been asked to modify an existing database to do the following. Our managers use a form, connected to a table to enter new data logged on our system. I wish to create a button on the form that will create a new record in the database, and will move certain fields to others (i.e. two columns exist that represent 'previous hours on system', and 'new hours on system' for various equipment that we work with; when a new record is created, I wish to carry the data from the 'new hours on system' fields from the previous record to the 'previous hours on system' fields to the newly created record).

    I attempted to use the duplicate record VBA code to first duplicate the record, then assigning Me.NewFieldName = Control Source (of data desired to be imported to new field); and finally, assigning Me.ImportedFieldName = "". That didn't work; also, it requires the user to select the record one wishes to duplicate, then overwrites the previous value in the already existing record. I'm trying to avoid having to duplicate an existing record. I simply want to create a new record and transfer some fields to different fields in the new record.

    Thanks in advance!
    Last edited by AccessNoobz; 2014-10-21 at 21:31.

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    This should set you on the right path: http://allenbrowne.com/ser-24.html

  3. #3
    New Lounger
    Join Date
    Oct 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried something similar to this earlier, but I was trying to include a line that would set the field Me.[Hours On Cathodes] = the string value of [E Cathode Hours], which is a calculated field. I kept getting errors when trying to run.

  4. #4
    New Lounger
    Join Date
    Oct 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So I was able to get a little further in achieving what I was attempting to accomplish, but ran into another issue. Here is the sample code:
    Code:
    Private Sub CopyRecord_Click()
    
    On Error Resume Next
    
    Dim v1 As Variant
    
    v1 = Me![E-Hours On Cathodes].Value
    
    DoCmd.GoToRecord , , acNewRec
    
    Me![S-Hours On Cathodes] = v1
    
    End Sub
    With this, I click on the record I want to copy, then click the button. It creates a new record with data I wanted to copy, but it doesn't allow for any new records after this one is created. I attached an image of what the record table looks like when I run this, and what it should look like. Any ideas? Thanks.

    record_error.jpg
    Last edited by RetiredGeek; 2014-10-23 at 21:49. Reason: Added Code Tags

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    A.N.,

    You're ending in Edit Mode (designated by the pencil icon in the left column).

    Instead of DoCmd try:
    Code:
       Dim v1 As Variant
       Dim sql As String
       
       v1 = Me![E-Hours On Cathodes].Value
       
       sql = "INSERT INTO Record ([E-Hours On Cathodes]) VALUES (" &  v1  & ")"
       
       DoCmd.RunSQL sql
    
       Me.ReQuery
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    New Lounger
    Join Date
    Oct 2014
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much! This works! Appreciate all the help!

Tags for this Thread

Posting Permissions

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