Results 1 to 6 of 6
  1. #1
    demaionewton is offline Novice
    Windows 10 Access 2021
    Join Date
    Sep 2022
    Location
    Raleigh, NC
    Posts
    5

    Angry Access error cloning record unless a msgbox before PasteAppend

    In my access database using VBScript I get an error telling me it cannot Paste the record, UNLESS I include a MsgBox before the PasteAppend command. Then it works fine.
    Can anyone explain why this is the case and what, if anything, I can do to make it work without the MsgBox?

    DoCmd.OpenForm "Cases", , , "Cases.[Case ID]=" & vCloneCase, acFormEdit = 1, acHidden = 1 'Go to the record to copy
    DoCmd.RunCommand acCmdSelectRecord 'Select record to copy
    DoCmd.RunCommand acCmdCopy 'Copy the record
    DoCmd.RunCommand acCmdRecordsGoToNew 'Go to a new record
    DoCmd.RunCommand acCmdSelectRecord 'Select the new record
    MsgBox ("Go on") 'Wait for Access to catch up


    DoCmd.RunCommand acCmdPasteAppend 'Paste the copied record

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    If you go to a new record, then it is already selected?
    Try commenting out the select record line.

    And it is vba not vbscript. They are similar, but not the same.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    demaionewton is offline Novice
    Windows 10 Access 2021
    Join Date
    Sep 2022
    Location
    Raleigh, NC
    Posts
    5
    Good points. I rewrote the code as follows:


    DoCmd.OpenForm "Cases"
    DoCmd.SearchForRecord , "", acFirst, "[Case ID]=" & vCloneCase
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdPasteAppend

    This gets me around the need for the MsgBox mentioned above. However, the resulting PasteAppend does not include a field from the record that is selected, copied, and appended. That field is located on the 2nd tab of the Form.

    Does acCmdCopy only copy the fields on the tab showing? I'd assumed it copied the entire record? Would using OpenTable solve my problem? I want the entire record copied, not just what's on the main form...

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Why so complicated? Make an append query that has criteria for the unique record identifier to correctly select the current record on the form (make sure you do not include that in the append to fields, you want the autonumber to generate a new unique one for the new record) and simply run that from your button.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    field is located on the 2nd tab of the Form
    If by tab you mean you're using a navigation form, then the only form that is open is the one you're looking at. So there's no getting a value from a form that's "on" another navigation form tab.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    add a reference to ms scripting runtime.
    add zzz to the tag property of each control you want to copy, excluding the primary key.
    put the sCopyForm code in a standard module.

    call the sub with

    Code:
    sCopyForm Me
    Code:
    Sub sCopyForm(frm As Form)
    
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strsql As String
        Dim k
        Dim ctl As Control
        
        Dim dict As New Scripting.Dictionary
        
        strsql = frm.RecordSource
    
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(strsql)
    
    
        If rs.BOF And rs.EOF Then
            GoTo MyExit
        End If
        
        For Each ctl In frm.Controls
    
            If ctl.Tag = "zzz" Then
    
                dict.Add ctl.ControlSource, ctl.value
    
            End If
    
        Next
        
        rs.AddNew
    
        For Each k In dict.Keys
    
            rs.Fields(k) = dict(k)
    
        Next
    
        rs.Update
       
    MyExit:
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        Set dict = nothing
    
    End Sub
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

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

Similar Threads

  1. Replies: 8
    Last Post: 01-13-2023, 12:04 AM
  2. msgbox go to new record
    By khayward in forum Modules
    Replies: 2
    Last Post: 07-16-2018, 12:32 PM
  3. Cloning Record on Form/Subform
    By RaycoQA in forum Programming
    Replies: 2
    Last Post: 06-29-2017, 05:19 PM
  4. MsgBox for Error Handling
    By derek7467 in forum Programming
    Replies: 7
    Last Post: 02-13-2014, 07:13 PM
  5. Cloning data to other existing records.
    By GraemeG in forum Programming
    Replies: 0
    Last Post: 02-16-2011, 11:53 AM

Tags for this Thread

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