Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2009-02-08
Medium Priority
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

ID: 23584715
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

ID: 23584744
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
ID: 23584853
LVL 58

Accepted Solution

harfang earned 1000 total points
ID: 23586445
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

ID: 31544238
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

580 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