We help IT Professionals succeed at work.

How to preserve columns in datasheet view with MDE file?

bfuchs
bfuchs asked
on
94 Views
Last Modified: 2017-11-05
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?
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
I have an article with sample database located here.
CERTIFIED EXPERT

Author

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
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
CERTIFIED EXPERT

Author

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
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
It actually seems that most people prefer to have the datasheet predefined.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Will try to extract that code into .bas files tonight so that you can use it in your application.
CERTIFIED EXPERT

Author

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
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Thank you very much, my experts!
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
glad we could help.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
You're welcome.