Microsoft Access, Continuous Form View, Copy Records and Column Order

Posted on 2009-02-08
Last Modified: 2013-11-28
I have the following code in a form and the code is executed with a button:

    DoCmd.OpenForm stDocName
    Forms!SessionHistory.RecordSource = rstString

rstString is defined above this code. Everything works great. Except, if I do a "copy all records" and then paste the result in a document, the columns are out of order.

So, I switched to Datasheet view and lo and behold, the datasheet view was displaying them in the order that they were being pasted. So, I modifed the datasheet view to put them in the order I wanted. Then, I switched back to continuous form view, selected, copied and pasted and it still ended up the way it was before. As a side note, when I do the copy/paste thing in datasheet view, it now works as I want it.

Now, before all this, a long time ago (well, 8 years ago when I first wrote this database), the original rstString was ordered the way the records are actually showing up in the copy/paste scenario. But I changed that years ago.

So, how do I get the copy/paste stuff to display the columns in the order specified? I've saved both forms (the "parent" with the clickable button and the subform with the data). I've cursed, danced and prayed.


Question by:schmidtc63
    LVL 42

    Expert Comment

    Please "let go" of the idea the the rows in a table have an "order".  Anytime you want to see them in an order or process them in an order it is your obligation to explictly say so in an order by clause.  

    I realize they appear in some order in datasheet view or in a record source and I know that you may have been able to rely on that order for before.  But, trust me, it is not guaranteed.  

    Use an order by clause in the underlying recordsource or query to designate the order you prefer.  
    LVL 42

    Expert Comment

    Yikes!  I'd blame my horrendous error on a long day, except it's morning.  While, my post is not incorrect, it is in reference to ROW order and does not apply to your problem of COLUMN order.  
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    LVL 58

    Accepted Solution

    Hello schmidtc63,

    An Access form has three concurrent control "orders".

    1) z-order, which you follow when tabbing in design view or from VB with Me(#)
        [changed using "bring to front" and "send to back"]

    2) tab order, for each section, which you follow when tabbing in form view
        [changed using the .TabIndex property or the "Tab Order..." assistant

    3) column order, which you see in datasheet view
        [drag-and-drop interface; if not modified: identical to the tab order]

    Copy-paste will use the current view's order, 2) or 3).

    In other words, for your copy-paste to work, adjust the tab order to something you like both for the screen and for the pasted columns...

    If you wish, you can remove the datasheet's column order for consistency, using the code below: open Form1 in design view, and run.

    Sub ClearColumOrder()
        Dim ctl As Control
    On Error Resume Next
        For Each ctl In Forms!Form1.Controls
            ctl.Properties("ColumnOrder") = 0
        Next ctl
    End Sub

    Open in new window


    Author Closing Comment

    Thank you. That was exactly it. When I first created the form, I had the columns in the wrong order. I corrected the output on the screen but never changed the tab order since it was not a form that was available for entry or editing.

    I modified the tab order, selected/copied/pasted and it worked perfectly.

    Again, thank you.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now