Results 1 to 2 of 2
  1. #1
    nika.duncan is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    36

    Trying to create duplicates but record overwrites first record in table

    Hi All,



    First time on this forum and fairly new to programming in Access. I am working with a sub-form where once a staff member enters there sub measure I would want to create a duplicate of that record. The problem I am having is that once you enter the sub-form and click the duplicate button it creates a duplicate of the record selected but overwrites the first record in the table. I want it to create a new SubMeasure Number which is the primary key and assigns the record the next available number.

    Also if I try to add another record after one has been added I get runtime error "3021" - No current record. I would have to close the form and reopen for it to be able to add again.

    Please if anyone can assist because I am not sure what must be done to eliminate the problem.
    I have attached the code below

    Code:
    Private Sub cmdDuplicate_Click()Dim dbs As DAO.Database, Rst As DAO.Recordset
    Dim F As Form
    
    
    'Return Database variable pointing to current database
    Set dbs = CurrentDb
    
    
    Set Rst = Me.RecordsetClone
    
    
    If Me.Dirty Then
    Me.Dirty = False
    End If
    'On Error GoTo Err_cmdDuplicate_Click
    
    
    'Tag property to be used later by the append query.
    Me.Tag = Me![MeasureID]    'Links Main form to sub-form
    
    
    'Add new record to the end of Recordset object
    
    
    With Rst
    .Edit
    !SubMeasureName = Me!SubMeasureName
    !SubMeasureDesp = Me!SubMeasureDesp
    !SubMeasureScore = Me!SubMeasureScore
    !SubMeasureWeight = Me!SubMeasureWeight
    !SubMeasureTotal = Me!SubMeasureTotal
    
    
    
    
    
    
    .Update
    
    
    .Move 0, .LastModified
    
    
    End With
    
    
    Me.Bookmark = Rst.Bookmark
    'Run the Duplicate Measure Detail append query which will
    'selects all detail records that have the MUserLoginID stored
    'in the form's Tag property and appends the back to the detail table
    'with the MUserLoginID of the duplicated main form record
    
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Duplicate Measure Detail"
    DoCmd.SetWarnings True
    
    
    'Requery the subform to display the newly appended records.
    Me.Requery
    
    
    
    
    
    
    Exit_cmdDuplicate_Click:
    Exit Sub
    
    
    Err_cmdDuplicateClick:
    MsgBox Error$
    Resume Exit_cmdDuplicate_Click:
    
    
    Me.SubMeasure = Nz(DMax("SubMeasure", "tblSubMeasure"), 0) + 1  'Suppose to find next available number and assign to record
    
    
    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sounds like you want .AddNew instead of .Edit.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-15-2013, 09:58 AM
  2. Replies: 12
    Last Post: 10-27-2012, 05:44 AM
  3. Replies: 5
    Last Post: 08-21-2012, 12:30 PM
  4. Create one record for every field in another table
    By sailngsue in forum Programming
    Replies: 6
    Last Post: 09-27-2011, 03:57 PM
  5. Create PDF for each record in table/form
    By ChrisCMU in forum Forms
    Replies: 15
    Last Post: 07-28-2009, 01:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums