Creating custom views (column orders) in subforms with datasheet view

sal

Registered User.
Local time
Today, 12:42
Joined
Oct 25, 2009
Messages
52
I have a subform in datasheet view whose column order I would like to be be able to customize, perhaps with a button on the main form.

Since there are different usages of the data based on the task users may be engaged in, it would be very helpful for them to have a way to order the columns for their needs without repetition or confusion. Any suggestions for a general approach to this problem would be greatly appreciated.
 
You can simply drag datasheet fields into other positions. Would that be straight forward enough for users?
 
Columns in datasheet view can be manually moved by user. However, when frontend is replaced, this column arrangement will be lost.

I am not aware of VBA code that can arrange datasheet columns.
 
I am not aware of VBA code that can arrange datasheet columns.
There is a .ColumnOrder property but you can't set it in preview or form view. You should be able to set it by modifying the column Properties collection before opening the form. That's not something I'd entertain given that you can just drag them around.
 
I was thinking something like this, referring to the Column Order property of the control on a subform:

Private Sub Command88_Click()

Me.[SGS Data Review sfrm].Form!Water.ColumnOrder = 1
Me.[SGS Data Review sfrm].Form!Date.ColumnOrder = 2
Me.[SGS Data Review sfrm].Form!Form.Survey#.ColumnOrder = 3

End Sub

This got me to the control but with the error, "Object doesn't support this property or method"

Note: I set the control to the text label as the field name rather than the text box.
 
Last edited:
You didn't say which line raises the error - that's always good for us to know. Probably the 3rd - you have form in it 2x.
If that's it and you fix it, the next message ought to be "you can't .... when the form is in form view" or something like that.
There is a .ColumnOrder property but you can't set it in preview or form view
 
Look at missinglinq's link. This has all the secrets. Then save it to a text file for future reference. (Anyone know why these properties are undocumented?)

@Micron. This following is incorrect, most definitely can set all these properties at runtime.
There is a .ColumnOrder property but you can't set it in preview or form view. You should be able to set it by modifying the column Properties collection before opening the form.

See following demo. It allows you to pick a query. Sort its fields in any order. Hide fields as desired. And Export the formatted product.

NOTE: Another great example of where a custom class comes into play. My handy listbox sorter I pulled out of the library. One line of code to instantiate and set a few properties to handle Field Lists.

Ignore the code for the bells and whistles. The code to modify the order is simply
Code:
Public Sub SortDataSheet()
  Dim lst As Access.ListBox
  Dim frm As Access.Form
  Dim I As Integer
  Set frm = Me.subFrmQuery.Form
  Set lst = Me.lstSort
  For I = 0 To lst.ListCount - 1
    frm.Controls(lst.ItemData(I)).ColumnOrder = I + 1
  Next I
End Sub
Public Sub hideColumns()
  Dim lst As Access.ListBox
  Dim frm As Access.Form
  Dim itm As Variant
  Dim I As Integer
  Set frm = Me.subFrmQuery.Form
  Set lst = Me.lstChoose
  Call showColumns
  For Each itm In lst.ItemsSelected
    frm.Controls(lst.ItemData(itm)).ColumnHidden = -1
  Next itm
End Sub
Public Sub showColumns()
  Dim lst As Access.ListBox
  Dim frm As Access.Form
  Dim itm As Variant
  Dim I As Integer
  Set frm = Me.subFrmQuery.Form
  Set lst = Me.lstChoose
  For I = 0 To lst.ListCount - 1
    frm.Controls(lst.ItemData(I)).ColumnHidden = 0
  Next I
End Sub
 

Attachments

This following is incorrect, most definitely can set all these properties at runtime.
You are referring to a form or a query (you mention query after that statement)? I tried it with a datasheet form and got an error, and form is what I was referring to.
 
No it works in any data sheet. Control names have to match field names. You're doing something wrong
 
You didn't say which line raises the error - that's always good for us to know. Probably the 3rd - you have form in it 2x.
If that's it and you fix it, the next message ought to be "you can't .... when the form is in form view" or something like that.

Yep. That was a typo, but the real issue was using the # in the naming convention. I changed that and now the code works. Thanks.
 

Users who are viewing this thread

Back
Top Bottom