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

    Unable to modify certain fields after a duplicate record has been created

    Good day All,


    This database was created to assess each staff member on how they deliver customer service whether internally or externally. Certain strands were created to access each department. After each strand was created and added to the table the idea was to duplicate the record and modify the scores, date and if neccessary position and department.

    The issues faced is that once the duplicate is created I am not able to:
    1. Change the date - once I try to update the date field it doesn't accept it
    2. If I change the position for the staff it brings up a new table to be updated with the criteria


    The code to create the duplicate is below not sure what I'm missing. I will try to attach the database after I've compress it to the required 2mb.



    Code:
    Private Sub cmdDuplicateData_Click()
    
    On Error GoTo Err_cmdDuplicateData_Click
    Me.AllowEdits = True
    
    
    Dim strSql As String
    Dim lngID As Long
    
    
    'Save any Edits First
    
    
    If Me.Dirty = False Then
        Me.Dirty = False
    End If
    
    
    'Make sure there is a record to duplicate
    
    
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate. "
    Else
        
    'Duplicate the main record:  add to form's clone
    
    
    With Me.RecordsetClone
        .AddNew
        !StaffID = Me!StaffID
        !StaffName = Me!StaffName
        !DepartmentName = Me!DepartmentName
        !StaffPosition = Me!StaffPosition
        !StaffBDate = Me!StaffBDate
        !StaffEDate = Me!StaffEDate
    .Update
    '.AllowEdits
    
    
    'Save the primary key value, to use as the foreign key for the related records.
    
    
    .Bookmark = .LastModified
    lngID = !StaffApraisedID
    
    
    'Duplicate the related records: append query
    
    
    If Me.frmMeasureDetail.Form.RecordsetClone.RecordCount > 0 Then
        strSql = "INSERT INTO [tblMeasure] (StaffApraisedID, MUserLoginID, MeasureName, MPositonName, MeasureScore, MeasureWeight, MeasureTotal, MeasureDesc)" & _
        "SELECT " & lngID & " As NewID, MUserLoginID, MeasureName, MPositonName, MeasureScore, MeasureWeight, MeasureTotal, MeasureDesc " & _
        "FROM [tblMeasure] WHERE StaffApraisedID = " & Me.StaffApraisedID & ";"
        DBEngine(0)(0).Execute strSql, dbFailOnError
    Else
        MsgBox "Main record duplicate, but there were no related records."
    End If
    
    
    'Display the new duplicate.
    Me.Bookmark = .LastModified
    End With
    
    
    End If
    
    
    Exit_cmdDuplicateData_Click:
    
    
    Exit Sub
    
    
    Err_cmdDuplicateData:
        MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDuplicateData_Click"
        Resume Exit_cmdDuplicateData_Click
        
    
    
    
    
    On Error GoTo Err_cmdDuplicateData_Click
    
    DoCmd.GoToRecord , , acLast
    
    Exit Sub
    Err_cmdDuplicateData_Click:
    MsgBox Error$
    Resume Exit_cmdDuplicateData_Click:
    
    
    End Sub
    
    
    
    
    End Sub


    Any help is welcomed!!
    Nika

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If Me.Dirty = False Then
    Me.Dirty = False
    End If
    The above code should be
    Code:
    If Me.Dirty = TRUE Then
        Me.Dirty = False
    End If
    or even better
    Code:
    If Me.Dirty Then
        Me.Dirty = False
    End If


    I see you are using code from Allen Browne's site. (http://allenbrowne.com/ser-57.html)
    The problem I see is that, according to Access 2010 Help, the RecordsetClone Property is read only. (I haven't tested this)
    According to other reading I have done, trying to add a record to the RecordsetClone doesn't affect the form recordset.


    You might want to try and adapt the code from the link below.

    ACC2000: How to Duplicate a Main Form and Its Subform Detail Records
    http://support2.microsoft.com/?kbid=208824


    However, I would change this part of the code in the above example
    Code:
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Duplicate Order Details"
    DoCmd.SetWarnings True
    to an inline query as in Alllen's code. I am not a big fan of using "SetWarnings False".

  3. #3
    nika.duncan is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    36
    I have used the suggested code in the link below. It duplicates the record in the Main Form "Copy of frmUserDetailsInfo" but the details in the subform frmMeasureDetail" are not been duplicated. Once you click the "Duplicate Record" button a message pops up asking to enter the parameter value for "Forms!frmMeasureDetail!StaffApraisedID" it then creates a duplicate record on the "Copy of frmUserDetailsInfo" but not the measures on the sub form.
    The query in SQL view and the code is inserted below.

    HELP PLEASE!!!!!!!

    Code:
    INSERT INTO tblMeasure ( MUserLoginID, MainMeasureID, MeasureName, MPositonName, MeasureScore, MeasureWeight, MeasureTotal, MeasureDesc, StaffApraisedID )SELECT tblMeasure.MUserLoginID, tblMeasure.MainMeasureID, tblMeasure.MeasureName, tblMeasure.MPositonName, tblMeasure.MeasureScore, tblMeasure.MeasureWeight, tblMeasure.MeasureTotal, tblMeasure.MeasureDesc, CLng([Forms]![frmMeasureDetail]![StaffApraisedID]) AS NewStaffApraisedID
    FROM tblMeasure
    WHERE (((tblMeasure.StaffApraisedID)=[Forms]![frmMeasureDetail]![StaffApraisedID]));
    Code:
    Private Sub cmdDuplicateData_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
    
    
    On Error GoTo Err_cmdDuplicateData_Click
    
    
    'Tag property to be used later by the append query.
    Me.Tag = Me![StaffID]
    
    
    'Add new record to end of Recordset object.
    With Rst
        .AddNew
            !StaffID = Me!StaffID
            !StaffName = Me!StaffName
            !DepartmentName = Me!DepartmentName
            !StaffPosition = Me!StaffPosition
            !StaffBDate = Me!StaffBDate
            !StaffEDate = Me!StaffEDate
        .Update                 'Save changes.
        .Move 0, .LastModified
    End With
    Me.Bookmark = Rst.Bookmark
    
    
    'Run the Duplicate StaffAprraised Details append query which selects all detail records that have the StaffID stored in the Form's
    'Tag property and appends them back to the detail table with the StaffID of the duplicate main form record.
    
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Duplicate StaffAppraised Details"
    DoCmd.SetWarnings True
    
    
    'Requery the subform to display the newly appended records.
    Me!StaffApraisedID.Requery
    
    
    
    
    Exit_cmdDuplicateData_Click:
    
    
    Exit Sub
    
    
    Err_cmdDuplicateData_Click:
    MsgBox Error$
    Resume Exit_cmdDuplicateData_Click:
    
    
    End Sub
    Quote Originally Posted by ssanfu View Post
    The above code should be
    Code:
    If Me.Dirty = TRUE Then
        Me.Dirty = False
    End If
    or even better
    Code:
    If Me.Dirty Then
        Me.Dirty = False
    End If

    I see you are using code from Allen Browne's site. (http://allenbrowne.com/ser-57.html)
    The problem I see is that, according to Access 2010 Help, the RecordsetClone Property is read only. (I haven't tested this)
    According to other reading I have done, trying to add a record to the RecordsetClone doesn't affect the form recordset.


    You might want to try and adapt the code from the link below.

    ACC2000: How to Duplicate a Main Form and Its Subform Detail Records
    http://support2.microsoft.com/?kbid=208824


    However, I would change this part of the code in the above example
    Code:
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Duplicate Order Details"
    DoCmd.SetWarnings True
    to an inline query as in Alllen's code. I am not a big fan of using "SetWarnings False".

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In the append query, it looks like step 11 was missed. I don't see "Forms!YourformName.TAG" in the SQL.

    Can you post your dB..... just a few records are needed for testing. Change any sensitive data.
    Compact And Repair, then zip it.

  5. #5
    nika.duncan is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Nov 2013
    Posts
    36
    I have attached the dB with just a few records for you to have a look. Appreciate the effort!!

    Quote Originally Posted by ssanfu View Post
    In the append query, it looks like step 11 was missed. I don't see "Forms!YourformName.TAG" in the SQL.

    Can you post your dB..... just a few records are needed for testing. Change any sensitive data.
    Compact And Repair, then zip it.
    Attached Files Attached Files

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I *finally* got the code to copy the child records. I had to almost disable the login form to be able to run the code.

    Here is the duplicate data code. I couldn't get the saved append query to insert the child records, so I moved the query into the code. (The query "Duplicate StaffAppraised Details" is not being used.)
    Code:
    Private Sub cmdDuplicateData_Click()
        On Error GoTo Err_cmdDuplicateData_Click
    
        Dim dbs As DAO.Database, Rst As DAO.Recordset
        Dim sSQL As String
        Dim OldID As Long
        Dim NewID As Long
        '    Dim F As Form
    
        'Return Database variable pointing to current database.
        Set dbs = CurrentDb
        Set Rst = Me.RecordsetClone
    
        'Tag property to be used later by the append query.
        OldID = Me![StaffApraisedID]
    
        'Add new record to end of Recordset object.
        With Rst
            .AddNew
            !StaffID = Me!StaffID
            !StaffName = Me!StaffName
            !DepartmentName = Me!DepartmentName
            !StaffPosition = Me!StaffPosition
            !StaffBDate = Me!StaffBDate
            !StaffEDate = Me!StaffEDate
            .Update                 'Save changes.
            .Move 0, .LastModified
        End With
        Me.Bookmark = Rst.Bookmark
    
        'get new ID
        NewID = Me.StaffApraisedID
    
        'Run the Duplicate Order Details append query which selects all detail records that have the StaffID stored in the Form's
        'Tag property and appends them back to the detail table with the StaffID of the duplicate main form record.
        sSQL = "INSERT INTO tblMeasure ( MUserLoginID, MeasureName, MPositonName, MeasureScore,"
        sSQL = sSQL & " MeasureWeight, MeasureTotal, MeasureDesc, StaffApraisedID )"
        sSQL = sSQL & " SELECT tblMeasure.MUserLoginID, tblMeasure.MeasureName, tblMeasure.MPositonName, tblMeasure.MeasureScore,"
        sSQL = sSQL & " tblMeasure.MeasureWeight, tblMeasure.MeasureTotal, tblMeasure.MeasureDesc, " & NewID & " AS NewID"
        sSQL = sSQL & " FROM tblMeasure"
        sSQL = sSQL & " WHERE tblMeasure.StaffApraisedID = " & OldID & ";"
        'Debug.Print sSQL
        dbs.Execute sSQL, dbFailOnError
    
        'Requery the subform to display the newly appended records.
        Me.frmMeasureDetail.Requery
    
    Exit_cmdDuplicateData_Click:
        't.Close
        'Set t = Nothing
        Set Rst = Nothing
        Set dbs = Nothing
        Exit Sub
    
    Err_cmdDuplicateData_Click:
        MsgBox Error$
        Resume Exit_cmdDuplicateData_Click:
    
    End Sub
    I also added a line (in blue) to the "Select Staff Combo" to force the calculation of the scores/weights in the subform
    Code:
    Private Sub Combo24_AfterUpdate()
        Dim rs As DAO.Recordset
    
        If Not IsNull(Me.Combo24) Then
            'save before move
    
            If Me.Dirty Then
                Me.Dirty = False
            End If
    
            'search in clone set
            Set rs = Me.RecordsetClone
            ' rs.FindFirst "[StaffApraisedID]= """ & Me.Combo24 & """"
            rs.FindFirst "[StaffApraisedID]= " & Me.Combo24 & ""
    
            If rs.NoMatch Then
                MsgBox "No Record Found"
            Else
    
                'Display the found record in the form
                Me.Bookmark = rs.Bookmark
                
                Me.frmMeasureDetail.Requery
            End If
    
            Set rs = Nothing
        End If
    
        Me.Combo24 = ""
        Me.Combo24.SetFocus
    End Sub
    There are many things that I saw that should be/need to be fixed in the dB.
    I would say the main thing is the structure. There is a table for users, but the userID AND the name is being stored. "Tbl-Users" is related to "UserDetails" by a text field (StaffName), not the UserID PK.

    Each module should have "Optioin Explicit" as the 2nd line of the module. when I added the line to every module and tried to do a Compile, the compiler reported there were undefined variables in the code.


    "StaffID" and "StaffApraisedID" are autonumbers and are being used as identifiers on the forms. Autonumbers are not intended to be viewed/used by end users of the application.

    See :
    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Use Autonumbers properly
    http://access.mvps.org/access/general/gen0025.htm



    Not tryig to slam you, just things I see that (IMO) should be fixed to have a more reliable dB.

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

Similar Threads

  1. Modify D/T for Multiple Fields in the Record
    By Murphy0417 in forum Access
    Replies: 8
    Last Post: 07-02-2014, 11:40 AM
  2. Replies: 1
    Last Post: 06-18-2014, 04:08 AM
  3. Unable to create duplicate record on subform
    By nika.duncan in forum Programming
    Replies: 9
    Last Post: 12-02-2013, 02:26 PM
  4. Replies: 6
    Last Post: 02-21-2012, 03:34 PM
  5. Replies: 9
    Last Post: 01-06-2011, 01:22 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