Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2009
    Posts
    11

    Unanswered: How do you duplicate certain information on a form?

    Hello Access Experts,

    I would like to duplicate a record in my form to alleviate repetitive key strokes. I use the duplicate record wizard, but it duplicates all of the record data. Is there some way to duplicate certain information and have other fields blank?

    For example, I have a form (InputForm) and have 6 fields on it, Field1, Field2, Field3, Field4, Field5 and Field6. I would like to duplicate Fields1, 2, 3 and have Fields 4, 5 and 6 blank.

    Appreciate your time.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Do you mean to duplicate a form or to duplicate a record being displayed by a form (that is to say, use one record as a template for another)?
    Me.Geek = True

  3. #3
    Join Date
    Sep 2008
    Posts
    150
    Yes. I would like to duplicate a record from a form. Sorry.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    I'd have a button on the form where the data is being entered. On the OnClick event, depending if it's just a couple of fields being copied once, I'd just do a couple of dlookup()'s into the fields that need to be filled.
    Me.Geek = True

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    And how would you know which record to pull the data from with the DLookups?

    In your form, you can use the AfterUpdate event of the control holding your data to set the DefaultValue for the field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each new record.

    Code:
    Private Sub YourControlName_AfterUpdate()
       Me.YourControlName.DefaultValue = """" & Me.YourControlName.Value & """"
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think Missinglinq way is the best but another way is to have an "Add New Record" button which stores the values into variables before it goes to the new record and then copies those variable values into the fields. Again though, Missinglinq way is the best approach I've used.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    I was assuming it was a once-off copy (clone record B to be mostly like record A) where you could use an input box or a more sophisticated method to let the user pick record A. But your method sounds pretty good too.
    Me.Geek = True

  8. #8
    Join Date
    Sep 2008
    Posts
    150
    I’m not sure if I explained myself correctly. I would like to duplicate certain fields in a new record. I use the “Duplicate Record” wizard, but the wizard copies ALL of the fields. I would like just Fields 1, 2 and 3 copied when going to the next record and have Fields 4, 5 and 6 show-up blank.

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Then use the code I posted for each field that you want to "carry forward."
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Sep 2008
    Posts
    150
    Hello Missingling,

    I am so sorry, I overlooked your original reply.

    I applied your code and it works GREAT for the duplication of the data I needed. The problem now is if I use the duplicate function a number of times and choose to add a new record, my new record function now acts like the duplicate function.

    Here is the code I modified for the Duplicate button.

    <START DUPLICATE CODE>
    Private Sub Duplicate_Input_Record_Click()
    On Error GoTo Err_Duplicate_Input_Record_Click

    'Duplicate Fields
    Me.ServiceDate.DefaultValue = """" & Me.ServiceDate.Value & """"
    Me.ServiceTime.DefaultValue = """" & Me.ServiceTime.Value & """"
    Me.Location.DefaultValue = """" & Me.Location.Value & """"
    Me.Server.DefaultValue = """" & Me.Server.Value & """"

    ‘Add New Record
    DoCmd.GoToRecord , , acNewRec

    Exit_Duplicate_Input_Record_Click:
    Exit Sub

    Err_Duplicate_Input_Record_Click:
    MsgBox Err.Description
    Resume Exit_Duplicate_Input_Record_Click

    End Sub
    <END DUPLICATE CODE>

    I am using the standard Wizard code for a New Record.

    <START NEW RECORD CODE>
    Private Sub Add_New_Record_Input_Form_Click()
    On Error GoTo Err_Add_New_Record_Input_Form_Click

    DoCmd.GoToRecord , , acNewRec

    Exit_Add_New_Record_Input_Form_Click:
    Exit Sub

    Err_Add_New_Record_Input_Form_Click:
    MsgBox Err.Description
    Resume Exit_Add_New_Record_Input_Form_Click

    End Sub
    <END NEW RECORD CODE>

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That's because the code for new record doesn't reset the Default Values back to standard.

    I am using the standard Wizard code for a New Record.
    Ughh... I hate looking at the machine generated code. Wizards are not a great way to start using VBA.

    Code:
    Private Sub Duplicate_Input_Record_Click()
       'Set default values.
       Me.ServiceDate.DefaultValue = """" & Me.ServiceDate.Value & """"
       Me.ServiceTime.DefaultValue = """" & Me.ServiceTime.Value & """"
       Me.Location.DefaultValue = """" & Me.Location.Value & """"
       Me.Server.DefaultValue = """" & Me.Server.Value & """"
    
       'Goto New Record
       DoCmd.GoToRecord , , acNewRec
    End Sub
    Code:
    Private Sub Add_New_Record_Input_Form_Click()
       'Reset Defaults
       Me.ServiceDate.DefaultValue = ""
       Me.ServiceTime.DefaultValue = ""
       Me.Location.DefaultValue = ""
       Me.Server.DefaultValue = ""
       DoCmd.GoToRecord , , acNewRec
    End Sub
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Sep 2008
    Posts
    150
    Hello StarTrekker,

    I used your code and it gives me an error that I cannot go to the next record when I click on both the "Add Record" and Duplicate Rec" buttons.

    Also receive another error that the field “server_start_time” cannot obtain a Null value because the Required property for this field is set to True.

    I get this error message before tabbing over to the “server_start_time” field.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Perhaps you already are at a new record, which is why you are getting the errors from the code.

    Add error handling or ignore errors with On Error Resume Next in those subroutines.

    The other message is about the fact that the specified field has the Required property set to true and, probably, the Default Value of "" is messing with that. Not sure what you want to do about that, it's your choice. Either turn off Required or set more appropriate default values.

    HTH
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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