Results 1 to 9 of 9
  1. #1
    pcli is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    4

    Copy Entire Record

    Hi,

    I am curious if we can copy the entire last record in a table with a macro. Can anyone help?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Macro can't do that directly. Macro can run an Access append (INSERT SELECT syntax) query object with the OpenQuery method.

    VBA can execute an SQL INSERT SELECT statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pcli is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    4
    Hi June,

    I'm working with web forms and Sharepoint so I can't use vba or append queries (a total pain). The system limits me to using macros which is pretty inflexible (In addition, its not the full suite of macro options as in the client macros.) I've tried doing a "for" loop, matching the last pkey with the attribute in the field and importing each attribute over in a data macro but it puts the entire site in an invalid state. I'm including the code below (its in XML format):

    Code:
    <?xml version="1.0" encoding="UTF-16" standalone="no"?>
    <DataMacros xmlns="http://schemas.microsoft.com/office/...on"><DataMacro Name="eqAdd">
    <Parameters>
        <Parameter Name="maxCC" Description="Max ID of [CC]"/>
    </Parameters>
    <Statements>
    <ForEachRecord>
        <Data Alias="aEQ">
             <Reference>equipment</Reference>
             <WhereCondition>[equipment].[id]=[maxCC]-1</WhereCondition>
        </Data>
        <Statements>
             <CreateRecord>
                 <Data>
                    <Reference>equipment</Reference>
                </Data>
                <Statements>
                    <Action Name="SetField">
                        <Argument Name="Field">dt</Argument>
                        <Argument Name="Value">[aEQ].[dt]</Argument>
                    </Action>
                </Statements>
             </CreateRecord>
         </Statements>
    </ForEachRecord>
    </Statements>
    </DataMacro>
    </DataMacros>
    I cut a bit of the repetitive sections out, but the structureis the same.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That is entirely beyond my experience and outside my comfort zone to advise. Maybe there is a Sharepoint forum somewhere you should post question on.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    pcli is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    4
    Thanks for trying anyway!

    Happy Friday!

  6. #6
    zinrey is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    8
    Pli - you can do this with a 'Data Macro'. This is the structure:

    Lookup record
    If record found then
    createnewrecord
    Set field
    Set next field
    end createnewrecord
    endif
    End LookupRecord

    Click image for larger version. 

Name:	CopyEntireRecord.jpg 
Views:	38 
Size:	70.3 KB 
ID:	7343

    You need to call it from a standard macro and provide it with the parameters. Hope the image makes it clear.

  7. #7
    pcli is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    4
    Thanks for the reply, we're having SharePoint issues atm but your solution will be great for reference. Thanks again!

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Oh, forgot about the new Data Macros in Access 2010. Not familiar with their capability and I have never used macros anyway. Never built a web-based db.

    The suggestion does look promising. Good Luck.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    zinrey is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    8
    It is working for me. Let me know if it is of help and you get it working for your application. Thanks

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

Similar Threads

  1. Replies: 3
    Last Post: 04-01-2012, 01:11 PM
  2. Replies: 3
    Last Post: 09-18-2011, 03:46 PM
  3. Replies: 1
    Last Post: 07-25-2011, 09:41 AM
  4. Copy old record data to new record
    By Aussie58 in forum Access
    Replies: 12
    Last Post: 07-20-2011, 02:30 PM
  5. Update single record via forum, not entire table?
    By 10 Gauge in forum Programming
    Replies: 2
    Last Post: 02-11-2011, 09:14 AM

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