Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help with Duplicate Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have a Continuous Form with an Duplicate button in the footer.

    The form contains Code, Description, Autonumber (used to attach to other records) and several other fields

    When I set focus on a record and click to Duplicate button the record get duplicated in the bottom of the Continious Form OK

    The operator is then suppose to change at least the Code, Description and possibly some other firlds.

    What VBA code can I use to prevent the operator from leaving the duplicated records before changing the 4 character Code and Decsription?

    Thanks, John

  2. #2
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Duplicate Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Great code Hans

    I didn

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

    Re: Help with Duplicate Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Oops, yes, that was a remnant of the field name in the database I tested in.

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

    Re: Help with Duplicate Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Before Update does not always fire if you move to another record, only if the record you are about to leave has been changed. When you duplicate a record, the duplicate is automatically considered to be changed (since it is new).

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help with Duplicate Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hans

    In the above code should blnDuplicate be set to False in AfterUpdate?

    John

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

    Re: Help with Duplicate Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Edited by HansV to correct two errors - thanks to Support4John for pointing them out

    Here is example code. You can expand/adapt it as needed.

    ' Module-level variables
    Private blnDuplicate As Boolean
    Private strCode As String
    Private strDescription As String

    ' On Click event procedure for command button
    Private Sub cmdDuplicate_Click()
    If Not IsNull(Me.ID) Then ' ID is the primary key field, you can also use another field
    ' Duplicate record
    RunCommand acCmdSelectRecord
    RunCommand acCmdCopy
    RunCommand acCmdPasteAppend
    ' Set variables
    blnDuplicate = True
    strCode = Me.Code
    strDescription = Me.Description
    End If
    End Sub

    Private Sub Form_AfterUpdate()
    ' Reset boolean for next time
    blnDuplicate = False ' originally had True here
    End Sub

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    ' Are we on a duplicate record?
    If blnDuplicate = True Then
    ' Test if Code has been changed
    If Me.Code = strCode Then
    MsgBox "You must change Code!", vbExclamation
    Me.Code.SetFocus
    Cancel = True
    ' Test if Description has been changed
    ElseIf Me.Description = strDescription Then
    MsgBox "You must change Description!", vbExclamation
    Me.Description.SetFocus
    Cancel = True
    End If
    End If
    End Sub

    Note: if Code or Description can be blank, the code will need to be changed.

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

    Re: Help with Duplicate Record? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Yes, thanks. I have corrected my reply, with a note.

Posting Permissions

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