Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2012
    Posts
    7

    Unanswered: copy certain data to new record on form?

    My apologies if this has been covered… I suspect it has, but my searches haven’t turned up quite what I’m looking for.

    The database I’m working on will be used by account managers & sales folks to request small samples of our products for potential customers to test. The primary form they will interface with contains:

    2 – Auto-populated text boxes (“today’s date”, “request #”: indexed field, autonumbered)

    9 – User-populated text boxes. 1 is optional, the other 8 require an entry (validation rule IS NOT NULL).

    9 – User populated combo boxes. All 9 require entry & have row source value lists built into their properties. 7 are restricted to those value lists, 2 will also accept free text.

    3 – Command buttons. One to save the current request, one to exit the database, and one to start a new request (blank request form, new record).

    I want to add another command button that will start a new request, but copy data from the current request in some of the text boxes. The idea is to make it unnecessary for the requestor to type out the customer name & shipping address more than once in cases where several product samples will be going to the same customer.

    Attempted to do this by using an append query that pulled the fields I wished to copy. Set up a macro to run the append query, then go to the next record. Not sure why, but it never would go to that next record. The append query had done it’s job, and a new record was created with the correctly copied data. But the form had to be closed & re-opened to show it.
    So I added steps in the macro to close & re-open the form. This caused validation rule errors, since the appended record was blank in some required fields.

    Am I missing something in my macro setup that will allow it to function as intended, or will this task need to be written in VBA? Certainly willing to do so, but please be gentle… my coding skills are very, very primitive.

    Thanks!

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Have you tried adding a requery step to the macro after you run the append query?

    Steve

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Here's one approach that will leave you on the newly created Record:

    Code:
    Private Sub CopyPartialRecordButton_Click()
    
    Dim MyFirstField As String
    Dim MySecondField As String
    Dim MyThirdField As String
    
     'Copy fields to variables 
      MyFirstField = Me.FirstField
      MySecondField = Me.SecondField
      MyThirdField = Me.ThirdField
    
     'Go to a new record
      DoCmd.GoToRecord , , acNewRec
    
     'Reverse the process and plug old values into new record
      Me.FirstField = MyFirstField 
      Me.SecondField = MySecondField
      Me.ThirdField = MyThirdField
    
    End Sub


    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Aug 2012
    Posts
    7
    Thanks so much to both of you for the suggestions! Going to give them a shot this afternoon.

    I can see that if I'm going to do much database building (& it appears that's exactly what's going to be asked of me), some formal training in VBA coding will be in order.

  5. #5
    Join Date
    Aug 2012
    Posts
    7
    Missinglinq, your suggestion is working well, many thanks! A few of the fields are still giving me trouble though, due to what I now know to be a huge rookie mistake... I gave them names with spaces. Tried underscoring the spaces- no dice. Tried bracketing the field name- negatory. Tried changing the field name in the table, then browsing through every query, form, report, & macro to update any links to that field- nyet. Guessing that I will need to go through all the code to ferret out any references to the original field name(s) & update them...

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The Access Gnomes don't play horseshoes...close doesn't count! Names have to be exact, and you have to be aware of what requires the Control names and what requires Field names!

    Access itself makes this difficult, because it you use the Form Wizard, or go to the Available Fields list and move a Field to the Form, Access will name them Control the same as the Field! Most experienced developers change this immediately, if only adding txt to the Field name, so MyFieldName becomes txtMyFieldName.

    You can use the Search feature, from the Code Module of any Form, and using the Current Project option, to find identical or near identical (by only entering part of the name) but things like Fields in Queries will not, I don't think, be found.

    I would also go to any Code Module and go to Debug on the Menu and click on Compile YourDatabaseName. This will often find object names of objects that do not or no longer exist.

    Good Luck!

    Linq ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Aug 2012
    Posts
    7
    Access Gnomes indeed...

    Was able to weed out obsolete references, not too bad after compacting & compiling. Working exactly as envisioned, big time THANKS!!!

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help! But a word to the wise...don't speak too loudly of the Access Gnomes! They can be very touchy...and you don't want them to get angry at you!

    ;0)>
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Aug 2013
    Posts
    1
    Bumping up an old thread, as this answered my question, but I need some further help.

    I'm trying to do exactly what the OP is - create a command button that will copy selected fields in the form to a new record.

    I've re-created Missinglinq's code, and this works, except for one problem.

    I'm getting an error message sometimes:
    Run time error '94':
    Invalid use of Null

    This seems to be happening because some of the fields I am trying to copy are blank - can I add something to the code to tell it to skip any null fields?

    Thanks

Posting Permissions

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