By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

protecting a form in datasheet view

P: 23
I have created a form in the datasheet view, but the problem I am having is that when other people use the form they reset size of the columns or hide them. How can I control the properties of the form so that it will reset back to all of the original column widths or un-hide columns when the form is closed.
Oct 2 '12 #1
Share this Question
Share on Google+
9 Replies

Expert Mod 5K+
P: 5,397
I feel your frustration.

I ran across a small bit of code (may have been in one of the many books I've read) that forces users to change to design mode to make certain changes to the forms.

Make a copy of your frontend/database and in a standard vba module (create a new one) cut and past the following code:

>Note: I usually only run this code either after I'm done with the database design or on a copy of the database that I push out to the users for design feedback.

Expand|Select|Wrap|Line Numbers
  1. Sub FixAllowDesign()
  2. Dim objFrm As AccessObject, frm As Form
  3. ' Go through every form in the database
  4. For Each objFrm In CurrentProject.AllForms
  5.    ' Open the form in Design view
  6.    DoCmd.OpenForm FormName:=objFrm.Name, _
  7.    View:=acDesign
  8.    ' Set the form object for efficiency
  9.    Set frm = Forms(objFrm.Name)
  10.    ' Check and reset the AllowDesignChanges property
  11.    If frm.AllowDesignChanges = True Then
  12.       frm.AllowDesignChanges = False
  13.       ' Save the change
  14.       DoCmd.RunCommand acCmdSave
  15.    End If
  16.    'release the form
  17.    Set frm = Nothing
  18.    ' Close the form
  19.    DoCmd.Close acForm, objFrm.Name
  20. ' Loop to the next form
  21. Next objFrm
  22. End Sub
Now, play with the forms in your copy. If you're OK with how this effects the forms then you can either push the copy out to the users or run the code in the production database.

This won't fix all of the issues that you're after; however, it'll go a long ways to lowering the bloodpressure.

Splitting the database into a front and backend then making an "mde" or "accde" from a COPY of the front end as the user can no longer open the forms in design mode either.
Oct 2 '12 #2

Expert Mod 5K+
P: 5,397
opps... hit enter too soon...
you can also design a form so that when default view is set to "Continuous Forms" it looks very much like a datasheet view and then disallow datasheet view.

I still run the code I posted even when I do the above.

You might also read this
Oct 2 '12 #3

P: 23
Thank you for the suggested code. I already have the data base split for front end and backend. Our company limits the number of people who have developer status and most users have read only capacity. But this has not prevented the data sheet view from being altered. The only other solution I can come up with is to a router which copies the front end from the network drive and puts it into the temp folder on the user’s C drive. This way it allows the user to manipulate the view while they have the database open. But once it is closed these changes do not cascade back to the frontend. By the way I do not make it common knowledge to everyone that they are making a copy on their hard drive, they must always use the router to open the database from the network drive.
Oct 5 '12 #4

Expert Mod 100+
P: 2,321
I have a developer frontend, which I create a MDE (or accde) and place that on the network. At the same time I update a version number in a central database. Next time the user opens their local frontend, the first thing it checks is the version number. If their local version number is different from the server it opens up a update db, which copies the MDE to their computer. That way each user always has their own frontend.
Oct 5 '12 #5

Seth Schrock
Expert 2.5K+
P: 2,941
Smiley, could you send me a PM with a copy of that system? I have several databases at work that I would love to have a system like that.
Oct 5 '12 #6

Expert Mod 100+
P: 2,321
I have been meaning to make a article out of it for some time. But currently I am working on creating a article series on the treeview based on the webinar I hosted last Thursday. For now, I think we have derailed this topic enough. :)
Oct 6 '12 #7

Expert Mod 2.5K+
P: 3,284

Won't this also be alleviated by setting the options for the current database? There is a check box for "Enable design changes for tables in Datasheet view." I thought unchecking this toggle would prevent users from being able to alter tables design in cases like this once the db has been published....
Oct 6 '12 #8

Expert Mod 5K+
P: 5,397
@Twinney: That is part of what the code does that I posted in #2

@Bruce: As TheSmileyCoder pointed out in #5 and I pointed out (but not as clearly) at the bottom of #2, The distribution of the front-end, preferably as either MDE or ACCDE will take care of this in that your end-user will have their own copy of the front-end.

The only issue with the front-end is if there are changes made. As Smiley does, so I also have a way of pushing the front-ends out that ensure that the most recent upgrade/version is in-use by my end-users. The nice thing about the front-end... if the user bungs the one they have, simple fix to have a new one pushed down... I have just such code built in that will force that upon an admin flag in the user profiles - but that's a whole other thread!
Oct 6 '12 #9

P: 23
Thank you I will give this a try.
Mar 6 '14 #10

Post your reply

Sign in to post your reply or Sign up for a free account.