Want to change order of columns in datasheet view in Access 2010

I have a form that is being displayed in datasheet view in Access 2010. I need to move one of the columns from column 2 (2nd place) to column 9 (9th place). How do I do that?
Who is Participating?
Dale FyeConnect With a Mentor Commented:
Can you post a sample database?

I've got a multi-user solution to this, which involves actually writing the column order to a table in the backend database when the form closes.  Then when the form (datasheet) is opened, it checks the database for the correct order of the columns and positions them accordingly.

Unfortunately, I don't have time to give a lot of details of that effort right now.  Will write an article on this eventually, but day and night programming jobs prevent me from spending the necessary time to write the article and develop the sample database.

You might be able to piece some of it together by searching on ColumnOrder or ColumnHidden within EE (don't forget to add "any activity by fyed" in the search criteria).  I asked a number of questions related to those topics about 10 months ago as I was developing this functionality.
Rey Obrero (Capricorn1)Commented:
open the form in view mode, select column 2 and drag to the position desired
save the form
Dale FyeCommented:
Open the form in datasheet view, click on the column and drag it where you want it, then save it and close it.

That should do it.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

bmorriso99Author Commented:
I have tried opening the form in datasheet view, clicking on the column (heading), dragging it and dropping it and then saving and that isn't working for me. It works while I am in the database but when I get out and back in it is back to position 2.
WHen I look at the view in design mode I can drag the field and it's label to where I want it but then when I go to datasheet view it is back to position 2. So for example in design mode the form looks like this

label 1   data 1                                  label 5   data 5
label 2   data 2                                  label 6   data 6
label 3   data 3                                   label 7   data 7
label 4    data 4                                 label 8     data 8
                                                           label 9     data 9

then I drag it so that it looks like this

label 1 data1                                      label 5   data 5
                                                            label 6    data 6
label 3 data 3                                    label 7     data 7
label 4 data 4                                    label 8    data 8
                                                           label 2     data 2
                                                           label 9     data 9

but then on the datasheet view it looks like this

label 1   label 2    label 3    label 4    label 5   label 6   label 7    label 8   label 9
data 1    data 2     data 3     data4      datat 5   data 6   data 7    data 8    data 9
Dale FyeCommented:
Do you have this datasheet as a subform in a main form?  If so, then opening the main form and then dragging the column will not work.

The position or order of the controls in the form design view has nothing to do with the datasheet column order.

You need to open the form(datasheet) in design view (all by itself).  Then switch to datasheet view, reposition the columns as you want, then click the Save button.  You should then be able to go back to design view, and switch back to datasheet and it should be in the correct position.
bmorriso99Author Commented:
Here is exactly what I am doing.
1. Double click the .mdb file
2. A switchboard is displayed wiht 4 controls/ buttons.
3. I click on the button that I want (modify comments is the heading)
4. The form called frmRpt comes up in dataasheet view with data.
5. I close out the switchboard tab
6. I click the drop down under 'view' and then 'design view'
7. I click the drop down under 'view' and then 'datasheet view'
8. I drag the column to where I want it
9. I right click the tab and choose 'save'
10. Sometimes I even click the save icon
11. Go back to 'view' , 'design view'
12 go to 'view', 'datasheet view' and the column is not where I want it.

Any ideas?
Dale FyeCommented:
I don't have 2010 available here at work, and I never work with the "tabbed" feature, so I cannot test what you are experiencing at the moment.  However, I will take a look when I get home this evening.

You might want to try changing the Document Window Options from "Tabbed documents" to "Overlapping Windows" (at least that is the way they are described in 2007) and trying it again.
bmorriso99Author Commented:
OK. It is not urgent and I appreciate your help. I just tried it with overlapping windows and that didn't work either.  Just so you know when I drag the column there is a dark (blue or black) line on the left hand side of the column and I drag that to where I want. I am not sure if that makes sense but I thought I would mention it. Let me know what you find tonight.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"9. I right click the tab and choose 'save'"
Can you post a screen shot of that ?

Rey Obrero (Capricorn1)Commented:

it will be better if you can upload a copy of the db.
I haven't been able drag and save column positions in 2007 or 2010. The only ways I have found to accomplish this are 1) freeze 1 or more columns to the left edge of the datasheet, or 2) go to design view and change the tab order of the fields.

lee555J5Connect With a Mentor Commented:
Based on your original Q, it doesn't look as if my method 1) will help. Therefore, try method 2) by switching to design view, right-clicking an empty area of the detail part of the form containing the fields to order, and selecting Tab Order....
bmorriso99Author Commented:
I will try both these methods. How do you freeze columns to the left side of the data sheet? I won't be working on this for a few more hours now.
Dale FyeConnect With a Mentor Commented:
To freeze columns, select as many columns as you want starting at the left and working to the right (I don't think you can skip columns).  Then right click on the selected column headers and select the "Freeze columns" option.
bmorriso99Author Commented:
When I right click on the design view in  the blank area and go to tab order the field that I want to move is in the place where I want to move it.

Next I tried freezing the 1st column and moving the second column where I wanted it to be and then I came back and froze the columns before it and that didn't work. The option I had was 'freeze fields' not 'freeze columns'.... is that correct?
bmorriso99Author Commented:
Unfortunately I cannot post the database because of proprietary information but let me see if I can make one up.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.