How to preserve columns in datasheet view with MDE file?

Hi Experts,

I have a program that are in use by a couple of users and they only have the MDE (A2003) file.

While in general this works fine, however I have a form/report which contains a lot of columns and hey would like to be able to select under hide/unhide the columns they need, including order of the columns.

Currently after they close the screen all their changes are lost, and the only way I can have them save it is by copying the original MDB and make their changes then save it, which is quite of a hassle..

What are the alternatives?
LVL 4
bfuchsAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
bfuchs,

1.  unzip the attached file
2.  Open an empty 2003 database
3.  Import the three Excel files into the new database
4.  Import the two .bas files into the database
5.  Import the two .txt files using the syntax:
loadfromtext acForm, "frm_Sample_Data_subform_Advanced", "C:\Work\Access\ExpertsExchange\Datasheet Columns\frm_sub.txt"
loadfromtext acForm, "frm_Sample_Data_Main", "C:\Work\Access\ExpertsExchange\Datasheet Columns\frm_Main.txt"

where you replace the path "C:\Work\Access\ExpertsExchange\Datasheet Columns\" with the path you saved the unzipped files to.

Then open frm_Sample_Data_Main and select a user from the combo box.

Adjust the column widths, and order, and freeze a column if you are interested, then either close the form, or change user.  It will save the current users settings.  This will show you how to create the tables you need in your application, and how to implement the code.

HTH,
Dale
DataColumns.zip
0
 
PatHartmanCommented:
You would need to create a table where the results can be stored.  The table should be in the BE and include the UserID so that every user can have custom settings.  I don't have a sample to post but someone else might.  The table should include
UserID
FormID
FieldName
OrdinalPosition
FieldWidth
VisibleFlg
0
 
Dale FyeCommented:
I have an article with sample database located here.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
bfuchsAuthor Commented:
@Pat,
What code will modify the forms appearance according to those settings?

@Dale,
I see you're mentioning sub form in that article, will that also work for a regular form being opened in datasheet view?
From my understanding, If form is based on a query it will not work? any workaround?
Would you have a MDB version of that file? (A2003)
One of the handy features of a datasheet is that users can move, resize, hide, and even freeze columns on the left side of the datasheet; they can configure the datasheet exactly the way they want and when they close the application, their settings are saved and restored the next time they open the application (at least sometimes they are). Unfortunately, the next time you distribute a new version of the application, their custom settings will be gone..
Well if only I would have that first part working for me (w/o the worry of distribute a new version..) I would be happy, however my experience is that settings dont get saved at first place, and therefore as soon as they close the screen all settings are gone!

Thanks,
Ben
0
 
Dale FyeCommented:
Ben,

Yes, it would work for a main form in datasheet view.   I don't have it in 2003, but will see if I can do the conversion for you.

Basically, what I do is create a table where I store the username, the name of the datasheet, the columnnames, their sequence, width, hidden and frozen attirbutes.  Then, when I load the form, I reshuffle the columns and set their width and or hidden properties appropriately.  Then, in the form_Unload event I reverse that process and store all of those values back into the table.
0
 
bfuchsAuthor Commented:
@Dale,
I don't have it in 2003, but will see if I can do the conversion for you.
Would appreciate it.

Besides that, there is no other way to have users save what they setup?
Why didnt MSFT make that available on compiled version, isn't there a demand for it?

Thanks,
Ben
0
 
Dale FyeCommented:
It actually seems that most people prefer to have the datasheet predefined.
0
 
PatHartmanCommented:
Why didnt MSFT make that available on compiled version, isn't there a demand for it?
The whole point of a complied app is that it doesn't change.
What code will modify the forms appearance according to those settings?
As I already said - I don't have an example.  But Dale posted one.  I didn't look at the code closely but I would just try it regardless of what Access version you are using.  VBA hasn't changed much over the years.
0
 
Dale FyeCommented:
Will try to extract that code into .bas files tonight so that you can use it in your application.
0
 
bfuchsAuthor Commented:
Hi experts,

It actually seems that most people prefer to have the datasheet predefined.
I agree that once its defined user may stick with that, but that all users will agree to same setting that is a diff story, and in most cases they don't, and therefore I'm unable to release a version that will satisfy all, unless I create separate report for each one..

The whole point of a complied app is that it doesn't change.
It should not change internal design that's fine, as this usually requires a programmer skills, but that should lock the datasheet view display columns this is in my opinion very impractical.

Same applied for the inability to change the printer assigned to a specific report..and each time I must log in and copy over the original file, change it and create a compiled file on their location..sounds absurd..

The way they created a wizard to manipulate linked tables, they should be able to have those features too encapsulated into the system..

Waiting to get hold of a A2007+ pc so I can open your demo.

Thanks,
Ben
0
 
PatHartmanConnect With a Mentor Commented:
The data on the columns is part of the design of the form.  The changes the user makes needs to be stored somewhere and that happens to be as part of the form's definition.  The solutions you've been offered, separate the design attributes from the form and store them in a table.

I'm not saying that I don't agree with you.  MS completely hosed the way DS settings work when they released A2007 and apparently no one but me complained so the poor design change is now embedded and 10 years old.  Most Access users don't even know that this was once a very smooth operation that could be controlled by the user or the developer.
0
 
bfuchsAuthor Commented:
Thank you very much, my experts!
0
 
Dale FyeCommented:
glad we could help.
0
 
PatHartmanCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.