Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Zero Values on New Copied Record? (Access2002)

    I have a main form with a subform in it. I have a "Duplicate Record" button on the subform and it works fine except ideally, I'd like for it to zero out the values on some of the fields in the NEW record. I don't have a clue on the VBA code but I am making a blind stab at it and tried something like "Me.DataSetChange = FieldName = "" and, although it doesn't error out, it doesn't seem to do anything either. I'd appreciate any guidance! Thanks in advance!!

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

    Re: Zero Values on New Copied Record? (Access2002)

    There are at least a couple of ways to do this. One is to move the subform to a new record through code and then populate the fields with whatever values you want. It sounds like you have some code running behind the Duplicate Record button when you click it. It would help to post that code. A second approach is to use either ADO or DAO to add a new record with a VBA procedure, and then refresh the subform. In either case you can specify default values for any field either at the table level or on the subform.
    Wendell

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

    Re: Zero Values on New Copied Record? (Access2002)

    Did you create the "Duplicate Record" button using the Wizard? If so, the On Click event procedure for the button will look more or less like this:

    <code>Private Sub cmdDuplicate_Click()</code>
    <code> On Error GoTo Err_cmdDuplicate_Click</code>

    <code> DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70</code>
    <code> DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70</code>
    <code> DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append</code>

    <code>Exit_cmdDuplicate_Click:</code>
    <code> Exit Sub</code>

    <code>Err_cmdDuplicate_Click:</code>
    <code> MsgBox Err.Description</code>
    <code> Resume Exit_cmdDuplicate_Click</code>
    <code>End Sub</code>

    You can add lines to set fields to Null (blank, empty) or to 0 (zero) immediately above the Exit_..._Click: line.

    Note: the DoCmd.DoMenuItem code produced by the wizard is antiquated. It can be replaced by more readable RunCommand instructions. The code would look like this:

    <code>Private Sub cmdDuplicate_Click()</code>
    <code> On Error GoTo Err_cmdDuplicate_Click</code>

    <code> RunCommand acCmdSelectRecord</code>
    <code> RunCommand acCmdCopy</code>
    <code> RunCommand acCmdPasteAppend</code>

    <code> ' Clear some fields.</code>
    <code> Me.Field1 = Null</code>
    <code> Me.Field2 = Null</code>

    <code>Exit_cmdDuplicate_Click:</code>
    <code> Exit Sub</code>

    <code>Err_cmdDuplicate_Click:</code>
    <code> MsgBox Err.Description</code>
    <code> Resume Exit_cmdDuplicate_Click</code>
    <code>End Sub</code>
    <code></code>

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero Values on New Copied Record? (Access2002)

    Thanks WendellB and Hans!! I've taken Hans' approach as it seems to be the simplest for me and Hans it worked great! My only other problem is that I would like to have the new record keep the focus and I can't figure out how to do that. The subform is based on a query and I don't have the query sorting. Any ideas?

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

    Re: Zero Values on New Copied Record? (Access2002)

    What type of subform do you have: a single form, a continuous form or a datasheet form? See if adding a line

    RunCommand acCmdRecordsGoToLast

    after the RunCommand acCmdPasteAppend line does what you want.

  6. #6
    New Lounger
    Join Date
    Feb 2003
    Location
    Texas, USA
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zero Values on New Copied Record? (Access2002)

    The subform is a single form. I did add that line of code after the PasteAppend code and it works great!! Thanks for all your help - AGAIN!! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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