Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    create default values to populate a new form (Access 2000)

    Hi everyone,
    I have a data entry problem, where the data to be entered using a form is nearly the same for each record to be entered, with only 2 fields being changed (including the primary key). I need to be able to duplicate the record (or something along those lines) to populate the next records fields and then insert the new record with the changes. I've tried movelast (VBA code) but I don't want to overwrite the last record, only duplicate most of the fields to create a new record.
    To make it more complicated, the form that is being filled in has two subforms attached to it, linked to three separate tables. The child forms are linked to the Parent form through the parent form's ID number. The two subforms are not linked to each other, although they share the same ID numbers as foreign keys.
    I hope I've made it clear enough.
    Cheers, Sue. <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: create default values to populate a new form (Access 2000)

    In the BeforeUpdate event of the Main Form save all the fields in an array.
    In the AfterInsert event of the Main form, populate all the fields of the Main form and set the focus to the first field of the Main form if required.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: create default values to populate a new form (Access 2000)

    For the mainform, I think the best way to handle this is move the current value of each control into its DefaultValue property. You can do this in the AfterUpdate event of the control or of the form.

    You weren't really clear about what you wanted to do with the subforms. Did you want to duplicate the information in the subforms from the prior record?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: create default values to populate a new form (Access 2000)

    Before we get too involved in solutions, can you clarify something for me please? Are the values in all but the two fields ALWAYS the same, or are they nearly always the same as the preceding record that was just entered? If it's the former, setting the default value for each control, or actually setting it at the table level would do the trick. If it's the latter, then you have a larger challenge - the solution of setting it from an array as suggested would work, or you could use the keyboard short-cut CTRL-APOSTROPHE which populates the value from the previous record in an automated fashion using SEND Keys or something similar on entry to a field.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: create default values to populate a new form (Access 2000)

    Hello Sue,

    You might have a look at the thread that starts at <!post=post 138575, 138575>post 138575<!/post>. The replies mention several methods for duplicating values to a new record.

    Regards,
    Hans

  6. #6
    New Lounger
    Join Date
    Jul 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create default values to populate a new form (Access 2000)

    in answer to Wendell's question, yes I do want to re-create the records in the subforms.

    In answer to the other question, the values can change in all fields from time to time, just that most of the time for the data that is to be entered, the values don't change, the user feels they should be able to simply check the data from the last record, press a button and voila! - the record is added with the small changes.

    Thanks for the suggestions, I'll let you know how they go.
    Sue. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: create default values to populate a new form (Access 2000)

    Well done, Hans.
    I looked at your AutoFill form, very smart. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>
    I was wrong about the AfterInsert event <img src=/S/blush.gif border=0 alt=blush width=15 height=15>, it should have been the AfterUpdate event (what Gary said), anyway Hans solution would be my choice.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    nb. Sorry Mark, I meant you not Gary

  8. #8
    New Lounger
    Join Date
    Jul 2002
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create default values to populate a new form (Access 2000)

    Thanks Hans, that code was excellent. However, any suggestions on how to autofill the subforms as well that I mentioned previously? The subform can have up to 3 records relating to the parent form's ID number, and I'd like to be able to have all 3 filled in . <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    thanks again, Sue.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: create default values to populate a new form (Access 2000)

    Hello Sue,

    If you just wanted to set the default values for new records in the subforms too, I suppose you could have extended the approach suggested in my previous reply to the subforms. But I gather that you actually don't want to do that, but instead, you want to copy the existing records in the subforms. In that case, I think you'd be better off with a command button that
    <UL><LI>Copies the current record in the main form to a new record.
    <LI>Copies the corresponding records in the subforms, setting the link field to the primary key value of the new main record.
    <LI>Makes the new record current in the main form.[/list]You can use DAO for this; perhaps ADO too but I have very little experience with that.

    Regards,
    Hans

Posting Permissions

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