How to save multiple datasheet layouts in .mde to a table?

[AdamHope]

I'm wanting to give my users some options in a datasheet view
* drag the columns around
* set the widths
* show or hide columns, etc.

If they do this, the changes don't 'stick' because they're using an .mde and
the next time they open the system it reverts back to the layout saved when
I created the .mde

I'm trying to add a combo button to select and view the different layouts in my sub form datasheet, and a 'Save Layout' button which records the new and existing control names, widths
and position in a table. Then each time they load the system, the settings
are read from this table for each user,
and the datasheet should look like it did when they saved it or select a different layout from the combo box.

I have some code to set just 1 default to a datasheet, but i would like the information to be stored in a table, to set more than 1 layout for that datasheet.

How do i get the information to be retived and saved to the back end table using VB via a save layout button on the form?

Private Sub Form_Open(Cancel As Integer)
 
Me.RowHeight = -1
 
With Me.ID
.ColumnHidden = False
.ColumnOrder = 1
.ColumnWidth = -2 'Set for visible text.
End With
 
With Me.FirstName
.ColumnHidden = False
.ColumnOrder = 2
.ColumnWidth = 2880
End With
 
With Me.LastName
.ColumnHidden = False
.ColumnOrder = 4
.ColumnWidth = 2880 
End With
 
'RunCommand acCmdUnfreezeAllColumns
 
End Sub
 
 

Thanks

[NeoPa]

Nice avatar Adam ;-)

What you are asking is quite involved. Not the sort of code I would expect a beginner to be attempting. With that in mind I don't intend to spoon-feed, but just throw out a few pointers.

1. Have you considered yet the format of the table that will be used to save the layout?
2. How will columns and overall formats be handled (IE What will the table structure be? Do you expect a flat structure to cover this?)?
3. Have you determined how you will identify each user?

These are just some of the questions you need to have answered before you start asking about the details of saving and loading the data (as the answers to these questions will effect how that is done).

I would suggest that a default whatever (depending on your structure) should be stored at the user level. You code would basically check for, and load, the user-based data if found, but would load the default data if no associated user data were found. Saving should be straightforward enough, assuming you have the table structure designed.

[AdamHope]

Hi Thanks for your quick reply,

I have determined how I will identify each user, by there account number. This will be stored In the layouts table next to there corresponding layouts, The users have a logon and user name which determines what account there are using.

The Datasheet that I want to select different layouts for has about 30 columns in. So I would prefer to save all the information for each column in one field, such as a memo field would be long enough to store all the paramounters.
Such as

txtID,False,1885;txtFirstName,True,2,630;txtLastNa me,True,3,855;txtDOB,True,5,645;txtFavColour,True, 4,1110;

Some how made into - Being “with me.txtID, .ColumnHidden=false, .ColumnWidth=1885 end with; …….ect”

I do not know how I would get this information to save like this in my table for that layout let alone back into the correct format for VB to use, unless I create each paramounter in a separate field for each Hidden, order, width and column heading which would be fairly large for 30 plus columns.

I hope this makes sense!

Thanks

[NeoPa]

It does make some Adam, but if I can say nicely, not quite enough.

I think you need to give more detailed consideration to how you plan to store this data. I may be able to help here in a general way, but your thoughts don't yet seemed marshalled well enough for me even to be able to guide you directly at this stage. Furthermore, though this may well seem very difficult, I suggest this is something you need to struggle with yourself. It is a very fundamental part of working with databases at the level you seem to be wanting to get to grips with. If it's done for you you will probably not get that understanding that will be so important for you for this and other projects.

At this stage, I would suggest giving a lot of thought (and as with all thoughts that are breaking new ground for you this will probably be difficult/uncomfortable) to exactly how you store this data. This doesn't appear to me to fit a flat file structure well. I would expect a normalised structure would be required (See Database Normalisation and Table structures). It's at about this stage that you come to realise what professional database developers understand, about how important the design and structure are in a database that is anything beyond the very basic, user-level databases. Bolting bits on afterwards doesn't work very well at all in such databases unless the fundamental design allows for it.

Sometimes it's possible to make compromises, but though it may get the db to market quicker, it may leave the db less extensible in case of later requirements. I really think you best next step is to work out exactly what information you need to store, and how best to design the storage of that information. It is neither a trivial task (Sorry about that), nor is getting it right unimportant.