Solved Avoid Horizontal scrollbar in Datasheet Form and column zero width and repositioning of columns (1 Viewer)

[ahmad_rmh]

1. I am trying to avoid horizontal scroll bar in datasheet form even though increasing columns widths, It shouldn't go beyond the form view.

2. On the other hand, the user should not be able to make zero width adjustment of any column during resizing of the columns.

Kindly suggest the ways.

 

[CJ_London]

1. turn off scrollbars
2. what does 'zero width adjustment' mean? You mean prevent a user hiding or unhiding a column? if so, I don't believe that is possible with a datasheet form - use a continuous form instead

 

[ahmad_rmh]

1. turn off scrollbars
2. what does 'zero width adjustment' mean? You mean prevent a user hiding or unhiding a column? if so, I don't believe that is possible with a datasheet form - use a continuous form instead

Thanks CJ,

I am talking about after turning off the scrollbar as I resize the column width the scrollbar appers as it goes more than the screen width or form width.

Yes, I want to prevent user from hiding or unhiding the columns.

 

[MajP]

This will explain how to keep the user from hiding, unhiding, resizing columns

You Can Do That with Datasheets?

docs.microsoft.com

 

[CJ_London]

just checked and turning off scrollbars doesn't work for a datasheet view. And the link Maj provided allows setting things like hidden columns but does not prevent a user from changing them - although I guess you can have code that resets them although actions like hiding/showing do not generate an event.

what is your problem with users making changes to the layout which require them to use the horizontal scrollbar? I would stick with using a continuous form - you can format it to look like a datasheet.

If you are trying to hide some columns as not relevant to the particular view, in a continuous form you can use code to either hide or set the control width to 0 - and if necessary move the controls to the right over to hide the gap.

Or if you want to stick to using a datasheet and want to prevent users from seeing certain columns, modify the recordsource to exclude them.

If you explain in more detail why you want to do this, alternative solutions might present themselves.

 

[MajP]

but does not prevent a user from changing them

Look at the Sections about not allowing repositioning. It describes it there. The intro paragraph.

Believe it or not, once users start realizing that they can reconfigure their UI, they can't get enough of it. Many users know that they can just stick the mouse at the header of the column, click to select, and drag the column around. Easy! But this can be very annoying if the result isn't appropriate for the applications—especially if they save those changes!

The problem is that sometimes you want to make sure that your users can't make dramatic changes to the layout of your datasheet. For instance, you might need to make sure that some data is always visible. Unfortunately, there's no property to fix a column in position. However, you can use the events associated with controls to get the same effect.

To control repositioning, you need to use the MouseUp event of the controls in the datasheet. Set the properties for all of the columns to a single procedure in the same way I described in the previous section (and as shown in Figure 2), and then use the following procedure to keep the columns from wandering:

 

[CJ_London]

Ah - OK, missed that. Guess you would need to use the form mouseup to stop changes made by using the box top left

 

[ahmad_rmh]

Thanks @MajP , Thanks @CJ_London

I am trying to implement as per my requirement and will let you know.

 

[MajP]

Unfortunately I tried to implement this. So in theory on the mouse up event you reset the width to the original width, visibility to true, position to original position. The issue is not in the code, but I cannot get the mouseup event to fire on a drag and drop. I tested this and it is very intermittent when it does fire.

 

[CJ_London]

did you try the form mouse up event? Although must admit I've found that to be a bit 'scrappy'

 

[MajP]

did you try the form mouse up event? Although must admit I've found that to be a bit 'scrappy'

I did and that seemed more promising, but it is still strange. The event in the controls does not fire on the drag and drop. On the form It works when I drag right to left but does not fire when dragging left to right. So since it does not work all the time the behavior would be annoying. All of a sudden everything resets.

 

[CJ_London]

It is not clear to me what the OP is trying to achieve (or why) but this link may be of interest

continuous form that allows users to resize and reorder columns like a datasheet

I developed this because datasheets have some useful functionality, but limitations as well - among them Datasheets can't show controls such as buttons, images, attachments, etc Datasheets can't show a record over multiple rows within the detail section Datasheets don't have header and footer...

www.access-programmers.co.uk

 

[ahmad_rmh]

Is it possible to maintain a min and max width of a column?

 

[MajP]

Is it possible to maintain a min and max width of a column?

That seems to work fine since the mouse up does fire on the resize, just not always on the drag and drop. The commented out code is the position. The question is where to store the min and max for each control. I have a collection that stores the original width but you could have a formula for min and max based on a percentage of original, or store in the tag property hard values, or store in two other collections.

So this code keeps all columns to be more then .75 inc and less than 2 in. If you exceed those it resets to the original width. But I assume you want a width specific min max for each control.

Public ControlWidths As New Collection

Private Sub Form_Close()
  Dim ctl As Control
  For Each ctl In Me.Controls
    If ctl.Tag = "Lock" Then
      ctl.ColumnWidth = ControlWidths(ctl.Name)
    End If
  Next ctl
 
End Sub

Private Sub Form_Load()
  Dim ctl As Access.Control
  Me.KeyPreview = True
  For Each ctl In Me.Controls
    If ctl.Tag = "Lock" Then
        ControlWidths.Add ctl.ColumnWidth, ctl.Name
    End If
  Next ctl
End Sub

Private Sub LockPosition()
Dim ctl As Control
Dim counter As Integer
counter = 1
'MsgBox "Lock Position"
For Each ctl In Me.Controls
      If ctl.Tag = "Lock" Then
         'ctl.ColumnOrder = counter
         'counter = counter + 1
       
         If ctl.ColumnWidth < 0.75 * 1440 Then
           ctl.ColumnWidth = ControlWidths(ctl.Name)
         ElseIf ctl.ColumnWidth > 2 * 1440 Then
           ctl.ColumnWidth = ControlWidths(ctl.Name)
         End If
         ctl.ColumnHidden = False
      End If
Next ctl

End Sub


Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
  'Exit Sub
  LockPosition
End Sub

So just tested some more and the mouse up is inconsistent here to.

 

[ahmad_rmh]

I have made as like this which fulfills the requirement.

Private Sub Form_Current()
    With Me
        CategoryName.ColumnHidden = False
        CategoryName.ColumnWidth = -2
        CategoryNameArabic.ColumnHidden = False
        CategoryNameArabic.ColumnWidth = -2
        IsActive.ColumnHidden = False
        IsActive.ColumnWidth = -2
    End With
End Sub
Private Sub Form_GotFocus()
    Me.Refresh
End Sub

Kindly suggest which event I should have to use for the best results. Then repositioning of columns issue.

 

[MajP]

I used the form mouse up and tried this on another form and worked much better. I would make a common sub routine and call it from mouse up and on current. If the mouse up does not fire then you can catch it sometimes in the on current. Make sure to set the form's key preview to true.

 

[ahmad_rmh]

I used the form mouse up and tried this on another form and worked much better. I would make a common sub routine and call it from mouse up and on current. If the mouse up does not fire then you can catch it sometimes in the on current. Make sure to set the form's key preview to true.

I have expanded like this for the repositioning of columns issue which have solved the repositioning problem.

Private Sub Form_Current()
    With Me
        CategoryName.ColumnHidden = False
        CategoryName.ColumnWidth = 3500
        CategoryName.ColumnOrder = 1
        CategoryNameArabic.ColumnHidden = False
        CategoryNameArabic.ColumnWidth = 3500
        CategoryNameArabic.ColumnOrder = 2
        IsActive.ColumnHidden = False
        IsActive.ColumnWidth = -2
        IsActive.ColumnOrder = 3
        Refresh
    End With
End Sub
Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.Refresh
End Sub

Now, I want to convert it to loop, how I will do to perform this. Sorry Maj, I have tried key preview but it's not making a difference. Kindly elaborate. thanks