Datasheet/Data Grid Layout

[Matt]

I have a split database that is under development and as new versions get
pushed to the users I continue to get one requests. The users would like to
keep their query and form Datasheet view layouts. Each person wants to see
the columns in they order they choose.

Is there a way to capture the column sequence store it in a table and then
set the column sequence back the the what the user wanted for forms datasheet
view and queries/tables?

Could really use some help with this one and I can't seem to find the answer
anywhere online.

Matt

 

[Alex Dybenko]

Hi,
you can "save" column order to table or registry using ColumnOrder property:

Dim ctl As Access.Control
For Each ctl In Me.Section(0).Controls
If TypeOf ctl Is TextBox Then
SetUserOption Me.Name & "_" & ctl.Name, ctl.ColumnOrder
End If
Next ctl

and then restore it in open event:

For Each ctl In Me.Section(0).Controls
If TypeOf ctl Is TextBox Then
lng = GetUserOption(Me.Name & "_" & ctl.Name, dbLong, 0)
If lng > 0 Then
ctl.ColumnOrder = lng
End If
End If
Next ctl

SetUserOption and GetUserOption are functions to save/restore values in
registry, you can replace them with your own

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Matt]

Thanks Alex. I did not consider writing to the Registry.

I Plugged this into a form and when I compile it says that GetUserOption and
SetUserOption are not defined subs or functions.

Is there a reference I need to have for this to run? or is there another
reason for this?

 

[Matt]

Alex, I got this to work using some different commands. Thanks for the Idea!
But this leaves me wondering about the rest of the formatting issues.... ie.
Width, Height, FontSize, and font of the datasheet view. If I could create a
table or access and change the table based on the registry to match what the
user wants to see....That would be AWESOME! Can you help?


My Code, below, is in the form....

Private Sub Form_Close()
Dim db As Database
Set db = CurrentDb
Dim ctl As Control
For Each ctl In Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Or TypeOf ctl Is ListBox
Then
SaveSetting db.Name, Me.Name, ctl.Name, ctl.ColumnOrder
End If
Next ctl
Set db = Nothing

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim db As Database
Set db = CurrentDb
Dim ctl As Control
Dim lng As Long

For Each ctl In Controls
If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Or TypeOf ctl Is ListBox
Then
lng = GetSetting(db.Name, Me.Name, ctl.Name, ctl.ColumnOrder)
If lng > 0 Then
ctl.ColumnOrder = lng
End If
End If
Next ctl
Set db = Nothing
End Sub


Thanks

 

[Alex Dybenko]

Hi Matt,
Width, Height - you mean form's Width and Height? then you can use form's
..Width property and section .Height property to save and then restore
For font you can use DatasheetFont* properties like DatasheetFontHeight,
DatasheetFontName

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com