prevent user from moving columns in form datasheet view

Hello experts,

I am working with an MS Access 2007 form that displays has its property default view set to datasheet.   The forms controlsource is set to a sql statement and the textbox controls on it are bound to the datasource or date retrieved from the sql.

I noticed that when I open this form (in datasheet view), there is nothing preventing a user from changing the ordering of columns.    It's easy for any person using the form to click on a column, select it, and drag to another location.    I need to prevent users from making any changes to the display of the datasheet.

Is there a property of the form that will prevent this?    I've already tried setting "moveable" property to No, "allow edits" property is set to no as well but neither, prevents a user from changing the order of columns.   Please let me know if any clarification is needed.

Thanks!   lcha
lchaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
close the form with

docmd.close acform, me.name, acsaveno

they can move the columns, but can not save it
0
Rey Obrero (Capricorn1)Commented:
or use a continuous form instead.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is there some reason a user should not be able to move the columns? I ask because this is a very normal UI paradigm - if users see columns, they expect to be able to move them. I can't think of a good reason why users would NOT be able to move columns, but you may have a valid reason.

0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

BitsqueezerCommented:
Hi,

if you only want to make sure that the form will look like you designed it when the form is opened there is one simple method: Deploy the database with Access runtime. The user can go on moving the columns but the new arrangement can not be saved. After closing and reopening the column arrangement is as original designed.
I agree with LSMConsulting, the users I know would be thankful if I could make it possible to save the column arrangement with the runtime. But that belongs to "form design" and every function of this group doesn't work in the runtime edition (because of this you also can't for example use form based filter in the runtime).

Cheers,

Christian
0
lchaAuthor Commented:
Hi All,

thanks for the feedback..

Actually it would be fine that users can rearrange columns.   I just want to make sure that everything is back in place as initially started after closing and reopening the form.    

capricorn1 - regarding
docmd.close acform, me.name, acsaveno    (not sure where this code should go on my form)

User has the option to close the form by clicking on the X in the upper right hand side of the form.    After closing the form, and reopening, all rearrangement or movement of any columns remains.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't think that acSaveNo would work for this, but I"m not sure. You can always specify the order in which your columns should be shown in the Open or Load event of your form:

Me.YourFirstColumn.ColumnOrder = 1
Me.YourSecondColumn.ColumnOrder = 2
etc etc


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
open the datasheet form
select all columns, then right click select freeze columns

when satisfied, open the datasheet form in design view and set the shortcut menu property to NO
0
lchaAuthor Commented:
apologies for the delay thanks everyone for the feedback, got pulled into some other work and didn't get the chance to respond on this in a timely manner.

LSMConsulting - you got me thinking that maybe I should allow users the ability to sort and move columns.  

your most recent suggestion almost works for me.   I noticed though that if I make a change (e.g. move column) close the form and reopen.  The change to the column remains when it seems that the onload event should have set the column order back ... it only goes back to how it should be after moving the horizontal scroll bar of the form.   For some reason that seems to be activating putting the columns back in the order I specified in the form load.

Me.YourFirstColumn.ColumnOrder = 1
Me.YourSecondColumn.ColumnOrder = 2
etc etc

Capricorn1- thanks, the suggestion you provided will also work.  

bitsqueezer - thank you, I will look further into the access runtime.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I can't tell you why that happens, but it would seem to be a glitch in the interface. You could always force the order in the Open event of the form, if it's important.
0
lchaAuthor Commented:
thanks all for being patient.   I
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.