Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unanswered: Lock Edit of Column Size in Datasheet View

    Is there a way to lock the size of the columns in a subform which is in datasheet view? I don't want anyone to move or resize the columns. I searched through the forum but did not find exactly what I was looking for.

  2. #2
    Join Date
    May 2005
    Posts
    150
    instead of putting it into a subform why not just use a listbox?

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could also use a continuous form with closely positioned textboxes that would appear identicle to a datasheet
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Dec 2006
    Posts
    53
    I was able to find the following ColumnWidth Proporty in VBA:

    You can use the ColumnWidth property to specify the width of a column in Datasheet view. Read/write Integer.

    expression.ColumnWidth
    expression Required. An expression that returns one of the objects in the Applies To list.

    Setting
    You can set this property by dragging the right border of the column selector or by clicking Column Width on the Format menu in Datasheet view and entering the desired value. When you set the ColumnWidth property by using the ColumnWidth command, the value is expressed in points.

    In Visual Basic, the ColumnWidth property setting is an Integer value that represents the column width in twips. You can specify a width or use one of the following predefined settings.

    Setting Description
    0 Hides the column.
    1 (Default) Sizes the column to the default width.

    Remarks
    Note The ColumnWidth property applies to all fields in Datasheet view and to form controls when the form is in Datasheet view.

    Setting this property to 0, or resizing the field to a zero width in Datasheet view, sets the field's ColumnHidden property to True (1) and hides the field in Datasheet view.

    Setting a field's ColumnHidden property to False (0) restores the field's ColumnWidth property to the value it had before the field was hidden. For example, if the ColumnWidth property was 1 prior to the field being hidden by setting the property to 0, changing the field's ColumnHidden property to False resets the ColumnWidth to 1.

    The ColumnWidth property for a field isn't available when the field's ColumnHidden property is set to True.

    Example
    This example takes effect in Datasheet view of the open Customers form. It sets the row height to 450 twips and sizes the column to fit the size of the visible text.

    Forms![Customers].RowHeight = 450
    Forms![Customers]![Address].ColumnWidth = -2
    I imgaine that you could write some VBA code (not sure what event would call it) that would basically cancel out any changes to the column width that a user tries to make. I'll try to work on some code.

  5. #5
    Join Date
    Dec 2006
    Posts
    53
    Oh man AM I GOOD!!!

    Here's the code you're looking for.

    Code:
    Option Compare Database
        Dim intCurrentWidth(NumberOfFields-1) As Integer
    
    Private Sub Form_Load()
        
        intCurrentWidth(0) = Me!FirstFieldname.ColumnWidth
        intCurrentWidth(1) = Me!SecondFieldName.ColumnWidth
        etc.
    End Sub
    
    Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
            If Me!FirstFieldName.ColumnWidth <> intCurrentWidth(0) Then
                Me!FirstFieldName.ColumnWidth  = intCurrentWidth(0)
            End If
            
            If Me!SecondFieldName.ColumnWidth <> intCurrentWidth(1) Then
                Me!SecondFieldName.ColumnWidth = intCurrentWidth(1)
            End If
    
            etc, with one if . . then . . end if statement for each field.
    End Sub
    Replace each italicized piece of code with your information as indicated. Use the form properties window to indicate that you have an event procedure for the OnLoad event and the OnMouseUp event.

    Oh yeah. All this takes place in the FORM properties of the form that is being used as a subform. The parent form will have no knowledge that this is taking place.
    Last edited by dfwelch; 01-03-07 at 17:07.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •