[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Freeze Columns

Posted on 2011-10-04
10
Medium Priority
?
303 Views
Last Modified: 2013-11-28
Hi All

I have a subform displayed in datasheet view. I want to lock the column headings so that users cannot re order the columns. Can't see how it's done in Access 2002
0
Comment
Question by:DatabaseDek
  • 5
  • 4
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36909370
Try this in datasheet view.

Select all of your columns, right-click and select "Freeze Columns".  That setting will persist when you close the form, and you can deploy your database like that.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36909604
If you want to do it via code, you could use:

Private Sub Form_Load()

    Dim intLoop As Integer
   
    For intLoop = 1 To Me.Controls.Count / 2
        With Me
            DoCmd.RunCommand acCmdFreezeColumn
        End With
    Next
   
End Sub
0
 

Author Comment

by:DatabaseDek
ID: 36910060
Hi All

Tried both ways both work. However the columns run off the page so when they are Frozen they stay put and I cannot see the off page columns. So to be more precise what I actualy need is a devise for maintaining the column order. Can you help with this? I do not know if this is possible?
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 49

Expert Comment

by:Dale Fye
ID: 36910082
Can I ask, why is it important to maintain the column order?  One of the only reasons I even consider using a datasheet view is to allow my clients to adjust the way they view the data.  Some users of a page may be concerned with columns A, B, and C.  Others may want them ordered by A, C, D.

0
 

Author Comment

by:DatabaseDek
ID: 36910261
Hi Fyed

The subform allows data to be pasted from excel which it can only do if the text and number fields are in correct order. The company regularily paste 70 columns of data so the order is critical
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36910386
Here is what I would do.

1.  Set the AllowAdditions property of the datasheet to False

2.  Add a button "Allow Additions" to your main form.

In the code behind that button, add some code that restores the column order to the proper order.  To use the columnorder property, you need to understand that all you can do is move columns so that they become the first column.  To get them in the proper order, you need to start with the column furthest to the right and do something like the following:

me.[Column5].columnorder = 0
me.[column4].columnorder = 0
me.[column3].columnorder = 0
me.[column2].columnorder = 0
me.[column1].columnorder = 0
me.AllowAdditions = true

Then, in the Form_AfterInsert event, set AllowAdditions = False and enable the "Allow Additions" button again.
0
 

Author Comment

by:DatabaseDek
ID: 36910633
Hi fyed

Sorry to be a Dork but do I actually use [Coumn5] or the name of the column's field? or the underlying form's object name. Perhaps I should have explained that I originally wanted a regular continuous form but as i could not get excel data to paste I altered the form view to datasheet. but the form still exists as a form underneath. Am I making any sense?
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 36910757
Yes, you are making sense.

You should use the name of the controls.  Most people don't bother to change the name of the controls on a datasheet (although I generally do), so I didn't think to mention that.

me.txt_Column5.columnOrder = 0
0
 

Author Closing Comment

by:DatabaseDek
ID: 36910869
Great help Thank you
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36910933
I found with one of my clients that they were a little disappointed that every time I fielded a new version of their software, the column shifting that they had done previously was overwritten by the sort order in the datasheet I fielded.

They asked me if I could make it so that their individual column sorting could be retained, so I had to write a couple of routines that read from/write to a text file every time the form is opened/closed, and sets the proper colum sequence for each user.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

829 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